Help with multiple Left Joins

15 Dec 2005 3:47 PM

lytung

Hi All,

this is my first time posting here as i cannot find the answer myself.
I have couple tables i want to join and i can't seem to get it right. I
have the following tables:

Part: (Part ID), PartDescription
Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE

I want a query of the Available qty >0 for every part we have. When i
do a query like this, i get 5363 records.

SELECT     dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
                      dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM         dbo.PART left outer JOIN
                      dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_ID
WHERE     (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost

Then i want to add a column for this query, the inventory_Trans.Qty
that has type =O. I tried the query below and it doesn't
work....obviously ican't inner join again from PART_WAREHOUSE as it
does the left join based on that table, so this wouldn't work:

SELECT     dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
                      dbo.PART_WAREHOUSE.AVAILABLE_QTY
FROM         dbo.PART

left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
dbo.PART_WAREHOUSE.PART_IS

left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
dbo.INVENTORY_TRANS.PART_ID

WHERE     (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
(INVENTORY_TRANS.TYPE='O')
group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
available_qty, part.unit_material_cost

I tried using this, but i am not familiar with this syntax and i am
getting errors.

SELECT      p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM         PART p1, PART p2
LEFT JOIN
                      dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN
                      dbo.INVENTORY_TRANS as i on p2.ID =
dbo.INVENTORY_TRANS.PART_ID

WHERE     (w.AVAILABLE_QTY > 0 and i.type='O')
group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
p1.unit_material_cost
Order by p1.warehouse_Id

so i am out of ideas. Can anyone enlighten me about how to do this:??
thank you so much in advance.

15 Dec 2005 4:22 PM

Mark Williams

Although this probably isn't the answer that you are looking for, but I'm
wondering why you are using the GROUP BY clause in your query? You typically
use GROUP BY when using an aggregate function in the SELECT statement, such
as COUNT. Try running the second and third queries without the GROUP BY
clause.

Try

SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
LEFT JOIN dbo.INVENTORY_TRANS as i on w.ID = i.PART_ID
WHERE (w.AVAILABLE_QTY > 0 and i.type='O')
Order by p1.warehouse_Id

Show quote

"lyt***@gmail.com" wrote:

> Hi All,
>
> this is my first time posting here as i cannot find the answer myself.
> I have couple tables i want to join and i can't seem to get it right. I
> have the following tables:
>
> Part: (Part ID), PartDescription
> Part_warehouse: ( WarehouseID), (Part_ID), Available_QTY
> Inventory_Trans: (Transaction_ID), PartID, QTY, TYPE
>
> I want a query of the Available qty >0 for every part we have. When i
> do a query like this, i get 5363 records.
>
> SELECT     dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
>                       dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM         dbo.PART left outer JOIN
>                       dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE     (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
>
> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work....obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:
>
>
> SELECT     dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
>                       dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM         dbo.PART
>
> left outer JOIN dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_IS
>
> left outer JOIN dbo.INVENTORY_TRANS ON dbo.PART_WAREHOUSE.PART_ID =
> dbo.INVENTORY_TRANS.PART_ID
>
> WHERE     (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)AND
> (INVENTORY_TRANS.TYPE='O')
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost
>
> I tried using this, but i am not familiar with this syntax and i am
> getting errors.
>
> SELECT      p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
> w.AVAILABLE_QTY, i.qty, i.type, w.WAREHOUSE_ID
> FROM         PART p1, PART p2
> LEFT JOIN
>                       dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
> LEFT JOIN
>                       dbo.INVENTORY_TRANS as i on p2.ID =
> dbo.INVENTORY_TRANS.PART_ID
>
> WHERE     (w.AVAILABLE_QTY > 0 and i.type='O')
> group by w.WAREHOUSE_ID, p1.ID, p1.DESCRIPTION, w.available_qty,
> p1.unit_material_cost
> Order by p1.warehouse_Id
>
>
>
> so i am out of ideas. Can anyone enlighten me about how to do this:??
> thank you so much in advance.
>
>

15 Dec 2005 4:43 PM

lytung

no that gave me an error.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'ID'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'warehouse_Id'.

But even if that wo rked the logic doens't make sense.

I want the second query to be based on the first query. Maybe its not
about doing two joins but what i probably need is a transaction query.
First i need this:

SELECT p1.ID, p1.DESCRIPTION, p1.UNIT_MATERIAL_COST,
w.AVAILABLE_QTY, w.WAREHOUSE_ID
FROM PART p1
LEFT JOIN dbo.PART_WAREHOUSE as w ON p1.ID = w.PART_ID
where ( w.AVAILABLE_QTY > 0)

Then i need the i.type='O' (from inventory_trans) based on those
results. I hope this make sense!

15 Dec 2005 5:14 PM

Rogas69

Hi

> SELECT     dbo.PART_WAREHOUSE.WAREHOUSE_ID, dbo.PART.ID,
> dbo.PART.DESCRIPTION, dbo.PART.UNIT_MATERIAL_COST,
>                      dbo.PART_WAREHOUSE.AVAILABLE_QTY
> FROM         dbo.PART left outer JOIN
>                      dbo.PART_WAREHOUSE ON dbo.PART.ID =
> dbo.PART_WAREHOUSE.PART_ID
> WHERE     (dbo.PART_WAREHOUSE.AVAILABLE_QTY > 0)
> group by PART_WAREHOUSE.WAREHOUSE_ID, part.ID, part.Description,
> available_qty, part.unit_material_cost

is it possible that part with given ID doesn't belong to a part_warehouse?
in other words can you get null as warehouse_id in above query?
the second thing - group by clause here is really not necessary

> Then i want to add a column for this query, the inventory_Trans.Qty
> that has type =O. I tried the query below and it doesn't
> work....obviously ican't inner join again from PART_WAREHOUSE as it
> does the left join based on that table, so this wouldn't work:

how about this?
SELECT pw.WAREHOUSE_ID, p.ID, p.DESCRIPTION, p.UNIT_MATERIAL_COST,
pw.AVAILABLE_QTY

FROM dbo.PART p left outer JOIN

( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID

) ON p.ID = pw.PART_ID

WHERE (pw.AVAILABLE_QTY > 0)

AND (itr.TYPE='O')

HTH

Peter

15 Dec 2005 6:05 PM

lytung

Hi Peter,

thanks for replying. The query you gave me ended up with too many
records. You are right, i dont need the group by statement.

Part_Warehouse has 2 Primary Keys: Part_ID, and WAREHOUSE_ID

you skipped out the PART_WAREHOUSE join to PART. I guess for this join
it doesn't have to be a left join, but it has to be joined. The second
join has to be left, which you did...

I am getting confused with mixing the joins. What is the general rule
of multiple joins? does the second join depend on the previous join? or
can they be independent?

16 Dec 2005 10:22 AM

Rogas69

use parentheses to prioritize joins. the outer table is joined to result of
join in parentheses.

can you show the ddl of these tables and some sample data and describe
result you would like to obtain?

part_warehouse is a table that relates parts and warehouses?

> you skipped out the PART_WAREHOUSE join to PART. I guess for this join
> it doesn't have to be a left join, but it has to be joined. The second
> join has to be left, which you did...

FROM dbo.PART p left outer JOIN

( dbo.PART_WAREHOUSE pw inner join dbo.INVENTORY_TRANS itr ON pw.PART_ID =
itr.PART_ID

) ON p.ID = pw.PART_ID

no, I left joined PART to the result of inner join between PART_WAREHOUSE
and INVENTORY_TRANS.

again, do you have PARTs without WAREHOUSEs?

peter