CODEWITHSUNDEEP
mysqlsql
user2891518
user2891518

Reputation: 189

mysql join two tables when value exists in one

I have two tables of customer information, Tables A and B. They share no columns besides the ones found in the query, and all customers from Table B can be found in the much larger Table A.

Whenever I try to combine the customer data I end up eliminating the customers in A but not in B, or I end up getting way too many rows by getting repeat entries. How do I join the tables so I retain all of Table A and only add Table B columns to the customers found in both? Here is my best attempt:

select a.*, b.*
from table a
join table b
on a.id = b.id
where a.name = b.name ##OR?

**That will give me everything I need EXCEPT it only gives me the customers in both tables. I think I need to include some type of or statement to allow for cases where the customer is found in Table A but not Table B but I'm not sure how to set that up.

Upvotes: 2

Views: 7853

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

select id, NULL as somecol1, NULL as somecol2 --add more columns as needed
from tablea
UNION
select id, somecol1, somecol2 --add more columns as needed
from tableb
where id in (select id from tablea)

You can use union to achieve what you need.

Upvotes: 2

FirebladeDan
FirebladeDan

Reputation: 1069

Here's a good visual cheat sheet. http://www.codeproject.com/KB/database/Visual_SQL_Joins/Visual_SQL_JOINS_orig.jpg

enter image description here

Upvotes: 4

Related Questions