CODEWITHSUNDEEP
sqlpostgresql
Paul Wicks
Paul Wicks

Reputation: 65570

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

(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.

Upvotes: 3

Views: 1774

Answers (5)

Boodoo
Boodoo

Reputation: 35

I would try with information_schema.colums table. 

    SELECT concat(table_name,'_',column_name) 
FROM information_schema.columns WHERE table_name = bar1 OR table_name = bar2
into new_table

Then you can populate it.

with foo as select * from bar1

or select into

Upvotes: 0

Donnie
Donnie

Reputation: 46933

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.

Upvotes: 2

Phil Ross
Phil Ross

Reputation: 26100

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.

Upvotes: 1

z-index
z-index

Reputation: 389

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;

Upvotes: 2

ceth
ceth

Reputation: 45295

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

Upvotes: 0

Related Questions