CODEWITHSUNDEEP
sqlms-access
tmo
tmo

Reputation: 149

select with multiple conditions,


Hi guys

I am quite new for the SQL, this site as well.

I have data as below, i want to select all records except failure"BTM dead" for Train "1101", which means only the records 1, 3 will not be selected.

*record*     | *Train*    |  *Failure*     |

   1         |  1101      |   BTM dead     |
   2         |  1101      |  relay failure |
   3         |  1101      |   BTM dead     |
   4         |  2101      |  relay failure |
   5         |  2101      |   BTM dead     |
   6         |  2101      |  relay failure |

Here is what I tried..

SELECT failure_table.record, failure_table.Train, failure_table.Failure
FROM failure_table
WHERE failure_table.Train <> 1101 And failure_table.Failure <> "BTM dead";

but turns out that only records 4,6 selected.
can I have a suggestion on that please? what statement would it be?

Thank you!

Upvotes: 1

Views: 105

Answers (2)

mszymborski
mszymborski

Reputation: 1654

You used AND instead of OR:

 SELECT failure_table.record, failure_table.Train, failure_table.Failure
 FROM failure_table
 WHERE failure_table.Train <> 1101 OR failure_table.Failure <> 'BTM dead';

Upvotes: 4

Matt
Matt

Reputation: 14341

SELECT failure_table.record, failure_table.Train, failure_table.Failure
FROM failure_table
WHERE NOT (failure_table.Train = 1101 And failure_table.Failure = 'BTM dead')

Sometimes the easiest way is to use NOT.

So because you know you want everything except when train = 1101 and failure = 'BTM' simply state that and then tell sql you want the opposite by saying NOT. Also note you need single quotes when identifying a string not double quotes or it will think it is a column.

Upvotes: 5

Related Questions