CPG CPG - 3 months ago 5
MySQL Question

SQL query with multiple unrelated tables

In my mySQL database there are four tables:

breakfast
,
dinner
,
supper
,
supper
.
Column names of these tables are the same. Everyone one of them has a column called '
accepted
' which is equal to either
0
or
1
.

I want to make the SQL query that gives me one random row from these four tables
WHERE 'accepted' column is equal to 0.

SQL query with only 1 table that works:

SELECT * FROM breakfast WHERE accepted=0 ORDER BY RAND() LIMIT 1


My version of SQL query with four tables that doesn't work:

SELECT * FROM breakfast, dinner, supper, dessert WHEREbreakfast.accepted=0
AND dinner.accepted=0 AND supper.accepted=0 AND dessert.accepted=0 ORDER BY RAND() LIMIT 1

Answer

Try like this,

I assume that your number of columns in all the four tables are same.

MySQL

   SELECT col1,col2
FROM (
    SELECT col1,col2
    FROM breakfast
    WHERE breakfast.accepted = 0

    UNION

    SELECT col1,col2
    FROM dinner
    WHERE dinner.accepted = 0

    UNION

    SELECT col1,col2
    FROM supper
    WHERE supper.accepted = 0

    UNION

    SELECT col1,col2
    FROM dessert
    WHERE dessert.accepted = 0
    ) T
ORDER BY RAND() LIMIT 1

SQL Server:

SELECT top 1 col1,col2
FROM (
    SELECT col1,col2
    FROM breakfast
    WHERE breakfast.accepted = 0

    UNION

    SELECT col1,col2
    FROM dinner
    WHERE dinner.accepted = 0

    UNION

    SELECT col1,col2
    FROM supper
    WHERE supper.accepted = 0

    UNION

    SELECT col1,col2
    FROM dessert
    WHERE dessert.accepted = 0
    ) T
ORDER BY newid()