CODEWITHSUNDEEP
mysql
vault-boy
vault-boy

Reputation: 523

sql query that involves OR won't return results if one of the tables empty

I have a problem figuring out a small problem with this query to mysql db

SELECT s.*
FROM section AS s, sectioncompany AS scom
WHERE ((s.restricted = 0 ) OR (s.restricted = 1 AND s.id = scom.sectionId AND scom.companyId = $companyId))

This works fine and returns results when there are entries in sectioncompany table, but when that table is empty, query won't result anything. At the moment I did a workaround by simply adding an empty record to sectioncompany table but I want to understand what have I overlooked. As you can see, the condition that involves existing values in that table wouldn't be met once table is empty, but it should still return something for s.restricted = 0, shouldn't it? What am I missing?

Thanks

Upvotes: 0

Views: 110

Answers (3)

DaveRandom
DaveRandom

Reputation: 88647

Use an explicit JOIN instead of Cartesian joins, so you can LEFT JOIN and the problem should disappear:

SELECT `s`.*
FROM `section` `s`,
LEFT JOIN `sectioncompany` `c` ON `s`.`id` = `c`.`sectionId`
WHERE `s`.`restricted` = 0
  OR (`s`.`restricted` = 1 AND `c`.`companyId` = $companyId)

Upvotes: 1

Mikey
Mikey

Reputation: 2951

The problem here is that you're using a INNER JOIN (by using the WHERE clause to match on (s.id = scom.sectionId). What you want is a LEFT JOIN:

SELECT s.*
FROM section s
LEFT JOIN sectioncompany scom ON (s.id = scom.sectionId)
WHERE ((s.restricted = 0 ) OR (s.restricted = 1 and scom.companyId = $companyId))

Upvotes: 2

Ian Newson
Ian Newson

Reputation: 7949

This is because you're using a cross join, which returns one row for each combination of rows from the source tables. If one of tables has no rows then there's no possible combinations.

Let us know what you're trying to achieve, with schema and data if possible, and someone may be able to suggest the correct SQL.

Upvotes: 1

Related Questions