SQL Select Statement against attributes indexed UNIQUE

2
votes

Have a table with three columns: col1 being primary INT, col2 & col 3 being UNIQUE and INT.

I need to select those rows in which col1, col2 and col3 are equal to const1, and col2 and

col3 are not equal to zero.

Is the following query correct?

NOTE: Have used meaningful names for attributes, and the values given here for

illistrative purposes only.

SELECT * FROM table WHERE col1 = const OR col2 = const OR col3 = const AND (coll2 <> 0 AND col3 <> 0);

const can be any INT values

2
sqlselect
You say "not equal" in the text but your query appears to be comparing with =? - Martin Smith
@OlegDok: Definitely not. Even if it was, I have atleast tried only to find the logic incorrect, rather than just copy the question from my notes. - user980411
@MartinSmith: Oops. What I basically need to do is to select those rows that match a particular const values, and such that the const value, and / or attribute values is / are not zero - user980411

2 Answers

5
votes

No, query is not correct. Here is the correct one:

SELECT * 
FROM table 
WHERE 
    const1 IN (col1, col2, col3) 
AND 0 NOT IN (col2, col3)

Update:

Your query finds all the rows where col2 <> 0 and col3 <> 0 and appends all the rows where one or more columns is equal to const

1
votes

I'm confused but based on this:

What I basically need to do is to select those rows that match a particular const values, and such that the const value, and / or attribute values is / are not zero

I think what you want is:

SELECT * 
FROM table 
WHERE 
(col1 = const1 or col2 = const1 or col3 = const1) and (col1 != 0 and col2 != 0 and col3 are !=0)

I understood that at least one of the columns need to match the const but it cant be zero