BIG_MAC_AND_FRIES February 2016

Need some help on an MySQL query

I am stuck on this query.

My problem is in the syntax. I can't seem to find the correct way to match records in the "LIKE" clause and to exclude the records that do not match the "LIKE" clause.

I am using a CASE statement because I need to format the output according to the LIKE conditions.

Sample of MYSQL code I am using is posted below.

I have also included an image showing how I desire the output to be formatted.

The image of the desired output is found below.

MySQL Output

SELECT DISTINCT S.SalesID, SS.Contact
CASE CONTACT
WHEN Contact IS LIKE '%Manuel%'
THEN WORKS
WHEN Contact IS NOT LIKE '%Manue%'
THEN NO
END AS Manuel?
FROM Salespeople S
LEFT JOIN Studios SS
ON (S.SalesID = SS.SalesID)
WHERE SS.Contact = 'Manuel Austin' Or
SS.Contact IS NOT NULL;

Answers


sagi February 2016

You have a lot of syntax problems in your query.

You can use this query:

SELECT S.SalesID,
CASE WHEN ss.Contact LIKE '%Manuel%' THEN 'WORKING WITH MANUEL'
ELSE 'NOT WORKING WITH MANUEL' END AS Manuel
FROM Salespeople S
LEFT JOIN Studios SS
ON (S.SalesID = SS.SalesID)
GROUP BY S.SalesID,
CASE WHEN ss.Contact LIKE '%Manuel%' THEN 'WORKING WITH MANUEL'
ELSE 'NOT WORKING WITH MANUEL' END
ORDER BY s.SalesID

You shouldn't check in the where clause anything about manuel or when the column are null, since you need all the results no matter if they have manuel as their contact.

So you just need to left join, and use case.

Post Status

Asked in February 2016
Viewed 1,923 times
Voted 9
Answered 1 times

Search




Leave an answer