MYSQL Combing Two tables From Multiple Row

Nimitz E.

I have two tables, one is for all products, the other is for new purchases products.

Product Table

Item    Qty
301       2
302       5
303       3
304       4
305       6

Purchases Table

Item     Qty    Status    Date
302       5     used      09-15-2015
303       5     reserve   09-20-2015
301       5     used      09-20-2015
302       5     reserve   09-20-2015
304       5     used      10-15-2015
303       5     reserve   10-15-2015

I want to display how many quantity in the product table, and join the quantity of purchases table to Product Table

This is my initial sql query

SELECT product_name, product_quantity, quantity 
FROM products 
    LEFT OUTER JOIN purchases ON products.ID = purchases.product_ID 
WHERE product_status !='deleted' AND status != 'used'`. 

However, it only returned the the items from purchases table that is reserve.

what I want to achieve is

Item    Qty    Reserved Qty    Total
301       2     0                 2
302       5     5                 10
303       3     10                13
304       4     0                 4
305       6     0                 6

UPDATE New sql query

SELECT product_name, product_quantity, p.quantity 
FROM products 
    LEFT OUTER JOIN (SELECT * FROM purchases 
        WHERE status = 'reserved' 
        GROUP BY product_ID) AS  p ON p.product_ID = products.ID 
WHERE products.product_status !='deleted'`

But it returns 0 on reserved qty.

mynawaz

You are almost there in terms of your query except that you need to sum the quantity of reserved items and then use GROUP BY, sort of like this

SELECT
    pd.item AS 'Item', pd.qty AS 'Qty', IFNULL(pr.reserved_quantity, 0) AS 'Reserved Qty', (pd.qty+IFNULL(pr.reserved_quantity, 0)) AS 'Total'
FROM
    product pd
    LEFT OUTER JOIN (SELECT item, SUM(qty) AS 'reserved_quantity' FROM purchases WHERE `status`='reserve' GROUP BY 1) AS pr ON pd.item=pr.item

Эта статья взята из Интернета, укажите источник при перепечатке.

Если есть какие-либо нарушения, пожалуйста, свяжитесь с[email protected] Удалить.

Отредактировано в
0

я говорю два предложения

0обзор
Войти в системуУчаствуйте в комментариях

Статьи по теме

List users from two joined tables with mySQL

PHP MySQL Selecting Multiple Tables Based on Column/Row Value

Mysql subtract two row values from group by

Getting data from multiple tables into single row while

How to substract values in columns from two different tables in MySQL?

How to generate results from two different MySQL tables on a single graph

Joining multiple tables in MySQL

Querying multiple tables on MySQL

I need assistance with my MySql query (selecting from multiple tables)

How to delete from multiple tables with the same column in mysql?

Mysql select rows from multiple tables in specific order

Retrieving MySQL results from two tables excluding what is common in both tables in a specific column

Mysql creating view two different count of same data in different condition with multiple tables

Shell script to pull row counts from all Hive tables in multiple Hive databases

How to query multiple tables in mysql

How can i represent multiple m:n relations from two tables?

Mysql problem Select multiple tables problem in mysql

Database query from multiple tables

Update rows from multiple tables and

Conditionally joining from multiple tables

Selecting from multiple tables in laravel

Select The last 3 news from each category - Two tables - (MySQL - PHP)

mysql LEFT JOIN main table and two additional tables with max value from each

MySQL merge two tables in single table with SELECT

Mysql: Confused about query joining two tables

how to select one row from one table and multiple rows from other table using joins in mysql,

Mysql one middle table for multiple similar tables

Joining Multiple MySQL tables with Same Column Name

How to UPDATE multiple tables with 1 query in MYSQL

TOP список

  1. 1

    Распределение Рэлея Curve_fit на Python

  2. 2

    How to click an array of links in puppeteer?

  3. 3

    В типе Observable <unknown> отсутствуют следующие свойства из типа Promise <any>.

  4. 4

    Как добавить Swagger в веб-API с поддержкой OData, работающий на ASP.NET Core 3.1

  5. 5

    Нарисуйте диаграмму с помощью highchart.js

  6. 6

    无法通过Vue在传单中加载pixiOverlay

  7. 7

    Отчеты Fabric Debug Craslytic: регистрация, отсутствует идентификатор сборки, применить плагин: io.fabric

  8. 8

    Статус HTTP 403 - ожидаемый токен CSRF не найден

  9. 9

    TypeError: store.getState não é uma função. (Em 'store.getState ()', 'store.getState' é indefinido, como posso resolver esse problema?

  10. 10

    ContentDialog.showAsync в универсальном оконном приложении Win 10

  11. 11

    В UICollectionView порядок меняется автоматически

  12. 12

    Merging legends in plotly subplot

  13. 13

    Elasticsearch - Нечеткий поиск не дает предложения

  14. 14

    Bogue étrange datetime.utcnow()

  15. 15

    Объединение таблиц в листе Google - полное соединение

  16. 16

    Single legend for Plotly subplot for line plots created from two data frames in R

  17. 17

    как я могу удалить vue cli 2?

  18. 18

    ViewPager2 мигает / перезагружается при смахивании

  19. 19

    Компилятор не знает о предоставленных методах Trait

  20. 20

    JDBI - В чем разница между @define и @bind в JDBI?

  21. 21

    проблемы с AVG и LIMIT SQL

популярныйтег

файл