CODEWITHSUNDEEP
postgresqljoin
tokhi
tokhi

Reputation: 21618

Select rows of multiple tables via joins

I have some tables which are related to each others.

A short demonstration:

Sites:

 id | clip_id | article_id | unit_id
--------------+------------+--------
  1 | 123     |  12        |  7

Clips:

id | title  | desc |
------------+--------
 1 | foo2   | abc1

Articles:

id | title  | desc | slug
------------+---------------------
 1 | foo2   | abc1 | article.html

Units:

 id | vertical_id | title |
------------------+-------+
  1 | 123         |  abc  |

Verticals:

id | name  | 
-----------+
 1 | vfoo  |

Now I want to do something like below:

SELECT ALL VERTICAL, UNIT, SITE, CLIP, ARTICLE attributes 
from VERTICAL, UNIT, SITE, CLIP, ARTICLE TABLES 
WHERE vertical_id = 2

Can some one help me how can I use joins for this?

Upvotes: 0

Views: 27

Answers (2)

wumpz
wumpz

Reputation: 9131

Here is a running example of possibly what you want: http://sqlfiddle.com/#!15/af63b/2

select * from
sites
inner join units on sites.unit_id=units.id
inner join clips on clips.id=sites.clip_id
inner join articles on articles.id=sites.article_id
inner join verticals on verticals.id=units.vertical_id
where units.vertical_id=123

The problem is, that the description you gave us did not clearly specify which columns to join:

  1. (answered) Why does units have a link to site via site_id and sites a link back to units via unit_id?
  2. (answered) Why does units have a link to verticals via vertical_id and verticals a link back to units via unit_id?

I am guessing that your data does not giva a consistent example to get rows using the join. For vertical_id=123 there is no corresponding entry in verticals.

Edit:

I corrected the SQL due to corrections within the question. With this the two questions are answered.

Upvotes: 1

Benny
Benny

Reputation: 432

select s.id, s.clip_id, s.article_id, u.title, u.vertical_id, c.title, v.unit_id, c.desc, a.slug 
from sites s
join units u on s.id = u.id
join clips c on u.id = c.id
join verticals v on c.id = v.id
join articles a on v.id = a.id
where v.vertical_id = 'any id'

Upvotes: 0

Related Questions