Hi all, I have a question about how best to approach my particular issue, which usually I can research for myself, but in this case I can't quite get my brain around it. If what I'm asking is only possible when working with the output in PHP, please let me know.
I have two tables: "releases" and "variations". The "releases" table contains many fields with extensive details about each release, whereas "variations" contains only a few fields describing small differences to the main release. Both contain the field "pagename", which will correlate when the variation is tied to the release. Both tables also both share "item_creator_username", and this is the field I'm searching by the WHERE clause (WHERE item_creator_username = 'x'). However, any item from the "variations" table is going to have limited information about the release itself. So in addition to getting items where the item_creator_username fields match, I also want to be able to fill in the gaps about the release itself using the "releases" table.
If I'm trying to get all the items the user "Webmaster" created, I use a UNION ALL to get items from both tables:
SELECT pagename, id, titlesort, catalog, photos FROM releases WHERE item_creator_username = 'Webmaster' UNION ALL SELECT pagename, id, titlesort, catalog, photos FROM variations WHERE item_creator_username = 'Webmaster' ORDER BY titlesort
So this is well and good. But how do I also get the missing information about the particular release from "varations" table to be populated by the "releases" table? If I do a UNION ALL, it doesn't seem to work. I end up creating a lot of NULL fields for "variations" in order to match up the number of tables, and even then I'm not getting the information I need:
SELECT pagename, id, titlesort, photos, catalog, date, released, title, country, category, subcategory, format tracklist FROM releases WHERE item_creator_username = 'Webmaster' UNION ALL SELECT pagename, id, titlesort, photos, catalog, null as date, null as released, null as title, null as country, null as category, null as subcategory, null as format, null as tracklist FROM variations WHERE item_creator_username = 'Webmaster' ORDER BY titlesort
Basically, I want all the null fields that will be ouputed by the "variations" table to be populated by the "releases" table where the 'pagename' is the same. The "variations" table has some fields which delineate how those fields are different from "releases" (as can see in the very first SELECT statement above): 'photos' and 'catalog'. When these fields in "variations" are not empty, I want those to take precedence. So basically, all NULL fields from the "variations" table I would like to be populated from the "releases" table. All non-null fields from "variations" table, output those.
I know my description is a little confusing -- mainly it's because I'm not entirely sure how to approach it, so I'm struggling a little on articulation. Any help would be welcome, many thanks in advance.