Select Multiple Tables When One Table Is Empty in MySQL

Select multiple tables when one table is empty in MySQL

Using two tables in the from clause is functionally equivalent to a cross join:

select  *
from    A
cross join
        B

This returns a row of A for every row in B. When B is empty, the result is empty too. You can fix that by using a left join. With a left join, you can return rows even if one of the tables is empty. For example:

select  * 
from    A
left join  
        B
on      1=1

As the condition 1=1 is always true, this is just like a cross join except it also works for empty tables.

How to select from multiple tables when some of the tables are empty in MySQL

Finally I have solved It,
I broke the whole thing into many select query based on FK from each table, so number of additional row returns and mapping has become easy.

Who ever is getting this kind of problem, if it is possible then break it into many select query instead of one.

MySQL - Select from 2 tables even the other table is empty

Use an explicit left join:

SELECT a.name, b.title
FROM Artist a
LEFT JOIN Tracks b
    ON a.id = b.artist_id;

As a side note, your current query is using the old school implicit join syntax. The version I gave above is the correct way of writing the join.

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

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).

MySQL - Select from table 1 if second table is empty or not not empty

Left join will do it for you.

SELECT ST.* , E.data, E.status
FROM `students` ST
LEFT JOIN `table2` E
ON E.id_student=ST.id_student and E.data LIKE '$year-$month-%'
WHERE ST.class='$class'
ORDER BY ST.surname, ST.name, E.data

It will give you ST data values even if E data is empty. Of course you have to take care of the null values in the columns from the empty table

Inner join only gives you information when the data is present on both tables

Edit: jarlh suggestion in order to work properly

SQL Select multiple even if empty

Your SQL needs a LEFT JOIN clause in place of your comma-join clause.

SELECT *
  FROM bed
  LEFT JOIN patient 
             ON patient_bed_id = patient_id 
 WHERE station = 'Station 1'
 LIMIT 12

An ordinary join like yours suppresses any rows from bed that have no corresponding patient rows. The LEFT JOIN keeps those unmatched rows from bed and returns null values for the patient. That makes real-world sense: a bed with a null patient is an empty bed.

I hope your hospital has at least a few empty beds in this horrible time of pandemic.

SQL: join select multiple tables with missing row

Use left join where the table (or the row) are empty on don't match

SELECT a.date_in, a.date_out, b.name, b.phone, b.birthdate,
b.country, b.hotel, b.room_nr, b.passport_nr, c.email,
d.size, e.name, GROUP_CONCAT(DISTINCT g.service), GROUP_CONCAT(DISTINCT     h.service)
, GROUP_CONCAT(DISTINCT i.time), GROUP_CONCAT(DISTINCT j.location)

FROM reservation a 
INNER JOIN rider b on  a.rider_id = b.id 
INNER JOIN user c on b.user_id = c.id  
INNER JOIN bike_size d on a.bike_size_id = d.id 
INNER JOIN bike e ON d.bike_id = e.id  
LEFT  JOIN services_reservation f on  a.id = f.reservation_id   
INNER JOIN services g on f.services_id = g.id
INNER JOIN bike_shipping_reservation i on a.id = i.reservation_id
INNER JOIN bike_shipping h ON h.id = i.bike_shipping_id
INNER JOIN bike_shipping_location j on i.bike_shipping_location_id = j.id
where a.id = 80 

Select two tables when one table is empty or not empty

If you want to select every row of eventorg regardless of whether a corresponding record in acara exists, then you need a RIGHT JOIN, not a LEFT JOIN (or reverse the tables in your FROM and LEFT JOIN clauses).

As Matthew referenced in his comment, Jeff Atwood's Venn diagrams are nice way to visualize it: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Insert multiple records if table is empty

Test

LOCK TABLES `t002_seat_mst` AS t1 WRITE, `t002_seat_mst` AS t2 READ;

INSERT INTO t002_seat_mst AS t1
SELECT * 
FROM ( SELECT 2020 a,1 b,1 c,0 d,0 e,1 f,0 g,'228A' h UNION ALL
       SELECT 2021,1,2,0,0,1,0,'229A' UNION ALL
       SELECT 2022,1,3,0,0,1,0,'231A' UNION ALL
       SELECT 2023,1,4,0,0,1,0,'233A' UNION ALL
       SELECT 2024,1,5,0,0,1,0,'235A' UNION ALL
       SELECT 2025,1,6,0,0,1,0,'237A' UNION ALL
       SELECT 2026,1,7,0,0,1,0,'239A' UNION ALL
       SELECT 2027,1,8,0,0,1,0,'240A' UNION ALL
       SELECT 2028,1,9,0,0,1,0,'242A' UNION ALL
       SELECT 2029,1,10,0,0,1,0,'244A' UNION ALL
       SELECT 2030,1,11,0,0,1,0,'246A' ) data
WHERE NOT EXISTS ( SELECT NULL
                   FROM t002_seat_mst AS t2 );

UNLOCK TABLES;

---

Related Topics