[Resolved] LEFT JOIN or DISTINCT...

[ghjr]

Here are the two tables:

construction
--constructionid
--constructionname

report
--reportid
--constructionname

I need to pull out all the unique construction names (there are two) from both tables and display then on a <select> box. And here are my attempts at performing the queries:

1st Try:
SELECT DISTINCT report.constructionname, construction.constructionname FROM report, construction
This returned the same entry twice, and left out the other unique one.

2nd Try:
"SELECT DISTINCT A.constructionname, B.constructionname FROM report A LEFT JOIN construction B ON A.constructionname!=B.constructionname"
This one did perform the distinct but only returns one of the construction names.

I really don't understand the LEFT JOIN that much as too analise what's going on. Could anyone shine a light??

ghjr

[nemonoman]

TRY THIS

SELECT DISTINCT A.constructionname, B.constructionname FROM report A LEFT JOIN construction B ON A.constructionname=B.constructionname
WHERE
B.constructionname=''

---alternatively
WHERE
B.constructionname IS NULL

[tomhath]

When you say "from both tables" I assume you mean you want all the names, whether they appear only in the first table, only the second table, or in both.

If your database supports the UNION (mysql might not), you can do it this way:

SELECT DISTINCT report.constructionname from report
UNION
select distinct construction.constructionname FROM construction

Otherwise you will need to do it with two queries and pick the unique ones out in php.