MySQL Need help constructing query: join multiple tables into single row

Question

Hello stackoverflowers,

Forgive me if this question has been asked and answered, I've searched and found a few that look similar but I'm too much of a novice with SQL to adapt them to my needs. Also forgive me if I don't use the correct terminology, I know it can be annoying when someone asks a question and they don't even know enough to be able to ask for what they need.

<2nd EDIT Mar22:>

Ok, I didn't understand what the source data actually looked like, and that is why I couldn't get what I wanted. Thanks to @goran for pushing me to get the real source tables to post here (which I'm still not going to post because I'm too lazy to redact them as appropriate for easy viewing and protection of the innocent, but I should have at least printed them out before asking). Below are newly revised example tables, but I still haven't figure out how to achieve the end goal of getting everything into a single row.

table_one:

id  name          total
5   John Doe      20

table_two:

id  product_id    price
5   51            17

table_three:

id  text       number
5   Subtotal   17
5   Tax        3
5   Total      20

What I'm looking for is something like this:

id name     total product_id price text     number text number text   number
5  John Doe 20    51         17    Subtotal 17     Tax  3      Total  20

I'm no longer certain how valid the information below is, but I'm leaving it here for the moment, hoping it won't confuse new readers of the question too much.

</EDIT Mar 22>

I'm helping a friend gather some data, and need to perform a query that results in a single row per record, but instead I get multiple rows. Here is an example of what I'm querying right now (simplified, hopefully not too much):

SELECT * FROM `table_one` AS t1 
INNER JOIN `table_two` AS t2 ON t1.id = t2.id 
INNER JOIN `table_three` AS t3 ON t1.id = t3.id 
WHERE 1

The result is:

id  text       number
5   Subtotal   17
5   Tax        3
5   Total      20

What I need is to create a query that results in something more like this:

id  text       number  text  number  text   number
5   subtotal   17      Tax   3       Total  20

Any assistance/guidance would be much appreciated.

Thanks!

--jed

Answer 1

You won't be able to get your result exactly as you want to at least because you have columns with the same names. Probably you may be able to get this result having the columns renamed but this approach will lead to hacky code and inability of proper result usage.

You should reconsider the further use of the records returned and update it in order to process the actual result as soon as it is the way SQL select should return data normally.

Answer 2

The output you're asking for is not a relation, since its attributes don't have unique names. So it's unreasonable to expect a relational database system to produce such a result. Which is not to say that some database systems can break the relational model and produce non-relational results in some cases; but this isn't likely to be one of them.

So you should instead have the application take the relation as input and transform it to the output you want. From the RDBMS perspective, that's a major part of the role of the application.

Answer 3

I think you did simplify it too much somewhere. The query you quote would return exactly what you want already. Here's an example (selecting from single table twice gives similar situation as what you have)

mysql> select * from test t1 join test t2 on t1.a = t2.a LIMIT 1,5;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    2 |    1 |    1 | 
|    1 |    1 |    1 |    2 | 
|    1 |    2 |    1 |    2 | 
|    2 |    2 |    2 |    2 | 
|    2 |    2 |    2 |    2 | 
+------+------+------+------+
5 rows in set (0.00 sec)

Mysql has no problem to label the result set columns with same labels. I guess that your original query had select t1.* in select part.

If you want to refer to individual fields whose names are ambiguous you'll get

mysql> select a from test t1 join test t2 on t1.a = t2.a LIMIT 1,5;
ERROR 1052 (23000): Column 'a' in field list is ambiguous

And you have to specify exactly what you want (column aliases are optional, you can do t1., t2. as well)

mysql> select t1.a first, t2.a second from test t1 join test t2 on t1.a = t2.a LIMIT 1,5;
+-------+--------+
| first | second |
+-------+--------+
|     1 |      1 | 
|     1 |      1 | 
|     1 |      1 | 
|     2 |      2 | 
|     2 |      2 | 
+-------+--------+
5 rows in set (0.00 sec)

Edit 22MAR After a change in the sample data it seems that you want to turn several rows from one table into one. Here is a particular solution (assuming that you'll always have Tax, Total and Subtotal rows and that you are only interested in these rows).

SELECT t1.id, t1.name, t2.product_id, t2.price, t3a.number subtotal, t3b.number total, t3c.number tax
FROM `table_one` AS t1 
INNER JOIN `table_two` AS t2 ON t1.id = t2.id 
INNER JOIN `table_three` AS t3a ON t1.id = t3a.id and t3a.text = "Subtotal"
INNER JOIN `table_three` AS t3b on t3a.id = t3b.id and t3b.text = "Total"
INNER JOIN `table_three` AS t3c on t3b.id = t3c.id and t3c.text = "Tax"

(if you want you can also select constants "Tax", "Total" and "Subtotal" in the select part and give them some column names)

One thing that remains unclear is the relationship between id's in tables - are they primary key of table_one or table_two. That can influence the results, of course, when you will have multiple rows in table_one and table_two.