Professional Web Applications Themes

select multiple values for column in one query - MySQL

I have a table with a column that contains a comma seperated list of ids as TEXT that are used to reference multiple rows in another table. so "2,4" would reference id 2 and 4 in table2. select * from table2 where id = 3 or id = 4; Is there a way to do that without going through the list and delimiting with " or id = "? Something like select * from table2 where id in values(3,4); I thought I'd seen something like that before but I can't find it in the mysql manual. Is using a TEXT ...

  1. #1

    Default select multiple values for column in one query

    I have a table with a column that contains a comma seperated list of
    ids as TEXT that are used to reference multiple rows in another table.
    so "2,4" would reference id 2 and 4 in table2.

    select * from table2 where id = 3 or id = 4;

    Is there a way to do that without going through the list and
    delimiting with " or id = "? Something like

    select * from table2 where id in values(3,4);

    I thought I'd seen something like that before but I can't find it in
    the mysql manual.

    Is using a TEXT for an id list bad structure? Is there a better way to
    store an array of ids in a column in mysql?

    techcalgary@hotmail.com Guest

  2. #2

    Default Re: select multiple values for column in one query

    I guess I could do

    select * from table2 order by field( id, 3, 4 ) desc limit 2;

    but isn't there a way to do that in the where clause?

    techcalgary@hotmail.com Guest

  3. #3

    Default Re: select multiple values for column in one query

    com wrote in news:1174793194.111464.20170
    o5g2000hsb.googlegroups.com:
     

    Without answering the main question, and trying to be helpful, can I say:

    a) When you want to store an array of IDs in a table, it usually means you could
    use better table design, and most often, adding a table that has a 'many-to-
    many' type of relationship. In your example, I'm not quite sure what your first
    selection would be, but let's say you're looking for "basketball", and you want
    your query to return information about table2's id '3' and id '4'. Instead of
    having 'basketball' as one column in your first table and '3,4' in the column
    next to it, you'd JUST have 'basketball' in your table, with it's ID (1). Then,
    in your new many-to-many table, you'd have one column referencing the first
    table's ID (say, "table1ID") and a column for the tableID in table 2 (say,
    "table2ID"). So, in your case, in this new many-to-many table, there are TWO
    entries: i) '1' in the first column and '3' in the second and ii) '1' in the
    first column and '4' in the second. Just by looking at this new table, you can
    see that entry 1 in your first table('basketball') is related to ID 3 and ID 4
    from the second table. A query that would return this information (untested,
    example only) is:

    SELECT table2.* FROM table1
    JOIN newtable ON table1.ID=newtable.table1ID
    JOIN table2 ON newtable.table2ID=table2.ID
    WHERE table1.ID=1

     

    Storing text as a primary key (which I think is your question?) is a bad idea.
    Numbers are fastest, then VARCHARS i suppose, but certainly TEXT/BLOBs should be
    avoided.



    Good Guest

  4. #4

    Default Re: select multiple values for column in one query

    com wrote: 

    Yes, this is a poor design. Google for "database normalization" for a
    lot of good information.

    When you have a relationship, you're generally better off creating a
    third table with two columns - one for each id associated in the
    relationship. Much more flexible.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. multiple select list default values (asp/vb)
    By Bill the Geek in forum Dreamweaver AppDev
    Replies: 0
    Last Post: February 27th, 03:02 PM
  2. Multiple select listbox values in query
    By DC Gringo in forum ASP.NET Building Controls
    Replies: 2
    Last Post: April 14th, 06:28 AM
  3. Replies: 5
    Last Post: September 26th, 10:49 AM
  4. Sum a column of values from a MySQL query
    By Ben C. in forum PHP Development
    Replies: 3
    Last Post: August 5th, 03:37 PM
  5. [PHP] Sum a column of values from a MySQL query
    By Ben C. in forum PHP Development
    Replies: 8
    Last Post: August 5th, 01:50 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