[RESOLVED] Chaging a join statement

[Desbrina]

i can get the join working with just the cards2/user_inventory2 tables, but not the cards00 table. Joins are a new thing to me so i'm not that good at them, and i'm not sure if i have this one right

This is the join i have working

SELECT cards2.url, cards2.alttext, cards2.amount, cards2.type, cards2.deck
FROM user_inventory2
LEFT OUTER JOIN cards2 ON user_inventory2.card_id = cards2.ID
WHERE user_inventory2.name = 'Desbrina'
AND user_inventory2.status = 'collecting'
ORDER BY cards2.alttext

That code produces

url || alttext || amount || type || deck
http://midnighttempest.com/images/cards/fire01.png || Fire 01 || 20 || character || fire
http://midnighttempest.com/images/cards/fire02.png || Fire 02 || 20 || character || fire
http://midnighttempest.com/images/cards/fire12.png || Fire 12 || 20 || character || fire
http://midnighttempest.com/images/cards/fire13.png || Fire 13 || 20 || character || fire
http://midnighttempest.com/images/cards/fire20.png || Fire 20 || 20 || character || fire

Where as i want the gaps to be filled with fields from the cards00 table, so it looks like this. urls with 00.png are from the cards00 table, the rest from cards2

url || alttext || amount || type || deck
http://midnighttempest.com/images/cards/fire01.png || Fire 01 || 20 || character || fire
http://midnighttempest.com/images/cards/fire02.png || Fire 02 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire12.png || Fire 12 || 20 || character || fire
http://midnighttempest.com/images/cards/fire13.png || Fire 13 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire00.png || Fire 00 || 20 || character || fire
http://midnighttempest.com/images/cards/fire20.png || Fire 20 || 20 || character || fire

I want to see in the user_inventory2 table which cards are collecting (user_inventory2.status = 'collecting')

Then fetch all the rows from the cards2 table of the same set (so if the ID in the cards2 table is 6318, the cards2.deck = fire, so get all the fire cards from cards22)

Then check the cards.ID against the user_inventory2.card_id to see if the user has that card.

If the user has the card, then get the url, alttext, amount, type and deck from the cards2 table, if not get the same columns from the cards00 table