SQL select data from multiple tables

Question

I'm trying to select from multiples tables(10+) that are in the following format using mySQL.

+----------table(n)-----------+
+-----------------------------+
| url       | id        | ... |
+-----------+-----------+-----+
| url1.com  | 12345678  | ... |
| url2.com  | 45832458  | ... |

What I need to do is retrieve the id from each table for a given URL and return it in a single row like so.

+--------------table(n)--------------+
+------------------------------------+
| url       | table(n)  | table(n+1) |
+-----------+-----------+------------+
| url1.com  | 12345678  | 8182735    |

But the URL might not exist for a given table, so I just need to retrieve all the ids from the tables where the URL is found.

For example, I have 10 tables and a URL/id is in 4 of them. I need to retrieve these 4, in a single row. I've tried to use aliases for columns along with various JOIN combinations to no avail.

Any help would be appreciated.

You're going to have a tough time of it with a variable number of columns in the output. Can yo do it so you know how many columns you want ahead of time? — mikeb, Aug 27 '15 at 12:12
Right, **the number of columns in the result**, is that fixed or not? — mikeb, Aug 27 '15 at 15:36
the number of columns in the result is variable depending on how many results for a url are found — Craig Minchin, Aug 27 '15 at 20:33
See my answer. This is tougher than it seems like it should be with just SQL. — mikeb, Aug 28 '15 at 11:09

score 0 · Answer 1 · answered Aug 27 '15 at 12:11

0

You can do this with a union all and group by:

select url,
       max(id1) as id1, max(id2) as id2, . . .
from ((select url, id as id1, NULL as id2, . . .  from table1) union all
      (select url, NULL as id1, id as id2, . . .  from table1) union all
      . . .
     ) t
group by url;

answered Aug 27 '15 at 12:11

Gordon Linoff

1,242,037
58
646
786

Hey, thanks for answering. I need to be able to specify a WHERE clause for a given URL(WHERE url='somepage.com'), how can I do that? do I need a WHERE clause for every table? – Craig Minchin Aug 27 '15 at 12:23

score 0 · Answer 2 · answered Aug 27 '15 at 12:19

0

try like this

SELECT tu.URL,COALESCE(table_1.id,0) AS table_1,COALESCE(table_2.id,0) As   
table_2,......,COALESCE(table_n.id,0) AS table_n  FROM Table_URL tu 
LEFT JOIN  table_1 ON tu.URL = table_1.URL
LEFT JOIN  table_2 ON tu.URL = table_2.URL
.
.
LEFT JOIN table_n ON tu.URL = table_n.URL

answered Aug 27 '15 at 12:19

Raj Kamuni

388
2
12

What about a variable number of columns? – mikeb Aug 28 '15 at 11:10
@mikeb in that case your ans in this post is very help full. – Raj Kamuni Aug 28 '15 at 11:21

score 0 · Answer 3 · edited May 23 '17 at 12:09

You want a variable number of columns generated by your SQL statement, and you cannot do that with MySQL.

You can see some techniques to get around it here: MySQL pivot row into dynamic number of columns

Many reporting tools will let you do things like you want to, but using some sort of prepared statement hack with MySQL would be the only way to do it:

http://buysql.com/mysql/14-how-to-automate-pivot-tables.html

SQL select data from multiple tables

3 Answers3

Linked