Help with MySQL Query

[Ice2Cool]

Hey guys

I am new to SQL and looking for some help please. I need to combine data from 4 different tables (the common field between the first 3 is the same while table 4 is common only with table 3.

Here is my table columns:

Table 1 - 'Items' Columns:
Item_Code, Item_Min, Item _max

Table 2 - 'Stock on Hand' Columns:
Item_Code, Quantity

Table 3 - 'Purchase Orders Lines'
Item_Code, Quantity, Order_Num, Status

Table 4 - 'Purchase Order Headers'
Order_Num, Date Raised, Fulfilled

What I want:
Item_Code (1) , Quantity (Table 2), Quantity (Table 3), Date Raised (Table 4)

First I need to do an inner join between Table 3 and 4 on Order_Num where Status (Table3 ) = 'Ordered' and where Fulfilled (Table 4) = 'U'

After that, I need to do an outer left join between Table 1, Table 2 and the new table from above on item_code where Table 1 is the master.

Any advise?

I am truly puzzled. Maybe I am going about it wrong?

 

[ToxicBunny]

Ok, I don't know about MySQL syntax, but i think the Logic is roughly the same as MS SQL...

SELECT 1.Item_Code, 2.Quantity as (Table2Quantity), 3.Quantity as (Table3Quantity), 4. Date_Raised
FROM Items 1 with (NOLOCK)
JOIN Stock_on_Hand 2 with (NOLOCK)
ON 1.Item_Code = 2.Item_Code
JOIN Purchase_Order_Lines 3 with (NOLOCK)
ON 2.Item_Code = 3.Item_Code
JOIN Purchase_Order_Headers 4
ON 3.Order_Num = 4.Order_Num
WHERE 3.Status = 'Ordered'
AND 4.FulFilled = 'U'

 

[Batista]

Just use normal joins to build the query, then start changing joins to filter out/in the data you require...

You will find that in real life only left joins and inner joins gets used 99% of the time.

 

[Ice2Cool]

Just use normal joins to build the query, then start changing joins to filter out/in the data you require...

You will find that in real life only left joins and inner joins gets used 99% of the time.

Thanks guys I figured it out after much trial & error.

 

[nand]

Your design has some problems IMO.

You seem to be developing a model for tracking inventory.
Here's a website that describes another model: http://www.databaseanswers.org/data_models/inventory_and_sales/index.htm

The main problem I see is that you have two separate tables keeping track of orders.
You'll eventually find that, as the order history grows and orders are invalidated, it becomes spaghetti.
That would be because there's not really any direct references to keep the two tables in harmonious synchronization.

You can either combine them, which solve intermittent issues, or revise your model.
Another thing I notice is that your model lacks key features, such as invoices for example.

Given your current model though, the following query would suffice:

SELECT
    i.Item_Code,
    s.Quantity AS TotalQuantity,
    pl.Quantity AS PurchasedStock,
    (s.Quantity - pl.Quantity) AS StockInHand,
    DATE(ph.`Date Raised`) AS Date
    
FROM
    Items i
    
LEFT JOIN
    `Stock on Hand` s
        ON (s.Item_Code = i.Item_Code)
    
LEFT JOIN
    `Purchase Orders Lines` pl
        ON (pl.Item_Code = i.Item_Code)
        
LEFT JOIN
    `Purchase Order Headers` ph
        ON (ph.Order_Num = pl.Order_Num)
        
WHERE
    ph.Fulfilled = 'Y'
    
GROUP BY DATE(ph.`Date Raised`)

It does give out stock levels per day, but the query does not make sense as a statistic.

 

[Ice2Cool]

Haha it's not my design. Our MIS system was designed that way. In just trying to query the data. There are two tables for purchase orders as one keeps tracking of the header (supplier chosen) and and po_line table keepts track of each line item ordered by item code. Since we can order many different items from a supplier in one purchase order, the design makes sense.

Anyway, what I was trying to do was create alert events to warn my buyer when an item code falls below it's minimum stock level. It's a bit complicated as much of our stock is imported and at any given time we usually have 1-2 orders on the sea which we have to cater for. To complicate it further we can also part receive from a purchase order (for local suppliers). Anyway, it all worked out so quite happy.

Your design has some problems IMO.

You seem to be developing a model for tracking inventory.
Here's a website that describes another model: http://www.databaseanswers.org/data_models/inventory_and_sales/index.htm

The main problem I see is that you have two separate tables keeping track of orders.
You'll eventually find that, as the order history grows and orders are invalidated, it becomes spaghetti.
That would be because there's not really any direct references to keep the two tables in harmonious synchronization.

You can either combine them, which solve intermittent issues, or revise your model.
Another thing I notice is that your model lacks key features, such as invoices for example.

Given your current model though, the following query would suffice:

SELECT
    i.Item_Code,
    s.Quantity AS TotalQuantity,
    pl.Quantity AS PurchasedStock,
    (s.Quantity - pl.Quantity) AS StockInHand,
    DATE(ph.`Date Raised`) AS Date
    
FROM
    Items i
    
LEFT JOIN
    `Stock on Hand` s
        ON (s.Item_Code = i.Item_Code)
    
LEFT JOIN
    `Purchase Orders Lines` pl
        ON (pl.Item_Code = i.Item_Code)
        
LEFT JOIN
    `Purchase Order Headers` ph
        ON (ph.Order_Num = pl.Order_Num)
        
WHERE
    ph.Fulfilled = 'Y'
    
GROUP BY DATE(ph.`Date Raised`)

It does give out stock levels per day, but the query does not make sense as a statistic.

 

[Drake2007]

You may want to change the Group by Date to Order by Date.