How to check if table row exists in MYSQL statement

[galvin]

Say you have three tables like this...

 

STUDENTS (studentid, name)

 

CLASSES (studentid, classname)

 

GRADES  (studentid, grade)

 

And not all students have grades yet.  If you're doing a mysql query like below, is there anything you can add to the statement to check if a grade exists for each student?   So that if while lopping through the results of the main query, I can easily do something with the students who don't yet have a grade yet (like maybe mark their names in red).

 

I can do this by putting a whole separate mysql query inside the looping of the main query, but that seems terribly inefficient to call the database again for EVERY student.

$sql = "SELECT * FROM students, classes, grades
	WHERE students.studentid = classes.studentid
        ORDER BY students.lastname";
	$info= mysqli_query($connection, $sql);
	if (!$info) {
	die("Database query failed: " . mysqli_error());
	} else {
           //code
        }

Thanks!

Greg

[Barand]

1. Dont use "... FROM A, B, C WHERE ... " syntax - use explicit joins

2. Don't use SELECT *, specify the columns you need

 

3. In this case, where you want to list a student even if there is no matching grade record, then you need a LEFT JOIN

SELECT s.studentid
  , s.lastname
  , c.classname
  , g.grade
FROM students s
    INNER JOIN classes c USING (studentid)
    LEFT JOIN grades g USING (studentid)
ORDER BY s.lastname

edit : grade will be null where there is no matching grade record

Edited November 24, 2014 by Barand

[galvin]

That's beautiful, thank you for the advice!!

[Barand]

... thank you for the advice!!

 

You're welcome. As well as making your queries more understandable in terms of both function and structure, those tips will make your queries more efficient.

[galvin]

Hopefully quick follow-up...

 

What if each student gets multiple grades and I only want to check if a certain grade exists?  For example, what if in grades, there is a field for "period" and it can be 1, 2, 3 or 4.  How could I alter the MySQL query to see if a grade exists for SPECIFICALLY period 3?  In other words, if period 1 and 2 grades are there,  I don't care. I only want to know if period 3 is there.

 

But I still want all the info about every student as originally intended.

 

Can this be done in the mysql query, or do I need to get that data and check it after with PHP?  Like maybe add g.period, as in...

SELECT s.studentid
, s.lastname
, c.classname
, g.grade
, g.period
FROM students s
INNER JOIN classes c USING (studentid)
LEFT JOIN grades g USING (studentid)
ORDER BY s.lastname 

 And then run some if statements on $row['g.period'] once I get all the data and put it in an array?  I imagine that would work, but if I can do it all in the MySQL query, that would be preferred.

 

Any input appreciated as always.

 

Thanks

Greg

[Barand]

Because you are using a LEFT join to the grade table then put the condition in the ON clause

SELECT s.studentid
, s.lastname
, c.classname
, g.grade
, g.period
FROM students s
INNER JOIN classes c USING (studentid)
LEFT JOIN grades g ON s.studentid = g.studentid  AND g.period = 3
ORDER BY s.lastname

[galvin]

Awesome, thanks again for your VERY helpful information!...

[galvin]

Sorry, one more follow-up to this main question...

 

Let's say I don't want to limit my search to this left join (i.e. LEFT JOIN grades g ON s.studentid = g.studentid AND g.period = 3) but would still like to have that information handy (i.e. still know when a student has an existing grade in period 3).

 

I imagine you can do some type of  "AS" statement instead of a LEFT JOIN?

 

Something along these lines, which not surprisingly didn't work when i tried

SELECT s.studentid
, s.lastname
, c.classname
, g.grade
, g.period
, (LEFT JOIN grades g ON s.studentid = g.studentid AND g.period = 3) as period_3_exists
FROM students s
INNER JOIN classes c USING (studentid)
ORDER BY s.lastname

Then while looping through the results, I can check if period 3 grades exists by looking for ($row['period_3_exists']==1).

[Barand]

I imagine you can do some type of  "AS" statement instead of a LEFT JOIN?

 

 

If you  find one, let me know.

 

I haven't a clue what you are asking unless it is something like this

SELECT s.studentid
, s.lastname
, c.classname
, SUM(CASE WHEN period=1 THEN grade ELSE NULL END) as Period_1
, SUM(CASE WHEN period=2 THEN grade ELSE NULL END) as Period_2
, SUM(CASE WHEN period=3 THEN grade ELSE NULL END) as Period_3
, SUM(CASE WHEN period=4 THEN grade ELSE NULL END) as Period_4
FROM students s
INNER JOIN classes c USING (studentid)
LEFT JOIN grades USING (studentid)
GROUP BY s.lastname

[galvin]

Sorry I'm really having trouble explaining   In plain english, I'm saying...I want to bring back a row for EVERY student in the "students" table, regardless of whether they have any entries in the "grades" table or not.  But I would like to know if they specifically have a record in the "grades" table for Period 3.

 

So, based on what you wrote, if it would make Period_3==1 if there was a grade for period 3, or Period_3==0 if there was NOT a grade for period 3, then theoretically that would be all I needed bc I could just run that check and know whether that student had a grade for period 3.

 

I apologize if this doesn't clear things up at all.

 

I have a feeling what you wrote is along the right lines of what I need so I will experiement with that and see what happens.

 

Thank you!

[galvin]

Barand, what you wrote is what I need, just didn't need the SUM part of it, only need the CASE (sorry again for my terrible explanation).

Thank you so much!

[kicken]

I want to bring back a row for EVERY student in the "students" table, regardless of whether they have any entries in the "grades" table or not.

That is exactly what a LEFT JOIN does. It returns all rows from the source (left) table, and either the matching rows or NULL for the join (right) table.

 

But I would like to know if they specifically have a record in the "grades" table for Period 3.

To limit the join to just Period 3, you'd add that to the join condition. You'll need to use an ON clause rather than USING.

SELECT s.studentid
, s.lastname
, c.classname
, g.grade
FROM students s
INNER JOIN classes c USING (studentid)
LEFT JOIN grades g ON g.studentid=s.studentid AND g.period=3
GROUP BY s.lastname

If a row exists, it will return that row's grade column. If no rows exist, it will return NULL.

 

Notice that the period=3 condition is part of the join condition, not in the WHERE clause.