SQL: Looking up the same field in one table for multiple values in another table?

Question

(Not sure if the name of this question really makes sense, but what I want to do is pretty straightforward)

Given tables that looks something like this:

Table Foo
---------------------------------
| bar1_id | bar2_id | other_val |
---------------------------------

Table Bar
--------------------
| bar_id | bar_desc|
--------------------

How would I create a select that would return a table that would look like the following:

---------------------------------------------------------
| bar1_id | bar1_desc | bar2_id | bar2_desc | other_val |
---------------------------------------------------------

i.e. I want to grab every row from Foo and add in a column containing the description of that bar_id from Bar. So there might be some rows from Bar that don't end up in the result set, but every row of Foo should be in it.

Also, this is postgres, if that makes a difference.

Answer 1

SELECT
  foo.bar1_id, bar1.bar_desc AS bar1_desc,
  foo.bar2_id, bar2.bar_desc AS bar2_desc,
  foo.other_val
FROM
  foo
  INNER JOIN bar bar1 ON bar1.id = foo.bar1_id
  INNER JOIN bar bar2 ON bar2.id = foo.bar2_id

This assumes you'll always have both a bar1_id and a bar2_id in foo. If these can be null then change INNER JOIN to LEFT OUTER JOIN.

Answer 2

select f.bar1, b1.desc, f.bar2, b2.desc, f.value 
from foo as f, bar as b1, bar as b2 
where f.bar1 = b1.id 
  and f.bar2 = b2.id

Answer 3

SELECT F.bar_id1, 
    (SELECT bar_desc FROM Bar B WHERE (F.bar_id1 = B.bar_id)),
    F.bar_id2, 
    (SELECT bar_desc FROM Bar B WHERE (F.bar_id2 = B.bar_id)),
    F.other_val
FROM FOO F;

Answer 4

This doesn't directly answer your question (but that's ok, the people above already have), but...

This is considered very bad design. What happens in the future when your foo can be associated with 3 bars? Or more? (Don't say it will never happen. I've lost count of the number of "that'll never happen" things I've implemented over the years).

The generally correct way to do this is to do a one-to-many relationship (either with each bar pointing back to a foo, or an intermediate foo-to-bar table, see many-to-many relationships). Now you correctly format output on the front end, and just fetch a list of bars per foo to pass up to it (easy to do in SQL). Reports are a special case, but it's still relatively easily accomplished with pivoting or CrossTab queries.