Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- select distinct on two tables
May 11, 2005, 10:37 pm
rate this thread
I have two tables, both containing an 'authors' column. Is there a way
to get a unique list of authors from the two tables?
I tried SELECT DISTINCT `authors` from `table1`, `table2`;
but I got an "Column 'authors' in field list is ambiguous" error.
Is there also a query to return only the count of distinct authors from
the two tables?
Thanks for any help.
Re: select distinct on two tables
(SELECT `authors` FROM `table1`)
(SELECT `authors` FROM `table2`)
ORDER BY `authors`
See http://dev.mysql.com/doc/mysql/en/union.html for more information.
For aggregates over multiple tables, I think you'd have to start using
MERGE tables, but this can be used only if the tables have identical
Another method would be to use a TEMPORARY table:
CREATE TEMPORARY TABLE authorlist (`authors` VARCHAR(100));
INSERT INTO authorlist SELECT DISTINCT `authors` FROM `table1`;
INSERT INTO authorlist SELECT DISTINCT `authors` FROM `table2`;
SELECT DISTINCT `authors` FROM `authorlist`;
SELECT COUNT(DISTINCT `authors`) FROM `authorlist`;
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum