CODEWITHSUNDEEP
sqlpostgresql
taivo
taivo

Reputation: 139

sql check if join table has rows by mutliple criterias

I have two tables, assets and asset_params.

assets

|asset_id|  some_asset_data |
-----------------------------  
|   1    | 'some data'      |
|   2    | 'some other data'|

asset_params

|asset_param_id|asset_id|param_name|param_value|  
------------------------------------------------
|       1      |    1   | 'Memory' | '4096'    |    
|       2      |    1   | 'OS'     | 'Windows' |  
|       3      |    2   | 'Memory' | '4096'    |
|       4      |    2   | 'OS'     | 'Linux'   |

Now, how can i find assets which have parameters where they have a param called 'Memory' with value '4096' AND a param 'OS' with value 'Windows'.

Desired result is that i find one asset row, with id 1, in the example case.

I cant find a reasonable solution. Any help is appreciated.

Upvotes: 0

Views: 42

Answers (3)

flybyanswer
flybyanswer

Reputation: 11

Something like this should work:

select * from assets where
    assets_id in (select assets_id from asset_params
        where name = "OS" and value = "Win")
and
    assets_id in (select assets_id from asset_params
        where name = "memory" and value = "4096")

If your tables are huge, this needs proper indexes. But you should always have good indexes anyway. ;)

Upvotes: 1

Cédric L. Charlier
Cédric L. Charlier

Reputation: 797

select * from assets a where
    exists (select 1 from asset_params p
        where name = "OS" and value = "Win" and a.asset_id=p.asset_id)
    and exists (select 1 from asset_params p
        where name = "memory" and value = "4096" and a.asset_id=p.asset_id)

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this with aggregation and a having clause:

select asset_id
from asset_params
where param_name = 'Memory' and param_value = '3096' or
      param_name = 'OS' and param_value = 'Windows'
group by asset_id
having count(*) = 2;

Note: if you can have multiple parameters with the same name, you should use count(distinct param_name) = 2.

This easily generalized. A more Postgres'ish way of writing it is:

select asset_id
from asset_params
where (param_name, param_value) in (('Memory', '3096'),  ('OS', 'Windows'))
group by asset_id
having count(*) = 2;

Upvotes: 1

Related Questions