-2

I feel like I'm missing something simple here...

Table A(Name, Value)

ABC  123
DEF  456
XYZ  789
NON  111

Table B(Name1, Name2, Color)

ABC  NULL  Red
NULL DEF   Blue

SQL query

select * 
from TableA A
left join TableB B on A.name = B.name1 or A.name = B.name2

Doing this will only return

A.name, a.value, b.color

ABC 123 Red
DEF 456 Blue

I need it to return

A.name, a.value, b.color

ABC 123 Red
DEF 456 Blue
XYZ 789 NULL
NON 111 NULL

It's excluding rows in the left table.

Miguel-F
  • 13,450
  • 6
  • 38
  • 63
user1231231412
  • 1,659
  • 2
  • 26
  • 42
  • 2
    Your query looks correct for the output you want. – Gordon Linoff Sep 13 '13 at 15:44
  • Your code here looks correct. I'd check that you're actually using this code, and haven't got the tables swapped (joining from B to A), and that you are doing a `LEFT JOIN`. – Kirk Broadhurst Sep 13 '13 at 15:47
  • Checked your query. it works correctly on Mysql – vcs Sep 13 '13 at 15:50
  • Here is the SQL Fiddle that shows that the above works in SQL Server (http://www.sqlfiddle.com/#!3/48d02/1). – Gordon Linoff Sep 13 '13 at 15:57
  • Checked with SQL. Works just fine. – vcs Sep 13 '13 at 15:57
  • The example above does work but it's just an example of what I'm doing and what my output is when I do expect it to be like any other standard left join. It should not exclude rows on the left but it does and I have no WHERE clause either. – user1231231412 Sep 13 '13 at 17:35
  • Then the example provided by you is insufficient to solve the problem. Please post the entire query with the table structure. Please see the answer by @CodeByMoonlight. – vcs Sep 14 '13 at 07:09

3 Answers3

1

Your comment to the first answer says "I have some other tables being joined this is just a simplified example". I would guess that:

1) One of those tables is joined to TableB in your query
and
2) This additional table is referred to in a WHERE clause
and
3) The relevant join is not a LEFT JOIN, and/or the WHERE clause is not accounting the NULL

These things will combine to negate the effect of your LEFT JOIN, such as in the example below:

select * 
from TableA A
left join TableB B on A.name = B.name1 or A.name = B.name2
left join TABLEC C ON b.Field1 = C.Field1
WHERE C.MyField = 'avalue'
MartW
  • 12,348
  • 3
  • 44
  • 68
  • I am joining on more tables but I only see the issue when joining on this table, I know it's difficult to diagnose it without seeing the actual query and data. When I comment out this join I get my expected and verified number of rows(96) when I do this left join I get 47. It's excluding any row in the left table that doesn't match the right. – user1231231412 Sep 13 '13 at 17:34
0

Assuming you need all the entries in Table A joined with matching entries in Table B, you need a query like this -

SELECT 
     A.Name, 
     A.Value, 
     B.Color 
 FROM 
     TableA A 
     LEFT OUTER JOIN TableB B 
         ON (A.Name B.Name1 OR A.Name = B.Name2)
Robert
  • 25,425
  • 8
  • 67
  • 81
DotThoughts
  • 759
  • 5
  • 8
  • Beat me to it. And here is some info for the OP - [difference between inner and outer join](http://stackoverflow.com/a/38578/1636917) – Miguel-F Sep 13 '13 at 15:44
  • 2
    So what's the difference between this and the OP's sample? The parentheses? Note that a `LEFT JOIN` is **the same** as a `LEFT OUTER JOIN` - the `OUTER` keyword is entirely optional. – Kirk Broadhurst Sep 13 '13 at 15:45
  • 1
    I thought left outer join was the same as left join also – paparazzo Sep 13 '13 at 15:49
  • @Miguel-F I understand how the joins should work, I just don't understand why this left join doesn't work as it should but thanks for the link. – user1231231412 Sep 13 '13 at 17:37
0

If you execute the following query

select A.name, a.num, b.color 
from TableA A
left join TableB B on A.name = B.name1 or A.name = B.name2

it will get the result

ABC 123 Red
DEF 456 Blue
XYZ 789 NULL
NON 111 NULL
Viji
  • 2,629
  • 1
  • 18
  • 30