CODEWITHSUNDEEP
mysqljoinunion
Piokaz
Piokaz

Reputation: 374

MYSQL : Mix data from 3 tables and JOIN with another table

I've searched a long time to find a query on stackoverflow and google that look like to mine, but haven't found what i want. Tried many queries too, but i'm not enough skilled with SQL :(

Posts (id, title, url)
Imgs (id, title, url)
Zaps (id, title, url)

Comments(id, T_id, date, user, comment, from)

T_id = ids from Posts, Imgs, Zaps

from = contain text from wich table the data come from ("post", "img", "zap")

The table Comments contains all the mixed comments from the 3 tables. I'm trying to get this output :

Comments.id, Comments.user, Comments.comment, Comments.from, title, url

The listing of the last comments with the title and url of the table which it comes from.

I know my schema is somewhat fucked up, its in production and i cannot really alter it.

If someone can help me, i'll really appreciate it.

Thanks !

Upvotes: 1

Views: 204

Answers (1)

Chad
Chad

Reputation: 164

You need to match the T_id/from columns in comments to the proper unique id in the right table.

select c.id, c.user, c.comment, c.from, t.title, t.url
from Comments c, Posts t
where c.T_id=t.id and c.from='post'
union
select c.id, c.user, c.comment, c.from, t.title, t.url
from Comments c, Imgs t
where c.T_id=t.id and c.from='img'
union
select c.id, c.user, c.comment, c.from, t.title, t.url
from Comments c, Zaps t
where c.T_id=t.id and c.from='zap'

Upvotes: 1

Related Questions