Reputation: 315
I need your help on a SQL request. Here are my tables:
Table Events:
id | idType | used_by
1 1 Marc
2 1
3 1 Henry
Table Types:
id | name | activate
1 Type 1 0
2 Type 2 1
As you can see, I have Type 1 inactivate, but 2 persons are using it. I would like to display records from Events that have a type activated and also display records that are used by someone even if the type is inactivated.
The SQL below only displays events that have type activated:
CREATE TABLE events (id int(11), idType int(11), used_by varchar(50));
INSERT INTO events VALUES (1, 1, ''), (2, 1, 'Marc'), (3, 1, 'Henry'), (4, 2, ''), (5, 2, 'Sandy');
CREATE TABLE types (id int(11), name varchar(50), activate int(1));
INSERT INTO types VALUES (1, 'Type1' , 0), (2, 'Type 2', 1);
SELECT events.id, types.name, events.used_by
FROM events
INNER JOIN types
ON events.idType = types.id
WHERE types.activate = 1
Here is the sqlfiddle: sqlfiddle.com/#!9/7a36f/1 and as a result i would like from events ids: 2, 3 , 4 ,5
Upvotes: 0
Views: 60
Reputation: 2312
Just add an OR condition for the used_by field:
SELECT events.id, types.name, events.used_by
FROM events
INNER JOIN types
ON events.idType = types.id
WHERE types.activate = 1
OR events.used_by != ''
Edited to replace events.used_by check to look for non-blank rather than not null based on updated information.
Upvotes: 1
Reputation: 31
correct me if I understood wrong, you want to display all records from events table, which are used by someone, irrespective of type activated or deactivated,
for this you can simplu qyery on event table
Select * from events where used_by is not null and used_by!=''
Upvotes: 0
Reputation: 146541
Select e.*
from evsnts e
join types t on t.id = e.idtype
where t.Activate = 1 or
(e.used_by is not null and len(e.used_By) > 0)
Upvotes: 1
Reputation: 827
Try to use
SELECT events.id, types.name, events.used_by FROM events INNER JOIN types ON events.idType = types.id WHERE types.activate=1 or trim(events.used_by)<>''
Or
SELECT events.id, types.name, events.used_by FROM events INNER JOIN types ON events.idType = types.id WHERE types.activate=1 or not isnull(events.used_by)
Upvotes: 1