Solved

How would I do this query?

Posted on 2014-12-31
4
238 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 78

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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

737 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