0

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
qwaz
  • 1,285
  • 4
  • 23
  • 47
  • Please include example data to demonstrate the output you want for different input. Also, please investigate `LEFT JOIN` in place of your use of `,`. It is not possible to "not join" tables, what you have at present is `CROSS JOIN`, using syntax that was replaced in 1992. – MatBailie Mar 05 '14 at 11:25
  • Those tables are not joined, they all have data unrelated to each other, but each has status for its own purpose. And I just need to get all data with status '1' in one result from all three tables. It is confusing though. – qwaz Mar 05 '14 at 11:38
  • You may not WANT to join the tables, but what you have written IS joining the tables. What you have written is identical to `... FROM table1 CROSS JOIN table2 CROSS JOIN table3 WHERE ...` *(notice the repeated use of the word JOIN in there)*. I strongly suggest that you show us what the contents of the three input tables will look like in various situations and then show us the results that you want. – MatBailie Mar 05 '14 at 11:44
  • 1
    Without knowing more details about the behaviour you want, have you tried using `UNION`? Something like `SELECT 1, row_id FROM table1 WHERE status = 1 UNION ALL SELECT 2, row_id FROM table2 WHERE status = 1 UNION ALL SELECT 3, row_id FROM table3 WHERE status = 1`? – MatBailie Mar 05 '14 at 11:47
  • This helped. Could you please post it as an answer, so I could upvote it and accept it. Thank you. – qwaz Mar 05 '14 at 12:35

2 Answers2

1

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.

DustinEst
  • 11
  • 2
0

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.

Community
  • 1
  • 1
borjab
  • 11,149
  • 6
  • 71
  • 98
  • Assumes only one record per table has `status = 1`. – MatBailie Mar 05 '14 at 11:43
  • Yep, since there doesn't seem to be a short way, then I guess I'll have to use a separate query for each table. Thank you for the suggestion. – qwaz Mar 05 '14 at 11:44