CODEWITHSUNDEEP
sqlite
Display Name
Display Name

Reputation: 1035

SQLite conditional where clause

I have three tables

ZoneTable
ID, Name

UserTable
ID, Name

UserZone
ZoneTableID, UserTableID

Now. 

SELECT ID, Name from ZoneTable

gives me the full list of zones.

If i do the following 

SELECT ZoneTable.ID, ZoneTable.Name from ZoneTable LEFT JOIN UserZone on UserZone.ZoneTableID = ZoneTable.ID WHERE UserTableID = :ID GROUP BY ZoneTable.ID

gives me the list of zones the user is allowed in.

Now to make things annoying if a user is allowed in all zones then they don't appear in the UserZone at all.

So is there anyway i can get the list of ZoneTableID's per specific user user, if the count is 0 then don't apply the where clause, if count > 0 then only show the specific zone names, all in one query.

If that makes sense

Thanks

Upvotes: 1

Views: 663

Answers (2)

majodi
majodi

Reputation: 536

Did you consider a NOT IN subquery? As in: where ... OR :ID NOT IN (select usertableid from userzone where usertableid = :ID)

Upvotes: 0

CL.
CL.

Reputation: 180020

Note: you don't need to use an outer join, and the query could be rewritten as:

SELECT ID, Name
FROM ZoneTable
WHERE ID IN (SELECT ZoneTableID
             FROM UserZone
             WHERE UserTableID = :ID);

Anyway, you can add the condition to the WHERE clause:

SELECT ID, Name
FROM ZoneTable
WHERE ID IN (SELECT ZoneTableID
             FROM UserZone
             WHERE UserTableID = :ID)
   OR NOT EXISTS (SELECT ZoneTableID
                  FROM UserZone
                  WHERE UserTableID = :ID);

or add a second query for the second case:

SELECT ID, Name
FROM ZoneTable
WHERE ID IN (SELECT ZoneTableID
             FROM UserZone
             WHERE UserTableID = :ID)
UNION ALL
SELECT ID, Name
FROM ZoneTable
WHERE NOT EXISTS (SELECT ZoneTableID
                  FROM UserZone
                  WHERE UserTableID = :ID);

Upvotes: 1

Related Questions