CODEWITHSUNDEEP
mysqlsql
hgerdin
hgerdin

Reputation: 637

SQL - Case in where clause

I got the following sql question that I that won´t work for me. I know that the last CASE row are wrong but I would like to use a CASE statement like that in my where clause.

Short description of my situation: I got several companies that got there own material linked to them with "companyID". Each material might be linked to a row in pricelist_entries. If I search for one row in the pricelist_entries table that is linked to many material rows all rows will be returned but I just want to return the one that belongs to the current company (the company that performs the search). Conclusion: If materialID NOT NULL THEN materials.company="current.companyID".

SELECT peID, peName, materialID
FROM pricelist_entries
INNER JOIN pricelist ON pricelist_entries.peParentID=pricelist.pID
LEFT JOIN materials ON pricelist_entries.peID=materials.pricelist_entries_id
WHERE peBrand = 'Kama' AND pricelist.pCurrent = 1 
  AND (peName LIKE '%gocamp de%' OR    peArtnr LIKE '%gocamp de%') 
  AND pricelist.country=0 AND pricelist_entries.peDeleted=0
CASE materialID WHEN IS NOT NULL THEN materials.companyID=10 END

Please tell me if I need to describe my problem in a better way. Thanks in advance!

Upvotes: 0

Views: 102

Answers (2)

Ryan
Ryan

Reputation: 3171

You can't use CASE in the where clause that I'm aware of, you need to use it in the SELECT portion, but it will have the same effect. Something like this should work:

SELECT CASE materialid WHEN IS NOT NULL THEN companyid END as thiscompanyid

This will give you a new column named thiscompanyid and you can query off of that to get what you need.

Upvotes: 0

Joachim Isaksson
Joachim Isaksson

Reputation: 180867

Sounds like just moving the condition into the join would make it simpler;

SELECT peID, peName, materialID
FROM pricelist_entries
INNER JOIN pricelist 
  ON pricelist_entries.peParentID=pricelist.pID
LEFT JOIN materials 
  ON pricelist_entries.peID=materials.pricelist_entries_id
 AND materials.companyID=10                                 -- << condition
WHERE peBrand = 'Kama' AND pricelist.pCurrent = 1 
  AND (peName LIKE '%gocamp de%' OR    peArtnr LIKE '%gocamp de%') 
  AND pricelist.country=0 AND pricelist_entries.peDeleted=0

It will only left join in material rows that are linked to the correct company.

Upvotes: 2

Related Questions