JOINS

[annub]

I have 3 tables A,B,C. I have item common col in each table. I need to display all the items form table A and B after where conditions. i am doing something like this.


SELECT dbo.ttdilc101201.t_item, dbo.ttdilc101201.t_cwar, dbo.ttdilc101201.t_loca, dbo.ttdilc101201.t_clot, dbo.ttdilc101201.t_date, dbo.ttdilc101201.t_strb,
dbo.ttdilc101201.t_stro, dbo.ttiitm001201.t_dsca, dbo.ttipcs021201.t_dsca AS EXPR1, dbo.ttdilc101201.t_strs
FROM dbo.ttdilc101201, dbo.ttipcs021201 ,dbo.ttiitm001201
WHERE dbo.ttdilc101201.t_cwar = '12'
AND dbo.ttdilc101201.t_loca = 'BULK STO'
and dbo.ttdilc101201.t_strs > 0
and dbo.ttdilc101201.t_item = dbo.ttipcs021201.t_item
OR dbo.ttipcs021201.t_item = dbo.ttiitm001201.t_item



Thanks

 

[JohnDTampaBay]

I suggest using table aliases and moving your JOINs to the FROM clause instead of WHERE to make your code more manageable.

This query will return data from ttdilc101201 (dil) and ttipcs021201 (ipc) when the WHERE clause is satisfied. Data from ttiitm001201 (iit) will only be returned when dbo.ttipcs021201.t_item = dbo.ttiitm001201.t_item. Is this what you're looking for?

SELECT  dil.t_item
	, dil.t_cwar
	, dil.t_loca
	, dil.t_clot
	, dil.t_date
	, dil.t_strb
	, dil.t_stro
	, iit.t_dsca
	, ipc.t_dsca AS EXPR1
	, dil.t_strs
FROM   dbo.ttdilc101201 dil
	INNER JOIN dbo.ttipcs021201 ipc ON dil.t_item = ipc.t_item
		LEFT OUTER JOIN dbo.ttiitm001201 iit ON ipc.t_item = iit.t_item
WHERE  dil.t_cwar = '12'
AND   dil.t_loca = 'BULK STO'
AND  dil.t_strs > 0

--Angel
-----------------------------------
Every time I lose my mind, I wonder
if it's really worth finding.