SQL Joins

abarefoot
abarefoot used Ask the Experts™
on
I'm having some issues with a left outer join.  The problem is I'm only getting results if they exit in Table B but I need all results from Table A with the addition of it they are taxed.  

SELECT A.customer_id,
       A.customer_name,
       B.tax
FROM   A
       LEFT OUTER JOIN B
                    ON A.customer_id = B.customer_id 

Open in new window


Table A
customer_id	customer_name
1	                    Bob
2	                    Alex
3	                    Joe
4	                    Bill
5	                    Sue
6	                    Mike
7	                     Jill
8	                     Peg
9	                     Kip
10	                     Tony

Open in new window


Table B
customer_id	tax      shipping
1	        Y
2	        N
6	        N
7                       Y
9	        Y
10	        N

Open in new window


Results I'm getting
customer_id	customer_name	tax
1	       Bob	         Y
2	       Alex	         N
6	       Mike       	 N
7          Jill              NULL
9	       Kip	         Y
10	      Tony	         N

Open in new window



Results I'm looking for
customer_id	customer_name	tax
1	                Bob	        Y
2	                Alex	        N
3	                Joe	        NULL
4	                Bill	        NULL
5	                Sue	        NULL
6	                Mike	        N
7	                Jill	        NULL
8	                Peg	        NULL
9	                Kip	        Y
10	                Tony	        N

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Éric MoreauSenior .Net Consultant
Top Expert 2016

Commented:
do you have a WHERE clause by any chance? or do we see the full query?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
Just for kicks and giggles post the full query, as I see the alias A and B but not the table that is aliased by A and B.   Also the below would handle 'All rows in A, and the matching values of B if they exist', which is your stated requirement, so Éric's question about the WHERE clause seems to be in play here.

FROM A
   LEFT OUTER JOIN B ON A.customer_id = B.customer_id 

Open in new window

As an aside, a good way to understand JOINs is to do an images.google.com search for 'SQL JOIN', and eyeball the pretty Venn diagrams of JOINs and the T-SQL to pull them off.

Author

Commented:
Thanks for the help.  I just had my join all messed up.  Once I fixed my join the left outer join is working as expected.
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
So .. what was the answer here?

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