Alexking2005
Alexking2005

Reputation: 315

SQL need help on a request

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

Answers (4)

morgb
morgb

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

Yogi
Yogi

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

Charles Bretana
Charles Bretana

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

Christian
Christian

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

Related Questions