MySQL Union, table priority and fake columns

Question

I am trying to query 3 mostly unrelated tables. The relation for all three is one column (an email address field). I have created a 'union all' and pulled in email from all 3 tables and then grouped at the end to make sure there are no duplicates (I have tried union too).

My problem is that it all works fine, but I need to know if each email address is 1, 0 or NULL on the first table. I have tried pulling in the column and then adding to '"" AS Col1' to the other tables (avoid error 1222, match up the columns). I can do this but the data from table 1 gets overwritten by 'nothing' from table2 and then again by nothing in table 3.

Here's the query:

SELECT * FROM (
SELECT email AS emails, COUNT(email) AS qty, '' AS custCountry, '' AS custID, '' AS cName, active AS newsletter
    FROM newsletter
    WHERE email != '' 
    GROUP BY email

UNION ALL

SELECT email AS emails, COUNT(email) AS qty, '' AS custCountry, '' AS custID, '' AS cName, '' AS newsletter
    FROM orders 
    WHERE email != ''
    GROUP BY email

UNION ALL

SELECT email AS emails, COUNT(email) AS qty, country AS custCountry, customerID AS custID, countries.name AS cName, '' AS newsletter
    FROM customers
        LEFT JOIN countries ON customers.country = countries.zoneID
    WHERE email != ''
    GROUP BY email  
) AS newtable

GROUP BY emails
ORDER BY qty DESC

It works until I add the 'active' column, at which point it overwrites whatever was in the active column with nothing. I need the third table there as it pulls in location data that I need to retain. If I were to put the first table last it would give me 'active' but overwrite the location columns.

I'm no MySQL pro (take a look!) so any help greatly appreciated.

score 0 · Answer 1 · answered Apr 04 '11 at 17:48

0

What problem are you trying to solve? Do you want a list of unique email address from all 3 tables put together? If yes, you can use this:

select email from A union select email from B union select email from C

The grouping is not proper in your snippet. For a group by query you can have only those columns in the select clause which are either present in the group by clause or are aggregate functions such as min(), max(), avg(),count()

MySQL wont throw an error for this but will show some random values in the invalid colu. Other databases, namely Oracle would throw an error.

answered Apr 04 '11 at 17:48

Dojo

5,374
4
49
79

Thanks Priyank. I can get a list of email addresses combined but one table has a column that I want to pull the data from. As I am using union all the selects need to have the same number of columns so I need to fake them. Problem is that the fake table in the last select will overwrite the information from the first and second. – William Doherty Apr 05 '11 at 08:22
I guess I need a way of adding nothing to the fake columns so that it doesn't overwrite the data from the first. One solution would be to put the table with the actual data in a real column last so it doesn't get overwritten, but the last table pulls data in from real columns that are being faked above it. Hence, they'd get overwritten too. – William Doherty Apr 05 '11 at 08:25

score 0 · Answer 2 · answered Apr 05 '11 at 08:42

0

Ok. Simple really. Just join the data you need after selecting the union of tables. It's really obvious and probably harder to try and explain my problem than just get on and sort it. Thanks though.

answered Apr 05 '11 at 08:42

William Doherty

65
5

MySQL Union, table priority and fake columns

2 Answers2