Tricky SQL SELECT syntax, can it be done? - Macromedia ColdFusion

[Mattsala]

Hi, Wondering if this can be done in SQL (db = mySQL v3.23) tbl_announcements
announcementID (primary key) groupID (list of foreign keys i.e. 1,3,5,12,14)
announcement I have put the groupID as a list, to save database space, as many
annoucements apply to many different groups. So Im on a page for group 4 how
do I select those records from a table where the groupID list contains 4?
SELECT * FROM tbl_annoucement WHERE groupID contains (4); I dont think that
mySQL supports 'contains'... my current work around is to pull the entire
table, then do a cfloop query, and only spit out the record based on a cfif
ListFind(rs_dataset.groupID,4) NEQ 0 blah blah blah.. but of course this is not
very efficient! (ive tried QoQ, but no luck). Any help much appreciated,
Thanks! Matts

[paross1]

You basically ed yourself by making a very bad design decision "to save
database space". You should NEVER model a many-to-many relationship this way.
You should have created an associative entity (link table) to resolve this
many-to-many relationship, rather than denormalizing the database in this
manner. You are now reaping the benefit of a bad design decision (I'm sure that
it seemed like a good idea at the time). If at all possible, I would abandon
this design and model it correctly, then your SQL would be fairly straight
forward, and much easier to implement.

Phil

[Mattsala]

Thanks for the reply Phil, I've changed the structure as per your suggestion.
(As a note, I do use link tables regularily, thought I'd try something
different.. ugh!). So my new structure is as follows tbl_announcement
announcementID announcement postDate expireDate notes tbl_group groupID
groupName notes tbl_group_X_announcement group_announcementID
announcementID_fk groupID_fk - one record in this last table for each time the
annoucement applies to a group - ive put a primary key in this link table, as
mySQL seems to like to warn me (well Navicat mySQL tool) everytime I do an
update on a table with no primary key... I realize its not necessary). Is this
the correct structure? Cheers, Matts

[paross1]

It looks OK to me. The separate PK field is really not necessary, as you could
designate the announcementID_fk and groupID_fk together as a composite PK, but
it really doesn't hurt anything to have the group_announcementID as is. Once
you have a "link" table like this, you have the benefit of being able to add
other attributes to this entity, things like a status field, or date added,
etc. Now you can easily query for your groupID contains (4) with something like
this:

SELECT *
FROM tbl_annoucement a INNER JOIN tbl_group_X_announcement ga
ON a.announcementID = ga.announcementID_fk
AND ga.groupID_fk = 4

Plus, you can now find announcements by groupName, or groupNames by
announcements, etc., by joining all three.

Phil