SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict Garrett's Avatar
    Join Date
    Feb 2001
    Location
    England
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help using IN for a query

    hi

    Stuck on a query which i think uses a IN clause but I'm not sure. I have queried a table for a list of reference ids for objects and a list of ids of users who created these objects. With both of these i then created an array $tempreferenceid (for the reference ids for objects) and $tempuserid (for the ids of users). I now need one select query for another two tables 'object' and 'adminsession' for the details of each object using the object id and the details of each user using the user id. this is what I have...

    SELECT object.url, object.file_url, object.icon_url, object.title, object.text, object.description, object.keywords, object.type, adminsession.useralias
    FROM object, adminsession WHERE object.id IN '$tempreferenceid' AND adminsession.userid IN '$tempuserid'";

    any ideas where I am going wrong?

    a+
    gar

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    you have two problems:

    1) you did not specify your table relation anywhere. (and, as a side note, since you are, supposedly, on a version oy mysql that supports subqueries, you should use JOINs, not multiple tables in the FROM clause.)
    2) you need to convert the arrays $tempreferenceid and $tempuserid to strings to include them in the manner you have.

    taking those two problems in to account, here is my suggestion:
    PHP Code:
    $tempreferenceid implode(', '$tempreferenceid);
    $tempuserid implode(', '$tempuserid);

    $result mysql_query("
    SELECT
          o.url
        , o.file_url
        , o.icon_url
        , o.title
        , o.text
        , o.description
        , o.keywords
        , o.type
        , a.useralias
    FROM object o
    JOIN adminsession a ON
        o.id = a.object_id
    WHERE
        o.id IN (
    $tempreferenceid)
        AND adminsession.userid IN (
    $tempuserid)"

  3. #3
    SitePoint Addict Garrett's Avatar
    Join Date
    Feb 2001
    Location
    England
    Posts
    352
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by longneck
    you have two problems:

    1) you did not specify your table relation anywhere.
    if i understand correctly you mean the relation between the adminsession table and the object table, this is held in the first table that is queried seperately, there is no relevant relation between the two tables queried in this query.



    Quote Originally Posted by longneck
    (and, as a side note, since you are, supposedly, on a version oy mysql that supports subqueries, you should use JOINs, not multiple tables in the FROM clause.)
    2) you need to convert the arrays $tempreferenceid and $tempuserid to strings to include them in the manner you have.

    taking those two problems in to account, here is my suggestion:
    PHP Code:
    $tempreferenceid implode(', '$tempreferenceid);
    $tempuserid implode(', '$tempuserid);

    $result mysql_query("
    SELECT
          o.url
        , o.file_url
        , o.icon_url
        , o.title
        , o.text
        , o.description
        , o.keywords
        , o.type
        , a.useralias
    FROM object o
    JOIN adminsession a ON
        o.id = a.object_id
    WHERE
        o.id IN (
    $tempreferenceid)
        AND adminsession.userid IN (
    $tempuserid)"
    since there is no relation i can't use join and belive I have to go with...

    $query = "SELECT object.url, object.file_url, object.icon_url, object.title, object.text, object.description, object.keywords, object.type, adminsession.useralias FROM object, adminsession WHERE object.id IN (".implode(', ', $tempreferenceid).") AND adminsession.userid IN (".implode(', ', $tempuserid).") ";

    which you pointed me in the right direction of. out of cusiousity if there was a relation between the tables why is JOIN preferable to multiple tables in the FROM clause?

    thanks.

    a+
    gar

  4. #4
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    besides the fact it's easier to understand? some SQL optimizers won't correctly optimize multiple tables in the FROM clause.

    and it also avoids cases like yours. this query is not going to return what you expect. for example, let's say it pulls back object.id 1 and 2, and adminsession.userid 5, 6 and 7. your results (with some columns removed) are going to look like this:
    Code:
    o.id | a.id
    -----+-----
    1    | 5
    2    | 5
    3    | 5
    1    | 6
    2    | 6
    3    | 6
    when something like one of these two is what you are expecting:
    Code:
    o.id | a.id      o.id | a.id
    -----+-----      -----+-----
    1    |           1    | 5
    2    |           2    | 6
    3    |           3    |
         | 5
         | 6
    you need to use two separate queries.


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
  •