Monday, March 7, 2016

SQL JOINs y u no do

So I'm working on the Flower Town database/script, trying to write queries for more detailed reports, which so happen to include the things that the player hasn't yet done.  So, LEFT JOIN, right?

Well, sorta.  It works, but not completely.

Background: I have a table with two columns: breed_id and color_id.  They're foreign keys to a table of all breeds of the game's flowers and a table of all colors of the game's flowers, respectively.  It's indexed by the combination of the two, which is always unique.  This table contains data that never actually changes, grows, or shrinks; it's used as a reference for a second identically structured table.  This second table's contents reflect what the user has actually accomplished in-game.  Normal usage is totally fine, I can pull out anything about what the user has already done.  So, it would be logical that, using LEFT JOINs, I could easily pull out data on what the user still needs to do, right?

I said "sorta" above for a reason.  So long as both breed_id AND color_id are NULL, it works beautifully.  Related to a few other table columns here and there, I can get data back on things the user has yet to do in-game.

Because most breeds can be grown in multiple colors, though, it doesn't work completely.  I am finding myself unable to get data on which colors the user has yet to grow for a given breed, if they've grown some but not all colors for that breed.  I've mangled the JOINs and table relations and WHERE clause every way I can possibly think of, including trying to split certain parts off into subqueries here and there, all to no avail.

I may know SQL, but I'm no SQL god.  If anyone happens to be reading this who IS an SQL god (or, at the very least, knows more than me), by all means, drop some knowledge in the comments.  Relevant info: it's MySQL, because this is web development and I'm not an RDBMS snob.

No comments:

Post a Comment

I moderate comments because when Blogger originally implemented a spam filter it wouldn't work without comment moderation enabled. So if your comment doesn't show up right away, that would be why.