Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have to pull out a range of entries of a database, all based around two pieces of data. In itself thats fine. An example of the data is:

Record   ID    Tags
1        1423  Day, Sun, Warm
2        1423  Day, Sun, Wet
3        1742  Night, Warm
4        2743  Night, Warm, Dry, Stars
5        4832  Sunrise, Bright, Clear, Cool

Now in the above data, the ID is an internal company ID, but Record is a unique DB ID. There are 2,500 rows of similar data in the database, with other data thats not related to this query. Now we need to pull the data in the following style, written here in a symbolic, rather than specific SQL so people can understand it. Ive included brackets to emphasis which parts must match:

WHERE (ID=1423 AND tags contains Warm) 
    OR WHERE (ID=2743 AND Tags contains Dry) 
    OR WHERE (ID=4832 AND Tags contains Cold)

The question is how do I get the SQL statment to work right? I've tried

SELECT DISTINCT ID 
FROM DBTABLE 
WHERE ID=2743 AND Tags LIKE '%Warm%' 
    OR ID=4832 AND Tags LIKE '%Cool%'

But it doesn't seem to return the correct records. I have a specific query which I know will return 5 records, but every time it returns more, including ones that don't match. I'm thinking the SQL is mixing up for example where ID=2743 AND (tags like warm or 2743) AND Tags like dry or 4832 etc...

Can anyone point me in the right direction. In essence it is multiple where statements, where each statement has to match an ID and have a 'LIKE' tag. but the statement can match one or more of the given WHERE statements. Does that make sense?

share|improve this question
1  
You shouldn't be storing comma separated values in a column in the first place. –  a_horse_with_no_name Nov 17 '12 at 14:01
1  
Just apply the brackets in your pseudo code to your actual code. –  Martin Smith Nov 17 '12 at 14:03
2  
Without seeing your actual query and returned results it is har to tell anything. However, AND has a higher precedence over OR, which means in a AND b OR c AND d a&b and c&d are evaluated first and get then ORd. Anyway, if you suspect that your expression isn't quite that you want, you can always use parentheses to group subexpressions. That way it can even be more readable. –  dezso Nov 17 '12 at 14:04
    
@horse_with_name: yes appreciate that, but I've got to keep with the existing structure! –  TIW Nov 17 '12 at 14:21
    
@Dezso & Martin: Many thanks, i've learnt something today! Didn't realise you could use parentheses in SQL code. Thanks Guys - i'll report back! –  TIW Nov 17 '12 at 14:21
add comment

2 Answers 2

As Martin Smith and dezso commented, you can add parentheses to your query to be explicit in your intent since they can change how the where clause is evaluated:

SELECT DISTINCT ID AS IDs
FROM DBTABLE
WHERE ((ID = 1423) AND (Tags LIKE '%Warm%'))
OR ((ID = 2743) AND (Tags LIKE '%Dry%'))
OR ((ID = 4832) AND (Tags LIKE '%Cool%'))

This returns a 3-record result set containing 1423, 2743 & 4832 from your example data.

Parentheses can also make your SQL queries more readable and maintainable.

share|improve this answer
add comment

With just 2500 records using LIKE search should be sufficient but if the number of records will grow you may encounter serious performance problems.

Proper solution would be to add table 'tags' which will keep list of unique tag's values and then add another table which will keep list of tags for given record in the base table creating many-to-many relationship.

You can also keep current structure and just add a fulltext index on the 'Tags' column with such index your query would be something like this

SELECT DISTINCT ID AS IDs
FROM DBTABLE
WHERE ((ID = 1423) AND (MATCH (Tags) AGAINST ('Warm')))
OR ((ID = 2743) AND (MATCH (Tags) AGAINST ('Dry')))
OR ((ID = 4832) AND (MATCH (Tags) AGAINST ('Cool')))
share|improve this answer
1  
FULLTEXT, by default, will not match 3-letter words ('Dry'). Another approach is to build a table that maps Tags to IDs. –  Rick James Nov 20 '12 at 1:53
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.