Solved

How would I do this query?

Posted on 2014-12-31
4
215 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
4 Comments
 
LVL 76

Assisted Solution

by:arnold
arnold earned 167 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 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 166 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 48

Assisted Solution

by:PortletPaul
PortletPaul earned 167 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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
The viewer will learn how to count occurrences of each item in an array.

758 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now