CODEWITHSUNDEEP
mysqlsql
qwaz
qwaz

Reputation: 1305

MySql: select from multiple not joined tables in one query

The following query works fine when all 3 tables have rows with status '1', but if one of the tables doesn't have a row with status '1' the entire query returns empty, even though other tables have rows with the requested status.

SELECT
table1.row_id as rowsone,
table2.row_id as rowstwo,
table3.row_id as rowsthree 

FROM
table1,
table2,
table3

WHERE table1.status = 1 AND table2.status = 1 AND table3.status = 1

Upvotes: 0

Views: 124

Answers (2)

DustinEst
DustinEst

Reputation: 11

I think that whats wrong with your file is that we all know that all the tables function separately but also all the table have an identical field name "status" right, the use of an AND condition is that all of the conditions must be met if one fails to satisfy the condition the whole of it would result to a false value or in your case 0, So what I would suggest is this:

SELECT table1.row_id as rowsone, table2.row_id as rowstwo, table3.row_id as rowsthree

FROM table1, table2, table3

WHERE status = 1

with this code it checks tables 1,2, and 3 if they have a status of one.

Upvotes: 1

borjab
borjab

Reputation: 11655

As workarround if you know that there is just one row you may use subqueries

SELECT ( SELECT title from table1 WHERE status =1),
       ( SELECT title from table2 WHERE status =1),
       ( SELECT title from table3 WHERE status =1)
 FROM DUAL;

If all the tables have a different number of records... you have to think about what kind of result you want. May be something like a full outer join which is not supported in MySQL. It will be faster and more elegant to make several queries.

Upvotes: 0

Related Questions