select help

rharter

I need some help with sql. I have a table that has among other things these attributes and values:

+----------------------------------------------------+--------------+-------+

| sub_id | attribute_id | value |

+----------------------------------------------------+--------------+-------+

| 56b073df8cdc6fb857120ccf328a9fd1 | 84 | 4 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 85 | 1 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 86 | 0 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 87 | 1 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 89 | 1 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 90 | 1 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 92 | 1 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 93 | 1 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 94 | 0 |

| 56b073df8cdc6fb857120ccf328a9fd1 | 95 | 1 |

+----------------------------------+--------------+-------+

it also has a site_id that for the data set above would be the same for each row. The table has no keys and the sub_id, site_id and attribute_id are foreign keys. I am trying to select sub_id’s where each <attribute_id,value> is a specific value. I’m generating this query. Here is an example of a generated query:

select distinct sub_id from subscriber_preferences where site_id='8583af89e81b72acef29715070972a5f' and ((attribute_id='95' and (value='1' or value='2')) and (attribute_id='94' and (value='0' or value='1')) and (attribute_id='84' and (value='4')) and (attribute_id='89' and (value='0' or value='1' or value='2')) and (attribute_id='90' and (value='1')) and (attribute_id='93' and (value='1')) and (attribute_id='85' and (value='1')) and (attribute_id='86' and (value='0')) and (attribute_id='87' and (value='1')))

I get an empty set returned. For the query above, you can see that each attribute_id exists in the dataset and that each <attribute_id,value> condition is met. I would expect that I would get 10 sub_id’s returned and the distinct keyword would reduce that to 1. I’m obviously not getting something - I know, for example, this sql would work:

Select sub_id,attribute_id,value from subscriber_preferences where site_id=’value’ and attribute_id in (‘84’,’85’,’86’,…);

And then could loop through the result set and knock out any entries that don’t have the desired <attribute_id,value> values, and then reduce that array to have only unique sub_ids, but it seems the database should be able to do this and it would be more efficient (I think).

I'm not by any means a SQL guru and would appreciate anyone who can look at this and suggest an effective means to query this table to get the results I'm trying to get.

Thanks,
Ron

zhabala

Firstly the SQL shoud look like that:

select distinct sub_id from subscriber_preferences where site_id='8583af89e81b72acef29715070972a5f' and ((attribute_id='95' and (value='1' or value='2')) or (attribute_id='94' and (value='0' or value='1')) or (attribute_id='84' and (value='4')) or
(attribute_id='89' and (value='0' or value='1' or value='2')) or (attribute_id='90' and (value='1')) or
(attribute_id='93' and (value='1')) or
(attribute_id='85' and (value='1')) or
(attribute_id='86' and (value='0')) or
(attribute_id='87' and (value='1')))

please see the OR statements between each attribute_id condition.

But it seems to me, that you are doing something tricky ... probably exists better way how to do that, but I don't know what are you really trying to do.

Zdenek

rharter

while your sql will yield a result set, it is not the intended result. I really want only those sub_ids returned where the attribute_id's meet all the value= conditions. or returns a result for each attribute= clause and what I really want is a result returned if all attribute= clauses are met. This may be a bit confusing but I appreciate you thinking about it,
ron

ultraslacker

 select distinct sub_id 
from subscriber_preferences 
where site_id='8583af89e81b72acef29715070972a5f'
and ((attribute_id='95' and (value='1' or  value='2')) 
and (attribute_id='94' and (value='0' or value='1')) 
and (attribute_id='84' and (value='4'))
and (attribute_id='89' and (value='0' or value='1' or value='2')) 
and (attribute_id='90' and (value='1')) 
and (attribute_id='93' and (value='1')) 
and (attribute_id='85' and (value='1'))
and (attribute_id='86' and (value='0')) 
and (attribute_id='87' and (value='1')))

No row will ever meet those conditions. You are requiring that attribute_id be equal to multiple values.

rharter

do you have any suggestions and how i can create a query to accomplish the goal? Appreciate your looking at this. Is there a way to create a join such that I could query a row that would match this query, using aliases for each column perhaps ....
thanks,
ron