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:
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.
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
try
"SELECT * FROM table1 INNER JOIN table2 USING (user_id) WHERE user_id = '1'"
then try this one
SELECT address.table1 , email.table2 FROM table1 INNER JOIN table2 ON id.table1 ='1'
SELECT table1.*,table2.*
FROM table1 INNER JOIN
table2 ON table1.user_ID = table2.user_id
Where table1.user_id = 1
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
Expected Output
id user_id address id user_id email
----------------------------------------------
1 1 Add1 1 1 Email1
2 1 Add2 1 1 Email1
1 1 Add1 2 1 Email2
2 1 Add2 2 1 Email2
Here is a query using Cartesian Product
SELECT A.*,B.* FROM
(SELECT address FROM addresses WHERE user_id = 1) A,
(SELECT email FROM emails WHERE user_id = 1) B;
Here is a query using INNER JOIN
SELECT A.*,B.* FROM
(SELECT address FROM addresses WHERE user_id = 1) A
INNER JOIN
(SELECT email FROM emails WHERE user_id = 1) B
USING (user_id);
Both of these queries are refactored to bring one row each. There is no need to bring the entire dataset together before searching for user_id 1.
One thing to note: Please make user user_id is indexed in both tables:
ALTER TABLE addresses ADD INDEX (user_id);
ALTER TABLE emails ADD INDEX (user_id);
Here is something else: If user_id 1 has mutliple addresses and/or multiple emails here is what those two queries should look like:
Cartesian Product for Multiple Addresses/Multiple Emails
SELECT A.*,B.* FROM
(SELECT GROUP_CONCAT(address) AddressList FROM addresses WHERE user_id = 1) A,
(SELECT GROUP_CONCAT(email) EmailList FROM emails WHERE user_id = 1) B;
INNER JOIN for Multiple Addresses/Multiple Emails
SELECT A.*,B.* FROM
(SELECT GROUP_CONCAT(address) AddressList FROM addresses WHERE user_id = 1) A
INNER JOIN
(SELECT GROUP_CONCAT(email) EmailList FROM emails WHERE user_id = 1) B
USING (user_id);
Give it a Try !!!
UPDATE 2011-08-29 11:52 EDT
Since There is no guarantee a user_id would have both an address and an email address at the same time. You would have to perform a LEFT JOIN instead of an INNER JOIN since LEFT JOIN would have to favor at least one of those entites existing rather than the INNER JOIN compelling one to have a user_id with both entities. Try one of these:
SELECT
A.ListOfAddresses AddressList,
IFNULL(B.ListOfEmails,'<No Email Address>') EmailList
FROM
(SELECT GROUP_CONCAT(address) ListOfAddresses FROM addresses WHERE user_id = 1) A
LEFT JOIN
(SELECT GROUP_CONCAT(email) ListOfEmails FROM emails WHERE user_id = 1) B
USING (user_id);
or
SELECT
IFNULL(B.ListOfAddresses,'<No Address>') AddressList,
A.ListOfEmails EmailList
FROM
(SELECT GROUP_CONCAT(email) ListOfEmails FROM emails WHERE user_id = 1) A
LEFT JOIN
(SELECT GROUP_CONCAT(address) ListOfAddresses FROM addresses WHERE user_id = 1) B
USING (user_id);
Based on comments below the question, the question actually appears to be about sorting rows from different tables based on common criteria.
Beside having separate SELECTs and performing the sort client-side, a database server can perform the sort for you, roughly like this:
SELECT *
FROM (
SELECT
id addresses_id,
address address_addresses,
NULL emails_id,
NULL emails_email,
time
FROM
addresses
WHERE
user_id = 1
UNION ALL
SELECT
NULL addresses_id,
NULL address_addresses,
id emails_id,
email emails_email,
time
FROM
emails
WHERE
user_id = 1
)
ORDER BY
time -- Column not mentioned in question.
NOTE: Some returned rows will have NULLs at the end (these are from the addresses table) and some in front (these are from the emails).
This UNION is rather ugly and will get uglier with each new table though...