Jump to content

How to check if table row exists in MYSQL statement


galvin
Go to solution Solved by Barand,

Recommended Posts

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

Link to comment
Share on other sites

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 by Barand
  • Like 1
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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
  • Like 1
Link to comment
Share on other sites

  • 2 weeks later...

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

Link to comment
Share on other sites

  • Solution

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
  • Like 1
Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.