CODEWITHSUNDEEP
mysqlsqlselectjoin
Jurijs Visockis
Jurijs Visockis

Reputation: 141

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

Hi I try to show data from two tables, but second table is empty

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

what query I can use for my request (if second table is empty I need only students list if not I need the result), I know that Inner JOIN shows records only if it exist

Upvotes: 2

Views: 4416

Answers (3)

DSommer
DSommer

Reputation: 1

As Saharsh Shah states (sorry, cannot comment), you have to use LEFT JOIN, and you could also add IFNULL code, so that for example, if you want the ST.name ST.surname and ST.class values use:

    SELECT  IFNULL(ST.name,''),IFNULL(ST.surname,''),IFNULL(ST.class,''), 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

So that you don't have NULL values on your selected fields.

Upvotes: 0

Solano
Solano

Reputation: 550

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

Left Join diagram

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

Inner join diagram

Edit: jarlh suggestion in order to work properly

Upvotes: 5

Saharsh Shah
Saharsh Shah

Reputation: 29051

You have to use LEFT JOIN to fetch the data from first table and matched data of second table.

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

Upvotes: 1

Related Questions