Combining output from two tables in different ways

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.

Example:

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

Open in new window


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

Open in new window


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.
Coral SAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Are you sure you want a UNION ALL here and not a JOIN?

A UNION combines two recordsets from different queries to produce one long recordset i.e. first query has 10 rows, second query has 20 rows result of UNION will be 30 rows (assuming no duplicates).

A UNION ALL leaves the dupes but otherwise is identical.

A JOIN is when you need to extend the columns of a recordset to include data from a related table. Example table 1 has 10 columns and table 2 (related to table 1) contains 5 columns a join of both tables (using all columns) would result in a row with 15 columns

From your description I am guessing you want

SELECT r.pagename AS ReleasesPagename, r.id AS ReleasesId, r.titlesort, r.photos, r.catalog, r. date, r.released, r.title, r.country, r.category, r.subcategory, r.format, r.tracklis,
v.pagename as VariationsPageName, v.id AS VariationsId, v.titlesort, v.photos, v.catalog
FROM releases r LEFT JOIN variations v
ON r.pagename = v.pagename
WHERE r.item_creator_username = 'x'
ORDER BY r.titlesort

Open in new window

NB: I don't have the structure of your tables so I don't know which fields are from which table. In the SELECT you will see I have prefixed the fieldnames with the table aliases (x and v) to indicate from which table those fields must be taken. You will need to modify (and possibly extend as required) these aliases so that the fields match to the correct table.

If tables contain the same fieldname and you want to select both then make sure you alias the field name so that you can access it in the result. For instance if both tables contain an id field and you need both ids then you would do something like this (also in sample above)

SELECT r.id as ReleasesID, v.id as VariationsId, ...

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
awking00Information Technology SpecialistCommented:
Perhaps something like the following -
SELECT r.pagename, coalesce(r.id,v.id) id, coalesce(r.titlesort,v.titlesort) titlesort, coalesce(r.photos,v.photos) photos, coalesce(r.catalog,v.catalog) catalog, r.date, r.released, r.title, r.country, r.category, r.subcategory, r.format, r.tracklist
FROM releases r, variations v
where r.pagename = v.pagename
and r.item_creator_username = v.item_creator_username
and r.item_creator_username = 'Webmaster';
0
awking00Information Technology SpecialistCommented:
Sorry, my comment is eerily similar to JulianHansen's that I didn't see until after I had posted.:-)
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Coral SAuthor Commented:
Hi Julian and awking00, thank you both for your help. Julian, I was quite sure UNION ALL was not the right approach, but that's all I had come up with so far (I changed the title to include "UNION ALL" in case my original title was too general, but I just changed it back to my original title since perhaps it's misleading). I will explore both of your solutions and comment again!
0
Coral SAuthor Commented:
This solves my issue perfectly, thank you both. I've just never used JOIN before, and every time I read examples I got a little confused, but now I feel like I'm getting it. Thanks again for your help!
0
Coral SAuthor Commented:
I'm at a loss a bit as to how to spread the points, so I just gave the most to the solution that came first and that I ended up copying most closely. Thank you!
0
Julian HansenCommented:
You are welcome.
1
NerdsOfTechTechnology ScientistCommented:
The key here is that if you want to connect the tables together by a common column, JOIN would allow you to associate the records from both tables via a JOIN. A UNION stacks the result-sets which match column count and data-type together.
union-all.png
join-visual.png
sql-joins.jpg
1
Coral SAuthor Commented:
I'm not sure if it's okay to ask a follow up question, but I'm having a small bit of confusion. When both a main release and a variation has been submitted by the same person, I want to separate the main release and the variation into two items. I can do that when there's only one variation submitted by the same person, but the problem I run into is if a person has submitted multiple variations on a release, PLUS the main release. In that case, each variation comes up separately with the main release information so I can document each of those once, but I don't know how to make sure I document the main item only once, since the main release info is coming up multiple times with each variation. Is there a way I can make a check for that so I don't output the same main release twice, whether by PHP or MYSQL?

ETA: NerdsOfTech, that visual is extremely helpful, thank you for un-confusing it for me.
1
Coral SAuthor Commented:
Maybe I'll just put each pagename for the main releases into an array, and check the array each time to make sure I haven't already documented it.
0
NerdsOfTechTechnology ScientistCommented:
Thanks for the feedback.

For your follow up question, probably PHP would be the best way to go to filter any complex groupings.

Just to be sure, please elaborate using hypothetical input data that would match your problem case and your desired output data.

If this is part of the same query you want to enhance I am sure EE won't mind. If it is another query you need for this next question, a new question would be best.
0
Coral SAuthor Commented:
Oh okay, thanks for clarification on question etiquette. I was able to solve the issue myself in PHP, so thank you!
1
Julian HansenCommented:
but I don't know how to make sure I document the main item only once, since the main release info is coming up multiple times with each variation. Is there a way I can make a check for that so I don't output the same main release twice, whether by PHP or MYSQL?
This is very common in database applications that use Joins - you end up with

parent A child B
parent A child C
parent D child E
parent D child F
parent G child H
....

Open in new window

How you deal with this depends on how you want your output to look. If you want to use the parent as a header and list the children under it then all you do is keep a flag that says what parent you are on.
// ASSUMES A MySQLi CONNECTION OBJECT $conn
$parent = false;
while($row = $conn->fetch_object()) {
   if ($parent != $row->COLUMNTHATIDSPARENT) {
     if ($parent) {
        // output new parent 
     }
     $parent = $row->COLUMNTHATIDSPARENT;
   }
   // Output child here
}

Open in new window

This code loops through the recordset - if it finds a new parent - outputs the parent code otherwise it outputs child code under the last parent.
How you output is dependent on your requirements.
2
Coral SAuthor Commented:
Thank you Julian for expounding more on this issue. Your function is useful and your description made a lot of sense. I appreciate the help on a side issue even after I had accepted the solution, so thanks again.
0
Julian HansenCommented:
You are welcome.
Code correction though - using the algorithm above you don't need to do the if ($parent) check.
// ASSUMES A MySQLi CONNECTION OBJECT $conn
// ASSUMES DATASET IS SORTED ON PARENT
$parent = false;
while($row = $conn->fetch_object()) {
   if ($parent != $row->COLUMNTHATIDSPARENT) {
     // output new parent 
     $parent = $row->COLUMNTHATIDSPARENT;
   }
   // Output child here
}

Open in new window

2
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Images and Photos

From novice to tech pro — start learning today.