I cannot find, after much searching, how this would be done.
My web searches get Pivots and Concats and Cases and Subqueries etc, none of it is quite solving the issue for me. The multiple-rows-to-single-row questions aren't helping me.
The problem:
There are Individuals in one table. There are Addresses (sometimes multiple ones) for those Individuals in another table. I need a query to put the multiple Addresses on a single row (in the appropriate column) for each Individual.
Here is a an MySQL Fiddle with the tables and the query:
In that SQL Fiddle the result has 9 records for 6 unique Individuals:
Number | Name | EyeColor | HairColor | Street | City | State | Zip | Street2 | City2 | State2| Zip2 | Street3 | ...
1 | John Smith | blue | red | 100 Pine Street | New York | NY | 10019 | | | | 0 | | ...
2 | Nancy Jones | green | red | 200 Pine Street | New York | NY | 10018 | | | | 0 | | ...
3 | Bobby Joe | blue | black | 310 Oak Street | New York | NY | 10018 | | | | 0 | | ...
7 | Little Lebowski | green | blond | 100 Apple Street | New York | NY | 10018 | | | | 0 | | ...
7 | Little Lebowski | green | blond | 200 Hickory Street | New York | NY | 10018 | | | | 0 | | ...
7 | Little Lebowski | green | blond | 1234 Pineapple Street | New York | NY | 10018 | | | | 0 | | ...
2 | Nancy Jones | green | red | 230 Golden Street | New York | NY | 10018 | | | | 0 | | ...
8 | Sarah Shepard | brown | brown | (null) | (null) | (null) | (null)| (null) | (null)| (null)| (null)| (null) | ...
This is the SQL Fiddle with what I need the final result to be:
The Final Result SQL Fiddle has 6 total records for the 6 unique Individuals and their multiple addresses in the columns on the Individual's row:
Number | Name | EyeColor | HairColor | Street | City | State | Zip | Street2 | City2 | State2| Zip2 | Street3 | City3 | State3 | Zip3 | Street4 | City4 | State4 | Zip4 | Street5 | City5 | State5 | Zip5 | Street6 | City6 | State6 | Zip6
1 | John Smith | blue | red | 100 Pine Street | New York | NY | 10019 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
2 | Nancy Jones | green | red | 200 Pine Street | New York | NY | 10018 | 230 Golden Street | New York | NY | 10018 | | | | 0 | | | | 0 | | | | 0 | | | | 0
3 | Bobby Joe | blue | black | 310 Oak Street | New York | NY | 10018 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
7 | Little Lebowski | green | blond | 100 Apple Street | New York | NY | 10018 | 200 Hickory Street| New York | NY | 10018 | 1234 Pineapple Street | New York | NY | 10018 | | | | 0 | | | | 0 | | | | 0
8 | Sarah Shepard | brown | brown | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
9 | Joe Profigliani | brown | brown | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0 | | | | 0
I am working with the MySQL tables that I have been given, by the way, although open to ways to built temp tables for the solution, the question is about how to combine the data as shown in the final result, not about the elegance of the original tables.
There could be thousands of records, although I don't expect any one Individual to have more than 6 Addresses. (Having it fail gracefully if there were more than the Address fields available would be handy but that isn't the core of the question.)
I hope this is something simple that I am just not asking the right question for.
How are you making your decision as to which (name, address) combination is the "correct" one?
Well, I think that is may be the first 50% of my problem. All of the addresses are "correct" in that they all get written to the Individual's row. For example IndividualNumber 7 is LittleLebowski, He has 3 addresses, which all three should appear on that Individual's row in the Final Result. My assumption is that the first one to "appear" goes in as the first address. (Temp table with an auto-incremented value?) Once I answer that question about the order of the records, then the other 50% of the problem is to write them to the corresponding columns on that Individual's row?