0

My table has 4 types of "r1": 'Standard', 'HD', 'HD + DVR', 'None'
There are 3 types of "status": 'NTC', 'Repair', 'In Progress'

All I am trying to do is retrieve all of the rows where r1 = 'Standard' and matches all three of the "status":

SELECT * FROM installs 
WHERE r1 = 'Standard' 
 AND STATUS = 'NTC' 
 OR STATUS = 'In Progress' 
ORDER BY building, appointment_date ASC

My results continue to pull all the other types of "r1" (HD, HD + DVR, None) and I cannot figure out for the life of me why. Any input would be greatly appreciated.

4
Contributors
6
Replies
7
Views
6 Years
Discussion Span
Last Post by smantscheff
1

I think what you are trying for is where r1 = standard and the status is either NTC or In Progress. If so try this:

SELECT * FROM installs 
WHERE r1 = 'Standard' 
AND (STATUS = 'NTC' 
OR STATUS = 'In Progress') 
ORDER BY building, appointment_date ASC
1

Your solution lists only two of the the three possible status values. If you really don't care about the status you can just eliminate it from your query:

SELECT * FROM installs 
WHERE r1 = 'Standard' 
ORDER BY building, appointment_date ASC
Votes + Comments
agree
0

Thank you for the replies. I'll give this a shot on Monday at work. I was unaware that you could use () within a WHERE clause:

SELECT * FROM installs WHERE r1 = 'Standard' AND (status = 'In Progress' OR status = 'Repair')

There are 6 other "status", but for a help thread I wanted to keep the query as basic as possible without having to do a lot of explanation.

0

As suggested by smantscheff, you need not bother about STATUS column at all if you do not want to filter by that column and display all the records. So it really does not matter how many distinct values you have in that field.

0

Thank you for all the help.

@rch1231 your solution fixed my issue. Thanks so much! Learn something new everyday

SELECT * FROM installs WHERE (r1 = 'DVR' OR r2 = 'DVR' OR r3 = 'DVR' OR r4 = 'DVR') AND (STATUS = 'In Progress' OR STATUS = 'NTC') AND (STATUS != 'Activated' OR STATUS != 'Done')
0

Your where clause is redundant. Since Status has to be one of "In Progress" or "NTC" it cannot be neither "Activated" nor "Done".

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.