0

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

Ahdee
  • 4,679
  • 4
  • 34
  • 58
  • You say you tried an `inner join`? If so, show how you tried it and we can let you know what's wrong. If I'm understanding correctly, this is a pretty straight-forward `join` against 3 tables. Here's a good post about joins -- http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/ – sgeddes Oct 29 '15 at 23:51

2 Answers2

1

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'
danjuggler
  • 1,261
  • 2
  • 21
  • 38
  • dude you are fast; I found a solution a few minutes after I post this; for another way what I was missing was adding AND t1.id1='foo' right after first match, thanks. – Ahdee Oct 29 '15 at 23:57
  • Ah, yep that would return far more rows than you'd want! – danjuggler Oct 30 '15 at 00:05
1

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

Jim
  • 10,172
  • 1
  • 27
  • 36
  • 1
    I'd not recommend joining tables this way... Using a standard `join` is much more appropriate. – sgeddes Oct 29 '15 at 23:56
  • do you have a reason? this is exactly what the OP is asking, it's well documented and there is no obvious performance difference – Jim Oct 29 '15 at 23:59
  • in fact, the docs actually point out that this is more common than the alternative you suggest – Jim Oct 30 '15 at 00:00
  • 1
    Take a look at this post, it does a pretty good job explaining: http://stackoverflow.com/q/1018822/1073631 – sgeddes Oct 30 '15 at 00:03
  • Good post. However, as that post points out, the syntax is synonymous. The OP should first understand a "basic" join in a relational context. Hence my recommendation - walk before you run. – Jim Oct 30 '15 at 00:15
  • thanks Jim and sgeddes; much appreciate your help; yeah the joining stuff its still a bit obscure to me whereas the "WHERE" statement for some reason is just more straight forward but I actually found another solution with join; my mistake was not adding and AND clause right after he ON statement. – Ahdee Oct 30 '15 at 18:04