Solved

How would I do this query?

Posted on 2014-12-31
4
242 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 49

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

Secure Your WordPress Site: 5 Essential Approaches

WordPress is the web's most popular CMS, but its dominance also makes it a target for attackers. Our eBook will show you how to:

Prevent costly exploits of core and plugin vulnerabilities
Repel automated attacks
Lock down your dashboard, secure your code, and protect your users

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
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…

717 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