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
This is waht I made and it not working.
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
This is waht I made and it not working.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
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:
p.s. use always table alias names, it makes reading SQL statements much easier.
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;
p.s. use always table alias names, it makes reading SQL statements much easier.
>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?
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?
Premium Content
You need an Expert Office subscription to comment.Start Free Trial