Clever Geek Handbook

SELECT query with if statement?

I have a table with the following information about an example in a PHP script:

Custid Name Type Date
1 Roy A 20150715
1 Roy -C 20150715
2 Bart B 20150715

In my request, SELECT

I only want to get clients from type = A and type = B

, but if type = -C

, then I want to remove A

or B

from my request.

+3


source to share


2 answers


If you need to select all records for customers that do not have a "-C" type on any row, you first need to find the customers you want to exclude. This can be done with the following query:

SELECT Custid FROM yourtable WHERE `type` = '-C';

You should now be able to find all other clients:



SELECT * FROM yourtable 
 WHERE custid NOT IN (
  SELECT Custid FROM yourtable WHERE `type` = '-C'
 );

The above is just one way to achieve this. You can also use a temporary table or JOIN

for a specified subquery.

+2


source


Try using self-join.



SELECT c_details.custid, c_details.name, c_details.type, c_details.date 
FROM customers c_details
INNER JOIN customers c_type ON c_details.custid = c_type.custid
AND c_type.type <> '-C'
+1


source






More articles:


All Articles