Combining SQL Query Data Into Two Columns
Recently, I had to structure a SQL query data on a particular subject into a two column format. I want to pull a the first name, last name, and email of a swimmer who placed first in a swim meet.
SQL Syntax
Below is the SQL query I used. I was looking for a fellow named Kip. This uses a hypothetical data set which you can generate for yourself at https://github.com/treble37/sql_modeler
select 'first_name' as chr, first_name as value from swimmers
JOIN meet_relationships on swimmers.id = meet_relationships.swimmer_id
JOIN swim_meets ON meet_relationships.swim_meet_id = swim_meets.id
WHERE meet_relationships.swim_meet_rank = 1 AND swimmers.first_name = 'Kip'
UNION select 'last_name' as chr, last_name as value from swimmers
JOIN meet_relationships on swimmers.id = meet_relationships.swimmer_id
JOIN swim_meets ON meet_relationships.swim_meet_id = swim_meets.id
WHERE meet_relationships.swim_meet_rank = 1 AND swimmers.first_name = 'Kip'
UNION select 'email' as chr, email as value from swimmers
JOIN meet_relationships on swimmers.id = meet_relationships.swimmer_id
JOIN swim_meets ON meet_relationships.swim_meet_id = swim_meets.id
WHERE meet_relationships.swim_meet_rank = 1 AND swimmers.first_name = 'Kip'If I run the above, then I end up with the following data set pulled:
| first_name |
| last_name |
Try it for yourself
Use the sql_modeler rails application and use the rake db:seed command to generate your dataset. You may have to use a different name than ‘Kip’ since the names are randomly seeded.
Boot up the sqlite3 console
Type sqlite3 development.sqlite3 at the command prompt. Input the SQL statement above and check out the results.