Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How would I do this query?

Posted on 2014-12-31
4
Medium Priority
?
257 Views
Last Modified: 2015-01-07
Here's my sql:

select belt.student_id, belt.belt, belt.date, students.id, students.first_name, students.last_name from belt inner join students on belt.student_id=students.id where belt.belt='$belt' and students.status='Active'  order by students.last_name

Slam dunk! No problem!

But, my "belt" table has several rows per student with a unique data attached to each row. I want to select from the "belt" table only the most recent "belt."

So, say I want to see all of my red belts. In the "belt" table, I'll have a student listed like this:

George Bush | Black Belt | December 5, 2014
George Bush | Red Belt | October 14, 2014

In this instance, I don't want George to be listed in my recordset because while he WAS a red belt back in October, he is now a Black Belt and I want to see only those who are currently a Red Belt.

How do I structure my select statement to achieve that?
0
Comment
Question by:brucegust
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 80

Assisted Solution

by:arnold
arnold earned 668 total points
ID: 40525914
you would need to order by date describe group by user and try the use of distinct.

select distinct belt.student_id, belt.belt, belt.date, students.id, students.first_name, students.last_name from students inner join belt on belt.student_id=students.id and belt.belt="$belt" where students.status='Active'  order by students.last_name 

Open in new window


Your query Gould have only returned the one matched on the date.  

Something is a miss in your query if that is not the case, in your code output the query to the browser so that you can see the query matches what you expect

I.e query is:
select belt.student_id, belt.belt, belt.date, students.id, students.first_name, students.last_name from belt inner join students on belt.student_id=students.id where belt.belt='Red Belt' and students.status='Active'  order by students.last_name
0
 
LVL 25

Accepted Solution

by:
Tomas Helgi Johannsson earned 664 total points
ID: 40526630
Hi!

This query will give you the answer you are seeking.
select x.* 
from (
select b.student_id, b.belt, b.date, s.id, s.first_name, s.last_name 
from belt b, students s
where b.student_id=s.id 
and s.status='Active'  
and b.date = (select max(bb.date) from belt bb where bb.student_id = b.student_id )
)x 
where x.belt='$belt'
order by x.last_name

Open in new window


Regards,
      Tomas Helgi
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 668 total points
ID: 40527282
Here's an alternative approach:
SELECT
      belt.student_id
    , belt.belt
    , belt.date
    , students.id
    , students.first_name
    , students.last_name
FROM students
INNER JOIN (
            SELECT
                  student_id, belt
                , MAX(date) AS MAX_DATE
            FROM belt
            WHERE belt.belt = '$belt'
            GROUP BY
                  student_id, belt
      ) MAXDT ON students.student_id = MAXDT.student_id
INNER JOIN belt ON belt.student_id = students.id
               AND belt.belt = MAXDT.belt
               AND belt.date = MAXDT.max_date
WHERE students.status = 'Active'
ORDER BY students.last_name
;

Open in new window


Note; IF 2 records exist in BELT with the same date for a single student_id then solutions based on max(date) would fail to produce a single record for that student_id
0
 

Author Comment

by:brucegust
ID: 40536812
Thanks guys!

I wound up using a select within a select like this:

class belt_data {

	function belt_list($belt) {
	
	global $mysqli;

	$sql="select belt.student_id, belt.belt, belt.date, students.id, students.first_name, students.last_name, students.division, students.class from belt inner join students on belt.student_id=students.id where belt.belt='$belt' and students.status='Active' order by students.division, students.class, students.last_name";
	$query = $mysqli->query($sql);
		if(!$query)
		{
		$err="your select statement went south becasue ";
		$err.=$mysqli->errno.': '.$mysqli->error;
		trigger_error($err, E_USER_ERROR);
		}
	$array = array();
	
		while($row=$query->fetch_array())
		{
		//at this point you have to see if the belt that's being queried represents the most recent belt that they've been awarded
		$sql_1="select * from belt where student_id='$row[student_id]' order by date DESC LIMIT 1";
		$query_1=$mysqli->query($sql_1);
			if(!$query_1)
			{
			$err_1 = "your second query didn't fire because...";
			$err_1.=$mysqli->errno.': '.$mysqli->error;
			trigger_error($err_1, E_USER_ERROR);
			}
		$query_row=$query_1->fetch_object();
		
			if($row['belt']<>$query_row->belt)
			{
			continue;
			}
			else
			{
			$array[]=$row;
			}
		}
		
	return $array;	
	
	}

}

Open in new window


That got it! Thanks for your suggestions and assistance!
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

596 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question