SQL: one to many, select just one.

Question

I am writing a small tool for managing the people registered to an association.

I have a table USERS and a table CARDS. Each user can have more than one card (because cards expire).

With one query I would like to extract ALL user information plus ALL card information only for the most recent card (field: ISSUEDATE).

Here is a very basic example:

USERS table

IDUSER  NAME    SURNAME
------------------------
1       Robert  Hill

CARDS table

IDCARD  IDOWNER  ISSUEDATE   DESC 
----------------------------------------
1       1        2010-01-01  'CARD ONE'
2       1        2010-08-18  'CARD TWO'

Basically I would like to get back from my query these fields:

IDUSER  NAME     SURNAME  IDCARD  IDOWNER  ISSUEDATE   DESC
---------------------------------------------------------------
1       Robert   Hill     2       1        2010-08-18  'CARD TWO'

I tried, but I cannot manage :(

EDIT: What happens if there is NO CARD linked to the user? (one user is registered but the association did not give him/her any card yet). Is there any way to adapt this query in order to get back user information anyway? The idea is to get all possible information for each user. This information is later used via JSON on ad ExtJS application.

Thanks for your answers.

Answer 1

Option 1 with NOT EXISTS query:

select u.IDUSER, u.NAME, u.SURNAME,
       c.IDCARD, c.IDOWNER, c.ISSUEDATE, c.DESC
from USERS u
join CARDS c on u.IDUSER = c.IDOWNER
where not exists (select 1 from CARDS where IDOWDER = u.IDUSER and ISSUEDATE > c.ISSUEDATE)

Option 2 with LIMIT

select u.IDUSER, u.NAME, u.SURNAME,
       c.IDCARD, c.IDOWNER, c.ISSUEDATE, c.DESC
from USERS u
join CARDS c on u.IDUSER = c.IDOWNER
order by c.ISSUEDATE desc
LIMIT 1

Answer 2

select u.IDUSER, u.NAME, u.SURNAME,
       c.IDCARD, c.IDOWNER, c.ISSUEDATE, c.DESC
from USERS u
join CARDS c on u.IDUSER = c.IDOWNER and c.IDCARD in (
     /* select biggest IDCARD numbers that has same IDOWNER thus eliminating multiple rows and remaining only 1 for each IDOWNER */
     select MAX(IDCARD) as IDCARD
     from USERS join CARDS on USERS.IDUSER = CARDS.IDOWNER
     group by CARDS.IDOWNER

)

Answer 3

Altho LIMIT is not generally alowed in mysql subqueries, LIMIT 1 is an exception, so "Select just one" is a perfect for that case.

LEFT JOIN insures that you will also have users without card data (card data will be NULL, you can use ifnull(desc, 'No Card Issued') to display that at your will)

and subquery insures that only the most recent card data for the user will be selected.

SELECT * 
  FROM users u 
       LEFT JOIN cards c 
         ON c.idcard = (SELECT idcard 
                          FROM cards c1 
                         WHERE c1.idowner = u.iduser 
                         ORDER BY issuedate DESC 
                         LIMIT 1)