asked on

MYSQL Query help with Union's and Distinct references

I am working on an SQL query that would pull rows from three tables and order them by date. The structure of the tables is:

id - auto incrememnting integer
inquiry - the referenece to the inquiry that this comment relates to
date
comment - text

The problem is, inquiries can have multiple comments and I receive output with duplicating inquiry numbers. I would like a result set with only unique/distinct inquiry numbers, but including the word DISTINCT after each select does not solve this, any ideas?

(SELECT * FROM abc) UNION (SELECT * FROM def) UNION (SELECT * FROM ghi) ORDER BY `date` DESC

Open in new window

Guy Hengel [angelIII / a3]

what about this:

select * 
from ( SELECT * FROM abc 
 UNION SELECT * FROM def 
 UNION SELECT * FROM ghi
) 
ORDER BY `date` DESC

Open in new window

Rob--

ASKER

Thanks for the help, after running that query the following error occured:
#1248 - Every derived table must have its own alias

Guy Hengel [angelIII / a3]

sorry for that:

select * 
from ( SELECT * FROM abc 
 UNION SELECT * FROM def 
 UNION SELECT * FROM ghi
)  sq
ORDER BY `date` DESC

Open in new window

Rob--

ASKER

This query ran successfully, but it does not produce the same results. The id which is unique is showing up multiple times, which means multiple inquiry numbers. Any further thoughts?

Guy Hengel [angelIII / a3]

next step:

select * 
from ( SELECT * FROM abc 
 UNION SELECT * FROM def 
 UNION SELECT * FROM ghi
)  sq
GROUP BY id
ORDER BY `date` DESC

Open in new window

Rob--

ASKER

Thanks for the help, but this query still produced the same result

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Rob--

ASKER

It worked, thank you very much!

Rob--

ASKER

Thorough answer, thank you