Here's a Curve Ball: Limit number of records on Left Join

[dlite922]

I've heard this may not be possible, but never hurts to ask.

I've got three tables, Case, Violator, Alias

Case is a court police case
Violator is the person
Alias is different names (aliases) that person has.

Here's my Query:

 
SELECT 
c.id , 
c.caseNumber , 
v.lastName , 
v.firstName , 
a.firstName  AS aliasFirstName, 
a.lastName AS aliasLastName, 
v.driverLicenseNumber , 
v.dateOfBirth , 
t.courtDate , 
c.status 
 
FROM 
`case` as c LEFT JOIN trial as t ON c.id = t.caseID, 
violator as v LEFT JOIN  alias as a ON v.id = a.violatorID 
 
WHERE 
c.violatorID = v.id AND 
( c.status IN ('CONT','ARR','DE','FTAP','OJW','PRO','STAY','WRNT')) 
 
ORDER BY 1 DESC;
 
 
//Here's some test results from fake data, some fields are cut off because of no room. 
 
+-----+------------+-----------+-------------+----------------+---------------+
| id  | caseNumber | lastName  | firstName   | aliasFirstName | aliasLastName 
+-----+------------+-----------+-------------+----------------+---------------+
| 117 | A001       | DOE       | JOHN        | NULL           | NULL
| 116 | 07PA36503  | AVENT     | CHRISTOPHER | NULL
| 115 | 07PA33856  | APPLEBY   | KENT        | NULL
| 114 | 07PA36620  | ANDERSON  | JOSHUA      | Josh
| 114 | 07PA36620  | ANDERSON  | JOSHUA      | Joshi
| 113 | 07PA35300  | ADNDERSON | DONALD      | NULL           | NULL
| 112 | 07PA36487  | AKHTAR    | NASER       | NULL           | NULL
| 111 | 07PA36488  | AKHTAR    | NASER       | NULL           | NULL
| 110 | CA0003     | ARANJUEZ  | ALAN        | NULL           | NULL
| 109 | CA0001     | CARINOS   | JOHNY       | Jimmy          | Car
| 106 | CA0002     | MILLS     | CORRINE     | NULL           | NULL
+-----+------------+-----------+-------------+----------------+---------------+
 
//expect result is for the second caseID 114 to not be displayed. 
 
 

As you can see CaseID 114 is listed twice because Joshua Anderson has 2 alias records (Josh and Joshi)

How can I change my query so that it only displays the first alias, and each case only ONCE, but i need it to search all the aliases.

That is, If i type in Joshi, it will still pull up that record, but the listing will display Josh, because that's the first alias.

Complicated I know.

Anybody up for the challenge if even possible?


Thanks,



Dan

[dlite922]

Okay, I feel stupid.

Just add GROUP BY c.id !!

Tada!!

Problem Solved.


Dan

[coolsti]

Just in case you are not aware of this option, if you wish to also retrieve a delineated list of all the aliases as one string variable (which you can display or manipulate after retrieving from the database), you can use the GROUP_CONCAT function when you have a GROUP BY clause.

The default deliniator in group_concat is a comma, but you can specify instead what should be used. For direct display purposes, I usually set the delineator to ', ' which is a comma followed by a space, as this looks better.