Search code examples
mysqldatabaseselectrelational-databasedatabase-table

How to select multiple tables in single query mysql? (some tables have no data yet)

I have 3 tables called patients, customers and deliveries. Those tables are in the same database called db.

All the tables equally have id, first_name, last_name, gender and only deliveries table has their own data. (the other 2 tables are currently empty.)

Now, I want to select all of them in 1 query but mysql throws an error this:

SELECT first_name, last_name, gender FROM paitents, customers, deliveries GROUP BY people LIMIT 0, 50000 Error Code: 1052. Column 'first_name' in field list is ambiguous 0.047 sec .

This is how I tried:

SELECT first_name, last_name, gender
FROM patients, customers, deliveries
GROUP BY people;

How do I select all of the tables even if some tables currently have no data?

Solution

  • All the tables equally have id, first_name, last_name, gender and only deliveries table has their own data. (the other 2 tables are currently empty.)

    Now, I want to select all of them in 1 query

    I suspect that you are looking for union all:

    SELECT first_name, last_name, gender FROM patients
UNION ALL
SELECT first_name, last_name, gender FROM customers 
UNION ALL 
SELECT first_name, last_name, gender FROM deliveries

    This will combine all records available in the 3 tables in the resultset. On the other hand, using an (implicit) cross join like you do would generate a cartesian product of the 3 tables, with 9 columns (3 * 3) in the resultset (that is, if you fix the ambiguity on column names that you currently have).

    If you want to eliminate duplicates accross tables, you can use union instead of union all.

    If you want to limit the number of records in the resultset, you can do this as follows:

    (
    SELECT first_name, last_name, gender FROM patients
    UNION ALL
    SELECT first_name, last_name, gender FROM customers 
    UNION ALL 
    SELECT first_name, last_name, gender FROM deliveries
)
ORDER BY id
LIMIT 5000

    Note that, functionaly this does require an order by clause, otherwise the ordering of the results is undefined (I assumed id).