PostgreSQL Return Row if Value Exists in One of Several Columns

Question

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?

You should consider to normalize your tables.

McNets
– McNets

2018-03-27 13:06:23 +00:00
Commented Mar 27, 2018 at 13:06 — McNets
– McNets, Commented Mar 27, 2018 at 13:06

user330315 · Accepted Answer · 2018-03-27 13:15:52Z

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.

answered Mar 27, 2018 at 13:15

user330315

Sign up to request clarification or add additional context in comments.

Comments

McNets · Accepted Answer · 2018-03-27 13:09:02Z

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

answered Mar 27, 2018 at 13:09

McNets

10.9k3 gold badges39 silver badges70 bronze badges

Comments

HarlandMason · Accepted Answer · 2018-03-27 13:17:46Z

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

answered Mar 27, 2018 at 13:17

HarlandMason

7895 silver badges17 bronze badges

Collectives™ on Stack Overflow

PostgreSQL Return Row if Value Exists in One of Several Columns

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related