How can I fetch from 2 different table in Postgres-CodePudding

Im trying to combine both labeled2015 and labeled2019 with if diseasetype = 3

Here sample of my query

SELECT * from labeled2019 labeled2015 inner join dataaaaa on diseasetype = 3 ;

Here my Tables:

Here my Tables

Sample of labeled2015:

Sample of labeled2015

Sample of labeled2019:

Sample of labeled2019

dataaaaa table is empty. By the way its not necessary to put query result in to dataaaaa table. Showing the result is enough.

Note that it gives me the [42702] ERROR: column reference "diseasetype" is ambiguous error.

Expected result:

Getting all the photo names whose diseasetype = 3

photoname | diseasetype
32_left          3
asdf433sd        3

CodePudding user response：

Try using 'union' operator. It can join query results from multiple tables into one result. So query will look kind of like this:

SELECT * 
from labeled2015 
where diseasetype = 3
union
SELECT * 
from labeled2019 
where diseasetype = 3

See the documentation: https://www.postgresql.org/docs/current/queries-union.html

CodePudding user response：

Since you would like to get the entries of the two tables as separate lines, this will be easier when just using UNION ALL or UNION instead of JOIN.

SELECT photoname, diseasetype FROM labeled2015 WHERE diseasetype = 3
UNION ALL
SELECT photoname, diseasetype FROM labeled2019 WHERE diseasetype = 3

UNION ALL is faster and should be prefered, but doesn't remove duplicates if the same entry occurs in both tables. So take UNION instead if you want to rule this out. Since you restrict your result on entries having diseasetype = 3, you can even write SELECT photoname, 3... in bot queries instead of the column name.