Professional Web Applications Themes

Where in does not work for this - MySQL

I have a table that has a column of partids. They are seperated with commas. I need to select the partids from certain columns and use them to select from another table. They are the ids for the subjects table. If I grab the ids from the table then do another query using them in a where id in clause it works fine. But I can't use a subquery like where id in (select partids from collections where id = 346). It only uses the first number in the partids and that is it. Is there a way around having ...

  1. #1

    Default Where in does not work for this

    I have a table that has a column of partids. They are seperated with
    commas. I need to select the partids from certain columns and use
    them to select from another table. They are the ids for the subjects
    table. If I grab the ids from the table then do another query using
    them in a where id in clause it works fine. But I can't use a
    subquery like where id in (select partids from collections where id =
    346). It only uses the first number in the partids and that is it.
    Is there a way around having to do 2 queries.

    Extremest Guest

  2. #2

    Default Re: Where in does not work for this

    Extremest wrote: 

    Redesign your tables and have a multi-to-multi or maybe one-to-multi table
    which you use to connect things from one table to another instead of your
    column with the comma separated IDs.

    --

    //Aho
    J.O. Guest

  3. #3

    Default Re: Where in does not work for this

    Extremest wrote: 

    And after you've done what Aho suggested, use a JOIN instead of a subquery,
    it is many many many many times more efficient.


    Paul Guest

Similar Threads

  1. Links don't work in Shockwave movie but work in p
    By rokarege in forum Macromedia Director Basics
    Replies: 1
    Last Post: May 3rd, 02:02 AM

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