Using OR in SQL

[unknown101]

Hi Guys,

 

Say for example I have a item in my sql database which could have up to 5 possible attributes.  So below my item is car:

 

Car ||  A1 || A2  || A3 || A4 || A5

 

These 5 attributes may or may not be set, but say if I want to query to check if a particular word was one of these attributes what would be the best way to do this? (Assuming no two attributes are the same).

 

Say I wanted to search the above to see if the word "fast" was an attribute (in the actual system this is user input). Would it be something like...

 

$User_input = fast;

SELECT FROM cars_tbl WHERE A1='$user_put' OR A2='$user_input' ......and keep using OR's ?

 

Any suggestions welcome

 

Thanks in advance

 

[mezise]

Hi,

yes, this is correct query.

If your list of attributes is constant e.g:

  fast, economical, safe, capacious, 4x4

then consider using SET data type.

`attributes` SET('fast', 'economical', 'safe', 'capacious', '4x4') DEFAULT NULL

 

[fenway]

SET is quite evil... not very flexible, not easy to query / edit, etc.

[mezise]

SET is quite evil... not very flexible, not easy to query / edit, etc.

I do not agree. As TINYINT is for small numbers, SET is for small lists.

SET is very efficient during execution and if you code appropriate API, querying and editing works like a charm.

[fenway]

Small, uneditable, permanent lists, like the kind that don't exist in the real world.  It's a cheat to bypass proper normalization for a minute performance optimization.  Not worth it, IMHO -- and just because an API can hide these details, doesn't make it "easy", it makes it "transparent".  Having to change the DB schema to add another option is ridiculous.

[mezise]

I have some experience with big load applications where performance is a crucial matter and I assure you that doing ideal normalization at any price very often is shooting yourself in the foot. Practical solutions and professor's theories do not always are the same things. Don't you agree with this?

[fenway]

I have some experience with big load applications where performance is a crucial matter and I assure you that doing ideal normalization at any price very often is shooting yourself in the foot. Practical solutions and professor's theories do not always are the same things. Don't you agree with this?

I do agree... this isn't normalization at any price, it's normalization FIRST, and then de-normalization MUCH LATER.  I doubt that the OP has such an application.