• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
MYSQL Query

#1
[eluser]Jacob156[/eluser]
I have two tables Member and History. I need to select members that the Status == inactive and the latest History entry for that member. I only need the most current entry per member.

Current Code

Code:
$this->db->select('Member.*, History.Note, AccountType.Name, date_FORMAT(History.CreatedDate, \'%b %e, %Y\') as StartDateF');
$this->db->join('AccountType','Member.AccountTypeID = AccountType.ID','left');
$this->db->join('History','Member.ID = History.CustomerID','left');
$this->db->where('Member.StatusCodeID',$sc_id);
$this->db->where('History.HistoryCodeID',$hc_id);
$this->db->order_by("History.CreatedDate", "desc");
$results = $this->db->get('Member');

Thanks
Jacob

#2
[eluser]missionsix[/eluser]
Have you tried adding DISTINCT to you select statement for member's?

#3
[eluser]Jacob156[/eluser]
I was unable to change results with Distinct.

I have changed to the following which returns the proper Member.* and History.ID but not the proper History.Note. It returns one of the other notes for the user.

Code:
$this->db->select_max('History.ID','hcid');
$this->db->select('Member.ID, Member.*, History.Note');
$this->db->join('History','Member.ID = History.CustomerID','left');
$this->db->where('Member.StatusCodeID',$sc_id);
$this->db->order_by("History.CreatedDate", "desc");
$this->db->group_by("History.CustomerID");
$results = $this->db->get('Member');

Is there any way to do this in SQL without having to loop through the results and pull the last History entry?

All I need to do is go through the members with a given status and pull the most recent history entry.

Thanks
Jacob

#4
[eluser]missionsix[/eluser]
I think you might be coming at it the wrong way then. Try to select from the history table first, and join the member. Select a distinct customerID from the table, join the member on that customerID and order by the creation date as you have now. Since you have more than one history note, this will give you the latest notes and return the associated member to that history note.


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2020 MyBB Group.