CODEWITHSUNDEEP
sqlpostgresql
Ahdee
Ahdee

Reputation: 4949

How do I join 3 tables but only return rows that match?

Hi I'm using postgres,

I have three tables. 

table 1

id1 | name 
foo ,  jack 

table 2 

id3 | id1 
orange,  foo 
apple, foo

table 3 

id3|description 
orange, vit.c
apple, vit. a & c

say id1 = foo; what I want is find all the id3 that corresponds to this in table 2 ( in this case its orange and apple) and then return table 3 with all rows that matches with all the id3 in table 2.

whooo thats a mouthful, sorry bout that. I tried inner join, left join, but keeps returning me many more tables that don't match. I can do them serially, but I'm wondering if there is way to do it all in step?
If all works out, when I search for foo it should return vit.c and vit. a&c

thanks! Ahdee

Upvotes: 0

Views: 318

Answers (2)

Jim
Jim

Reputation: 10278

You just need a standard WHERE clause:

SELECT table_3.description 
FROM table_1, table_2, table_3
WHERE table_1.id1 = table_2.id1 AND table_2.id3 = table_3.id3

See the documentation here:

http://www.postgresql.org/docs/8.3/static/tutorial-join.html

Upvotes: 1

danjuggler
danjuggler

Reputation: 1320

A simple join is what you need for the data example you provided:

select description
  from table1 t1
  join table2 t2 on t2.id1= t1.id1
  join table3 t3 on t3.id3 = t2.id3
where t1.id1 = 'foo'

Upvotes: 1

Related Questions