MySQL Need help building a query: combining multiple tables on one row

Question

Forgive me if this question was asked and answered, I searched and found several similar to it, but I start too much with SQL to adapt them to my needs. Also forgive me if I do not use the correct terminology, I know that it can be annoying when someone asks a question, and they don’t even know enough to be able to ask what they need.

<2nd EDIT Mar22:>

Well, I didn’t understand what the source data looked like, and that’s why I couldn’t get what I wanted. Thanks to @goran for making me get the original source tables for publication here (which I still am not going to publish, because I am too lazy to edit them, which is necessary for the convenience of viewing and protecting the innocent, but I would have to at least print them before request). Below are recently redesigned example tables, but I still have not figured out how to achieve the ultimate goal - all on one line.

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 

I am looking for 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 am no longer sure how reliable the information below is, but I am leaving it here for now, hoping that this will not confuse the new readers of the question too much.

</ EDIT Mar 22>

I help a friend collect some data and you need to execute a query that results in one row per record, but instead I get a few rows. Here is an example of what I'm requesting 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 

Result:

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

I need to create a query that will lead to something more similar:

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

Any help / guidance would be greatly appreciated.

Thanks!

- Jed

Answer 1

I think you simplified it a bit. The query you quote will return exactly what you want. Here's an example (choosing from the same table twice gives a similar situation like yours)

 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 labeling columns in a result set with the same labels. I assume that your original request had a choice of t1. * In the highlighted part.

If you want to refer to individual fields whose names are ambiguous, you will 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 must specify exactly what you want (column aliases are optional, you can also do t1., T2)

 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 changing the sampled data, it seems that you want to turn several rows from one table into one. Here is a specific solution (provided that you will always have tax lines, totals and totals lines, and that you are only interested in these lines).

 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 the constants "Tax", "Total" and "Total" in the selected part and specify column names for them)

One thing that remains unclear is the relationship between id in tables - this is the primary key of table_one or table_two. This can affect the results, of course, when you have multiple rows in table_one and table_two.

Answer 2

You won’t be able to get the result exactly as you want, at least because you have columns with the same name. You can probably get this result by renaming the columns, but this approach will lead to hacker code and the inability to correctly use the results.

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

Answer 3

The result you are querying for is not a relation , since its attributes do not have unique names. Therefore, it is unreasonable to expect that a relational database system will produce such a result. Which does not mean that some database systems may violate the relational model and in some cases produce non-relational results; but this is unlikely to be one of them.

So, instead, you should use the application to accept this relation as input and convert it to the desired result . From an RDBMS point of view, a significant part of the application role.