user2305672 user2305672 - 1 month ago 8
MySQL Question

select all from several tables where one column is matching all

I'm trying to do something similar to this using php and mysql database:

SELECT * FROM accomodation,assessment,beneficiaries,site
FROM accomodation,assessment,beneficiaries,site
WHERE ITS_Code != "none"


ITS_Code
is mutual between all tables.

Thank you

Answer

Assuming that tables accomodation, assessment, beneficiaries, and site has equal number of columns, and you're only looking to list each table's content consecutively, you could do:

SELECT Column1, Column2, Column3 FROM accomodation
WHERE ITS_code <> "none"

UNION ALL

SELECT Column1, Column2, Column3 FROM assessment
WHERE ITS_code <> "none"

UNION ALL

SELECT Column1, Column2, Column3 FROM beneficiaries
WHERE ITS_code <> "none"

UNION ALL

SELECT Column1, Column2, Column3 FROM site
WHERE ITS_code <> "none"

If one of the table has unequal number of rows - say, site has only 2 columns - then you could SELECT NULL in its place, like:

SELECT Column1, Column2, Column3 FROM accomodation
WHERE ITS_code <> "none"

UNION ALL

SELECT Column1, Column2, Column3 FROM assessment
WHERE ITS_code <> "none"

UNION ALL

SELECT Column1, Column2, Column3 FROM beneficiaries
WHERE ITS_code <> "none"

UNION ALL

SELECT Column1, Column2, NULL AS Column3 FROM site
WHERE ITS_code <> "none"