Join 2 tables and show everything from left table.

corey gashlin
corey gashlin used Ask the Experts™
on
I have 2 tables I am trying to join. i have tried joining but it is not working how I would like.

Table 1 - OCI_DISCIPLINES - Shows as below -
discipline
Mechanical
Electrical
Plumbing
Fire Protection
Cx
Low Voltage
Report
Study
CAD
BIM

Table 2 - OCI_VOODOO Shows as below

Department      proj_id      timing
CAD      2147      21h 0m
Cx      2147      73h 20m
Electrical      2147      40h 30m
Mechanical      2147      88h 30m
Plumbing      2147      42h 0m

Trying to get Table1 to show all the rows for disciplines and join up to table 2.

Result I am looking for is this.

Department      proj_id      timing
CAD      2147      21h 0m
Cx      2147      73h 20m
Electrical      2147      40h 30m
Mechanical      2147      88h 30m
Plumbing      2147      42h 0m
Low Voltage 2147        0
Study          2147           0
Fire Protection 2147     0


SELECT        dbo.OCI_DISCIPLINES.discipline, dbo.OCI_VOODOO.proj_id, dbo.OCI_VOODOO.timing, dbo.OCI_DISCIPLINES.id
FROM            dbo.OCI_DISCIPLINES LEFT OUTER JOIN
                         dbo.OCI_VOODOO ON dbo.OCI_DISCIPLINES.discipline = dbo.OCI_VOODOO.Department  Where OCI_VOODOO.proj_id=2147

Open in new window



This is waht I made and it not working.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
For those values in Table 1 that are not in Table 2 (Fire Protection, Report, Study), explain for us the logic in assigning a 2147 for proj_id.

Author

Commented:
The Proj_id only Exists in Table 1.
Senior Developer
Commented:
Well, once you filter in the WHERE clause on the JOINed table, it becomes a INNER JOIN, when you ask for an actual value. This kind of filter must be placed in the JOIN condition in your case:

SELECT D.discipline ,
       V.proj_id ,
       V.timing ,
       D.id
FROM   dbo.OCI_DISCIPLINES D
       LEFT JOIN dbo.OCI_VOODOO V ON V.Department = D.discipline
                                     AND V.proj_id = 2147;

Open in new window


p.s. use always table alias names, it makes reading SQL statements much easier.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
>The Proj_id only Exists in Table 1.
In your Table 1 sample data there is only one column that is a varchar() Mechanical, Electrical, etc. and I don't see a 2147 value.

So my question is, if you wish to display ALL rows in Table 1 and ONLY the matching values in Table 2, of which one of them is proj_id = 2147, how do you know that for rows in Table 1 and NOT in Table 2 what the proj_id is?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial