CODEWITHSUNDEEP
sqldatabasejoin
Kaka
Kaka

Reputation: 395

Join only if field is more than zero

Instead of making 2 queries could I somehow just join persons if houses.owner_id is greater than zero?

SELECT * FROM houses 
INNER JOIN persons ON persons.id = houses.owner_id 
WHERE houses.id = id

Upvotes: 2

Views: 1367

Answers (5)

Bohemian
Bohemian

Reputation: 425013

Move the condition into the join's condition:

SELECT * FROM houses 
JOIN persons ON persons.id = houses.owner_id 
    AND houses.owner_id > 0
WHERE houses.id = id

This will only join if owner_id > 0.

This query is a little odd, because you're not using the joined data.

Upvotes: 2

Mark Hurd
Mark Hurd

Reputation: 10931

The other answers are probably what you want, but, for completeness, you might want to adjust the JOIN clause:

SELECT * FROM houses 
INNER JOIN persons ON persons.id = houses.owner_id AND houses.owner_id > 0
WHERE houses.id = id

Upvotes: 0

vamosrafa
vamosrafa

Reputation: 695

You have to use count of house.owner_id > 0. Having and group by clause is needed. Also, this is not a good SQL design.

Upvotes: 0

tornados
tornados

Reputation: 86

Why not pass it in as a where condition?

SELECT * FROM houses 
INNER JOIN persons ON persons.id = houses.owner_id 
WHERE houses.id = id AND houses.owner_id > 0

Upvotes: 0

Darren
Darren

Reputation: 70728

Try:

SELECT * FROM houses 
INNER JOIN persons ON persons.id = houses.owner_id 
WHERE houses.id = id AND houses.owner_id > 0

Not sure why an owner_id would be smaller than 0? Maybe a bad database design. Also SELECT * is considered bad practice, try to explicitly state your columns.

Upvotes: 1

Related Questions