How to handle SQL joins if missing rows on particular table exist

Question

    SELECT
    fromData.name as fromname, toData.name as toName, prodData.prodname, 
    t1.`from_id`, t1.`to_id` , t1.`product_id` , t1.`title`, t1.`message`, t1.`senttime` , t1.`readstatus`, t1.`responded`, t1.`merchanthidden`
    FROM `inquiries` as t1 
    INNER JOIN users as fromData on t1.from_id = fromData.id 
    INNER JOIN users as toData on t1.to_id = toData.id 
    INNER JOIN products as prodData on t1.product_id = prodData.id 
    WHERE t1.id=13

Above query joins 3 tables (inquiries, users, products) together and gets data from each table.

Sometimes it is possible that items in the 'products' table get deleted. Trying to join products table by a deleted product id will fail the query.

Is there a way that I can assign 'prodData.prodname' a default value and execute query without failing in case of a missing item in products table ?

If the products table literally might not even exist, then I think you would need to handle this using dynamic SQL. — Tim Biegeleisen, May 31 '17 at 08:32
You’re aware of the different types of JOIN, and how they are handling "missin rows" …? — CBroe, May 31 '17 at 08:32
If you add a "Left Outer Join" on products.. that will return you all rows still even if no row exists. — Wheels73, May 31 '17 at 08:33
@TimBiegeleisen You missed: "***items in the*** `products` table get deleted" — Disillusioned, May 31 '17 at 08:36
Sounds like you could use a foreign key constraint between inquiries and products to stop people from deleting products for which you have related inquiries! — Lord Peter, May 31 '17 at 09:31

score 0 · Answer 1 · answered May 31 '17 at 09:04

Why don't use left join insted of inner join , The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

SELECT
    fromData.name as fromname, toData.name as toName, prodData.prodname, 
    t1.`from_id`, t1.`to_id` , t1.`product_id` , t1.`title`, t1.`message`, t1.`senttime` , t1.`readstatus`, t1.`responded`, t1.`merchanthidden`
    FROM `inquiries` as t1 
    INNER JOIN users as fromData on t1.from_id = fromData.id 
    INNER JOIN users as toData on t1.to_id = toData.id 
    LEFT JOIN products as prodData on t1.product_id = prodData.id 
    WHERE t1.id=13

How to handle SQL joins if missing rows on particular table exist

1 Answers1