question

Francis Clement avatar image
Francis Clement asked

conditional sql statement

I have the following problem (simplified)

a table person with field personid related to table person_events with personid and eventid

another table events with eventid

A person can subscribe to many events from the events with the simple sql sentence

select personid                     
from person p                     
innner join person_events pe on p.personid=e.personid                    
inner join events e on pe.eventid=e.eventid                    

In this way I can see a list of persons who subscribed for one or more particular events (I can use distinct to have one person)

But sometimes a person can subscribe to all events and, instead of putting everytime eventid 1,2,3,4 in the table person_events I want to make a code for the event for instance code 0. If I put code 0 in the table person_events I want to subscribe the person to all the events in the table events. If I put all codes from events in the table person_events I would off course have the same effect but I don't want that.

Is there a solution with conditional joins?

joinsconditional
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

graz avatar image
graz answered

If you end up going this route I would use a UNION. The first query is all the normal rows that are joined together. The second query is all the zero rows. That way you avoid a funky join and you have two fast simple queries rather than one strange one. Make this a view and you should be able to use it easily.

Another option is to build a separate table of just people that have subscribed to everything. You can use this table in a UNION with those that have subscribed to specific events.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

RickD avatar image
RickD answered

I am guessing you have foreign keys between your tables. Even if you don't, you really should keep referential integrity, so the easiest way to do this is to insert row with event id of 0 in events.

If eventid is an IDENTITY then you will need the following:

SET IDENTITY_INSERT events ON            
            
INSERT INTO events (<FieldList>)            
VALUES (0,'All Events etc')            
            
SET IDENTITY_INSERT events OFF            

From here, you can just do a join. It wil take some logic in your code if you want to display all events, but should be simple enough in your front end, where you can match it and if 0 then you can do a select from events as a secondary query.

I don't fully understand what the problem with putting a row for each event in the table is exactly?

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.