WHERE clause only IF NOT NULL

Question

I need help with my SELECT .

I got a field, which can be NULL , and the foreign key is stored in it.

 SELECT * FROM beerImage WHERE beerBRewID = brewID AND beerBrandID = beerID <--- this can be NULL 

So, if it is NULL nothing happens.

How to check if beerID NOT NULL , so I can use "beerBrandID = beerID" ?

Answer 1

If you want to include entries that have no matches, you need an external connection

 SELECT beer.id AS beerID, beer.barrelID AS barrelID, beer.imageID AS imageID, beer.title AS title, beer.map AS map, beer.longitude AS longitude, beer.latitude AS latitude, brand.name AS brandName, brew.type AS brewType, image.name AS imageName, variation.name AS variationName FROM brand, brew, image, beer LEFT OUTER JOIN variation ON variation.ID = beer.VariationID WHERE beer.id = %s AND md5(beer.memberID) = %s AND beer.review = 1 AND brand.ID = beer.brandID AND brew.ID = beer.brewID AND image.ID = beer.imageID 

Answer 2

You will probably need something like this:

First example:

 SELECT * FROM beerImage WHERE beerBRewID = brewID AND (beerID IS NULL OR beerBrandID = beerID) 

Second example:

 SELECT * FROM beerImage WHERE beerBRewID = brewID AND beerID IS NOT NULL AND beerBrandID = beerID 

The first example will allow you to show entries in which there is a beer identifier id along with beer, BrandID beer is equal to pyridic (both).

The second returns exactly the beer that matches the BrandID beer (excluding NULL pills).

Answer 3

To check for null / not null, use IS NULL / IS NOT NULL

 AND beerID IS NOT NULL 

Answer 4

You can use the comparison functions "IS NULL" or "IS NOT NULL" MySQL.

Read more about this here: http://dev.mysql.com/doc/refman/5.0/en/working-with-null.html http://dev.mysql.com/doc/refman/5.0/en/comparison -operators.html # operator_is-null

Answer 5

How about using with a CASE statement in where where clause

 WHERE CASE WHEN beer.id IS NOT NULL THEN beer.brand_id = 12345 ELSE TRUE END