CODEWITHSUNDEEP
sqlpostgresql
J86
J86

Reputation: 15237

get related column in the same table

I have a works table with the following definition:

   Column    |         Type          | Collation | Nullable | Default
-------------+-----------------------+-----------+----------+---------
 id          | integer               |           | not null |
 location_id | uuid                  |           | not null |
 f_index     | integer               |           | not null |
 f_name      | character varying(40) |           | not null |
 f_value     | character varying(40) |           | not null |

It has data such as:

 id |             location_id              | f_index |   f_name    |  f_value
----+--------------------------------------+---------+-------------+------------
  1 | 40e6215d-b5c6-4896-987c-f30f3678f608 |       1 | plot_crop   | pears
  1 | 3f333df6-90a4-4fda-8dd3-9485d27cee36 |       1 | plot_crop   | pears
  1 | c17bed94-3a9c-4c21-be49-dc77f96d49dc |       1 | plot_crop   | pears
  1 | 1cdc7c05-0acd-46cb-b48a-4d3e240a4548 |       1 | plot_crop   | pears
  1 | dae1eee7-508f-4a76-8906-8ff7b8bfab26 |       1 | plot_crop   | pears
  1 | 6ecd8c99-4036-403d-bf84-cf8400f67836 |       1 | plot_id     | 137
  1 | 6ecd8c99-4036-403d-bf84-cf8400f67836 |       2 | farmer_name | John Smith

Query

I want to be able to query the table on f_value and always get the farmer name if present.

Desired Output

So if I write a query with the condition: where f_value = 'pears';, I'll get:

 id |             location_id              | f_index |  f_name   | f_value
----+--------------------------------------+---------+-----------+---------
  1 | 40e6215d-b5c6-4896-987c-f30f3678f608 |       1 | plot_crop | pears
  1 | 3f333df6-90a4-4fda-8dd3-9485d27cee36 |       1 | plot_crop | pears
  1 | c17bed94-3a9c-4c21-be49-dc77f96d49dc |       1 | plot_crop | pears
  1 | 1cdc7c05-0acd-46cb-b48a-4d3e240a4548 |       1 | plot_crop | pears
  1 | dae1eee7-508f-4a76-8906-8ff7b8bfab26 |       1 | plot_crop | pears

however, if the condition is: where f_value = '137'; then the output ought to be:

 id |             location_id              | f_index | f_name      | f_value
----+--------------------------------------+---------+-------------+---------
  6 | 6ecd8c99-4036-403d-bf84-cf8400f67836 |       1 | plot_id     | 137
  7 | 6ecd8c99-4036-403d-bf84-cf8400f67836 |       1 | farmer_name | John Smith

I have also created a db-fiddle.

Upvotes: 0

Views: 37

Answers (2)

Belayer
Belayer

Reputation: 14861

Your data begs the question(s):

  1. Are you looking for the farmer_name because f_value is 137?
  2. Are you looking for the farmer_name because the associated f_name is 'plot_id'?

Either way you can parameterize your query with an simple SQL function. Assuming he 1st above. (See fiddle)

create or replace 
function search_works_f_value( f_value_in character varying) 
 returns setof works
 language sql 
as $$ 
    select w1.* 
      from works w1
     where w1.f_value = f_value_in
        or exists (select null 
                     from works w2
                    where w1.location_id = w2.location_id
                      and w1.f_name = 'farmer_name' 
                      and w2.f_value = f_value_in
                  ) 
     order by f_index;
$$;

Of course the answer by @Serg could just as easily be parameterized the same way.

Upvotes: 0

Serg
Serg

Reputation: 22811

Finding relevant farmer

select * 
from works 
where f_value = '137'
union
select * 
from works w1
where  f_name = 'farmer_name'
  and location_id in (
      select location_id 
      from works w2 
      where w2.f_value = '137');

Upvotes: 1

Related Questions