Link to home
Create AccountLog in
Avatar of corey gashlin
corey gashlin

asked on

Join 2 tables and show everything from left table.

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.
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

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.
Avatar of corey gashlin
corey gashlin

ASKER

The Proj_id only Exists in Table 1.
ASKER CERTIFIED SOLUTION
Avatar of ste5an
ste5an
Flag of Germany image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
>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?