Solved

How would I do this query?

Posted on 2014-12-31
4
234 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 77

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 25

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 Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to count occurrences of each item in an array.

820 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