Mysql Query Help (JOIN)

[fabzster]

Hi

I have a query which i run to give me the monthly amount of money that needs to get loaded to our consultants cards.

Our system has changed and I need to get the same data but differently

The query needs to join three tables consultants,voucher,gift_cards.

The only difficulty I have is that in the gift card table the consultants may have more than one card number assigned to them as they may have lost thier previous card and been issued a new one, we keep the details of all cards for a history.

when I run the query I need only the latest card number to be displayed -(There is a column date_added) normally i would use

SELECT * FROM gift_cards WHERE id_number='{$id_number}' group by date_issued DESC LIMIT 0,1"

How do i apply this to my JOIN Query Below?

SELECT voucher.value
     , voucher.id_number
     ,cellc.name
     ,cellc.surname
     ,cellc.cell_number
     ,cellc.register_status
     ,cellc.store_id
     ,cards.card_number
     ,cards.issue_number
  FROM voucher
LEFT OUTER
  JOIN ( SELECT id_number
              ,name, surname, cell_number, register_status,store_id
           FROM consultants
         GROUP
             BY id_number ) AS cellc
    ON voucher.id_number = cellc.id_number
LEFT OUTER
  JOIN ( SELECT id_number
              ,card_number, issue_number
           FROM gift_cards
         GROUP
             BY id_number, date_issued) AS cards
    ON voucher.id_number = cards.id_number
WHERE date_added="2010-03-01"

I have tried adding the highlighted portion below but it doesnt work, I assume its trying to limit the value for everything

SELECT voucher.value
     , voucher.id_number
     ,cellc.name
     ,cellc.surname
     ,cellc.cell_number
     ,cellc.register_status
     ,cellc.store_id
     ,cards.card_number
     ,cards.issue_number
     ,cards.date_issued
  FROM voucher
LEFT OUTER
  JOIN ( SELECT id_number
              ,name, surname, cell_number, register_status,store_id
           FROM consultants
         GROUP
             BY id_number ) AS cellc
    ON voucher.id_number = cellc.id_number
LEFT OUTER
  JOIN ( SELECT id_number
              ,card_number, issue_number,date_issued
           FROM gift_cards
         GROUP
             BY id_number, date_issued DESC LIMIT 0,1) AS cards
    ON voucher.id_number = cards.id_number
WHERE date_added="2010-03-01"

My Appologies for the long post I hope someone can assist

THNX