0

Ok, I am stuck on this one.

I have a PostgreSQL table customers that looks like this:

id firm1 firm2 firm3 firm4 firm5 lastname firstname
1  13    8     2     0     0     Smith    John
2  3     2     0     0     0     Doe      Jane

Each row corresponds to a client/customer. Each client/customer can be associated with one or multiple firms; the numeric value under each firm# columns corresponds to the firm id in a different table.

So I am looking for a way of returning all rows of customers that are associated with a specific firm.

For example, SELECT id, lastname, firstname where 8 exists in firm1, firm2, firm3, firm4, firm5 would just return the John Smith row as he is associated with firm 8 under the firm2 column.

Any ideas on how to accomplish that?

Jim Jones
  • 18,404
  • 3
  • 35
  • 44
Greg Perry
  • 151
  • 10

3 Answers3

1

You should consider to normalize your tables, with the current schema you should join firms tables as many times as the number of firm fields in your customer table.

select    *
from      customers c
left join firms f1
on        f1.firm_id = c.firm1
left join firms f2
on        f2.firm_id = c.firm2
left join firms f3
on        f3.firm_id = c.firm3
left join firms f4
on        f4.firm_id = c.firm4
McNets
  • 10,352
  • 3
  • 32
  • 61
1

You can use the IN operator for that:

SELECT *
FROM customer
where 8 IN (firm1, firm2, firm3, firm4, firm5);

But it would be much better in the long run if your normalized your data model.

1

You can "unpivot" using a combination of array and unnest, as specified in this answer: unpivot and PostgreSQL.

In your case, I think this should work:

select lastname,
    firstname,
    unnest(array[firm1, firm2, firm3, firm4, firm5]) as firm_id
from customer

Now you can select from this table (using either a with statement or an inner query) where firm_id is the value you care about

HarlandMason
  • 779
  • 5
  • 17