This last week I have spent a significant amount of time dealing with mildly complex SQL queries for our game. At least they are complex by my standards (your mileage may vary). My first problem I needed to tackle is the structure of our database. In the grand scheme of things, the database is rather straightforward but a certain many-to-many relationship has definitely been causing havoc on my brain.
With the structure out of the way, I moved on to problem number two. The problem being, how do we retrieve all of the card entries from the cards table that belong to a specific user AND deck so that we can feed that into our script that handles creating decks to play the game. My first stab at it worked after a few tries however it makes you want to puke at first sight.
SELECT
cards.cardID,
cards.cardName,
cards.description,
cards.portrait,
cards.summon,
cards.elementString,
cards.cost,
cards.life,
cards.attack,
cards.defense,
cards.cardMax,
cards.spellValue,
cards.spellType,
cardInDeck.cardCount
FROM cards
JOIN users ON
users.userID = cards.userID
JOIN decks ON
users.userID = decks.userID
JOIN cardInDeck ON
decks.deckID = cardInDeck.deckID
WHERE
users.userID = '$userID' AND
decks.deckID = '$deckID' AND
cardInDeck.cardID = cards.cardID;Yuck!
Turns out I really only need information from two tables and following the foreign key lines from table to table to accomplish my joins was a terrible idea. One single join should solve this issue.
SELECT
cards.cardID,
cards.cardName,
cards.description,
cards.portrait,
cards.summon,
cards.elementString,
cards.cost,
cards.life,
cards.attack,
cards.defense,
cards.cardMax,
cards.spellValue,
cards.spellType,
cardInDeck.cardCount
FROM cards
JOIN cardInDeck
USING (cardID)
WHERE
userID = '$userID' AND
deckID = '$deckID'Much better. Only one JOIN needed!
But wait, this code is still pretty smelly. That is one long list of columns to select from the cards table. In fact, it is every column in the cards table!
SELECT
cards.*,
cardInDeck.cardCount
FROM cards
JOIN cardInDeck
USING (cardID)
WHERE
userID = '$userID' AND
deckID = '$deckID'Now we are finally getting somewhere. This query retrieves all the cards that are in the deck along with their associated info and the count of how many should be in the deck from the cardInDeck table.
The Next Trouble
Following that, I needed to create a query that would do something very similar but indeed quite different. In order to edit a deck that a user has created we need to retrieve every card they have ever made as well as the number of individual cards they have included in the deck so far. So in this case we need ALL the user’s cards and the number of each assigned to the deck. Let us bring in the magic of LEFT JOIN and separating conditionals. For my first few attempts I could not figure out a way to do different conditionals on my selected table and the JOIN. However, it is possible! We simply must use conditionals in the JOIN statement and then another conditional on the WHERE statement that refers to SELECT.
SELECT
cards.*,
cardInDeck.cardCount
FROM cards
LEFT JOIN cardInDeck
ON cards.cardID = cardInDeck.cardID AND
deckID = '$deckID'
WHERE
userID = '$userID'So in this case, I select everything from cards where userID matches the desired userID. Then, perform the join for matching cardID’s AND the desired deckID. So userID applies to the SELECT and deckID applies only to the JOIN. Obviously this is some fairly rudimentary SQL work but figuring it out was quite satisfying. Here’s to the next inevitable SQL query that must be resolved for our game. Cheers!
