CODEWITHSUNDEEP
mysqlsqlleft-join
Cripto
Cripto

Reputation: 3751

Conditional Where clause in sql

select colA, colB from car
where car.id in (select id from make) and 
car.id in (select id from model);

The above query works perfectly file as is, say there is a case that the make table has not been popluated with anything. just an empty table. is there a way to make the join not take place on that table?

Basically, if the table has 1 or more rows, apply the where condition. Otherwise, ignore and dont limit it.

Is there a way to achieve the same result on a left join?

EDIT

Result algorithm:

Thisnk of model2 a table of things I dont want, and model and make tables of things I do want.

Upvotes: 1

Views: 144

Answers (3)

Barmar
Barmar

Reputation: 780673

SELECT colA, colB
FROM car
WHERE ((SELECT COUNT(*) FROM make) = 0) OR id IN (SELECT id FROM make))
  AND id IN (SELECT id from model)

With a LEFT JOIN:

SELECT DISTINCT colA, colB
FROM car
JOIN (SELECT COUNT(*) c FROM make) mcount
LEFT JOIN make ON car.id = make.id
JOIN model ON car.id = model.id
WHERE mcount.c = 0 OR make.id IS NOT NULL

Using OR can often prevent use of indexes, so it may be better to use UNION:

SELECT distinct colA, colB
FROM car
JOIN make on car.id = make.id
JOIN model on car.id = model.id

UNION

SELECT distinct colA, colB
FROM car
JOIN (SELECT COUNT(*) c FROM make) make
JOIN model ON car.id = model.id
WHERE make.c = 0

Extending the LEFT JOIN version to both tables is straightforward:

SELECT DISTINCT colA, colB
FROM car
JOIN (SELECT COUNT(*) c FROM make) makecount
LEFT JOIN make ON car.id = make.id
JOIN (SELECT COUNT(*) c FROM model) modelcount
LEFT JOIN model ON car.id = model.id
WHERE (makecount.c = 0 OR make.id IS NOT NULL)
  AND (modelcount.c = 0 OR model.id IS NOT NULL)

If there are other tables to join with, you can just keep repeating this pattern.

Doing this with the UNION query is harder, because you would need a subquery for each combination of join tables that can be empty: one subquery for both make and model having rows, one for just make, one for just model, and one for both being empty. If there were 3 tables being joined with, this would expand to 8 subqueries (i.e. there will always be 2n subqueries). Maybe someone can come up with a way to do it better, I can't think of it off the top of my head.

Upvotes: 2

echo_Me
echo_Me

Reputation: 37233

try this

     select colA, colB from car
     left join make on make.id = car.id
     inner join model on model.id = car.is
     where make.id IS NOT NULL

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269443

You would have to do this with a more complicated join:

select c.colA, c.colB
from car c cross join
     (select count(*) as num from make) m
where num = 0 or
      (c.id in (select id from make) and 
       c.id in (select id from model)
      )

Upvotes: 1

Related Questions