从2个表中获取内容？

Question

I want a query to grab results from 2 different tables where a specific user has added the row. So my 2 tables look like this:

addresses

• id
• address
• user_id

emails

• id
• email
• user_id

So, I want to pull the content from both tables where the user_id row equals 1. For context, imagine each user can add multiple addresses and emails to the site and I want to grab them all to display on their profile pages.

So I tried this:

SELECT *  
  FROM addresses
  JOIN emails USING (user_id) 
 WHERE user_id = '1'

However this doesn't work - It either returns 2 of each row from the first table, or nothing at all. I also tried this:

SELECT * 
  FROM addresses, 
       emails 
 WHERE user_id = '1'

But this also returns nothing.

Answer 1

Simple Union Version:

Select user_id, address as details from addresses
where user_ID = 1
UNION 
Select user_id, email as details from emails
where user_ID = 1

Here is some quick reading on union: http://www.tizag.com/sqlTutorial/sqlunion.php

From Input

table1:                        table2

id  user_id  address           id    user_id   email
----------------------         ----------------------
1   1        Add1              1       1       Email1
2   1        Add2              2       1       Email2
3   2        Add3              3       2       Email3
4   2        Add4              4       3       Email4

The results will be

user_id     details
-----------------------
1           Add1
1           Add2
1           Email1
1           Email2