SQL Join Query: Problem with multiple tables

Question

I want to do an SQL join query. I have three tables _A, _B, _C, say. ANd each table has a column named "level" with some items having value 1,2,3 in this column.

At first, I just wanted an item with level = 1 from table _A, and so wrote:

SELECT * FROM _A WHERE level = "1" ORDER BY RANDOM() LIMIT 1

which worked fine.

Now, I want to select a random item with level=1 from any one of the three tables. I wrote:

SELECT * FROM _A, _B, _C WHERE level = "1" ORDER BY RANDOM() LIMIT 1

It didn't work. The system crashed. But my reference book says this is the way to do it. Please help.

Answer 1

I agree with redfilter. However, why do you need three tables for this Rather than having an additional column in table A (ABC char(3))? And then each row that would have been in table _B would have a B in this column and each row that would have been in table _C would have a C in this column. You can then use your first select statement unchanged.

Answer 2

SELECT *
FROM (
    SELECT * FROM _A WHERE level = "1"
    UNION ALL
    SELECT * FROM _B WHERE level = "1"
    UNION ALL
    SELECT * FROM _C WHERE level = "1"
)
ORDER BY RANDOM() LIMIT 1