Professional Web Applications Themes

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

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 ...

  1. #1

    Default Tricky SQL SELECT syntax, can it be done?

    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

    Mattsala Guest

  2. #2

    Default Re: Tricky SQL SELECT syntax, can it be done?

    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

    paross1 Guest

  3. #3

    Default Re: Tricky SQL SELECT syntax, can it be done?

    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

    Mattsala Guest

  4. #4

    Default Re: Tricky SQL SELECT syntax, can it be done?

    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


    paross1 Guest

Similar Threads

  1. Need help formatting syntax for SELECT query on a Bittype data field
    By ca_nimrod in forum Coldfusion Database Access
    Replies: 2
    Last Post: January 11th, 03:23 AM
  2. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  3. it's tricky!
    By ciecierega webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 0
    Last Post: November 10th, 03:18 PM
  4. SELECT: Syntax error. Please help
    By sam in forum PHP Development
    Replies: 14
    Last Post: September 29th, 10:32 PM
  5. Help with Select Syntax
    By Chaster in forum Microsoft Access
    Replies: 1
    Last Post: July 22nd, 10:24 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139