Thread: SQL IN syntax

    #1
  1. No Profile Picture
    Smelly coder man
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Posts
    6
    Rep Power
    0

    SQL IN syntax


    I know in SQL Server 7 you can do the following:

    SELECT * FROM table WHERE id IN (SELECT id FROM table2 ...);

    I'm using mySQL however, and it does not support the IN clause (in this way anyway).
    Can someone help me with the alternative syntax in mySQL?

    Thanks in advance.
    Neil.
  2. #2
  3. SwollenMember
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2000
    Location
    the master control
    Posts
    264
    Rep Power
    14
    i know in oracle you can do something like this...might be the same in mySQL

    select a.id
    from tablea a,
    (select c.id
    from tableb c
    ) b
    where
    a.id = b.id;

    do a search on google for nested select statements
  4. #3
  5. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    MySQL supports the IN clause. But, it doesn't support sub-selects.

    In other words, you can do something like
    Code:
    SELECT * FROM table WHERE id IN (1,4,5,7);
    IN operates on a set of values, which means that a DBMS with subselects can use the SELECT to create that set. Really, though, this is not the most efficient way to query related data in tables anyway. Try a standard join, which should work in any SQL database:
    Code:
    SELECT table1.* FROM table1,table2 WHERE table1.id = table2.id;
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  6. #4
  7. No Profile Picture
    Smelly coder man
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Posts
    6
    Rep Power
    0
    Thankyou for your help.
    I've got it sorted. It's a shame mySQL doesn't support sub-queries. It'd make things faster and easier in the long run.

    Ah well.

    Thankyou again..
  8. #5
  9. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    It is a shame that MySQL doesn't support subqueries, but still, you should only use them when a join won't work. Using a subquery in the WHERE clause of a query will force a query to be executed while looping through each row of your main query, which is very inefficient. (in fact, it can prevent the use of indexes, which will really slow down your query). Try it on a larger table and you will see the difference.
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  10. #6
  11. No Profile Picture
    Smelly coder man
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2001
    Posts
    6
    Rep Power
    0
    The way I've done it is to select the data I wanted, then use PHP to do the looping and create the comma seperated values for the IN() clause.

    The reason for doing this is I needed to get the last 15 rows in the table in id ascending order.
    The only way I could see to do this is to first selected the top 15 in descending order, then to do a re-order using IN. Like this:

    PHP Code:
    $sql "SELECT id FROM table ORDER BY id DESC LIMIT 0, 15";
    $result mysql_query($sql$db);
    //A while loop to create $ids (a comma separated list of ids)

    $sql "SELECT * FROM table2 WHERE id IN ($ids) ORDER BY id ASC"
    I know, as you said the looping may be bad practice, but I can't think of another way to do it, and, it's only going to ever have a maximum of 15 iterations of the loop, so in a way it's kinda justified.

    I can also see how if I wasn't doing this weird re-ordering, how the cross join rycamor suggested would work.

    Thanks for the help.
  12. #7
  13. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    San Diego, CA
    Posts
    10
    Rep Power
    0

    The question I was about to ask...


    Hey, this topic is just what I needed...

    I'm programming (very unfortunately) in ASP and I'm implementing a search engine.

    Essentially I have a table and the elements has one or more "detail" elements attached to it. There is also another table that determines access to the elements of the first table.

    So the guy whose mess I'm cleaning up did:

    Select Distinct [data] From [table] where [conditions] in (select * from [accesstable] where [userid] is in table OR is viewable by all) Order by [field]

    Which, of course, works but it is doing a separate [access] search within the search itself.

    The problem is they want an 'advanced' search that accesses the detail elements and compares them to supplied text - which seems like I'd need to add ANOTHER IN statement to the above giving me two nested loops when one was bad enough.

    Am I missing something or is there another way to do this within SQL?

    Any and all input appreciated,

    =tkk
  14. #8
  15. No Profile Picture
    Gödelian monster
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jul 1999
    Location
    Central Florida, USA
    Posts
    2,307
    Rep Power
    61
    Code:
    Select Distinct [data] From [table] where [conditions] in (select * from [accesstable] where [userid] is in table OR is viewable by all) Order by [field]
    "...is viewable by all"?!?! I have never heard of such a syntax. Is this some Microsoft SQL server thing?
    The real n-tier system:

    FreeBSD -> PostgreSQL -> [any_language] -> Apache -> Mozilla/XUL

    Amazon wishlist -- rycamor (at) gmail.com
  16. #9
  17. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2002
    Location
    San Diego, CA
    Posts
    10
    Rep Power
    0
    > "...is viewable by all"?!?! I have never heard of such a syntax. Is this some Microsoft SQL server thing? <

    No, not that M$ crap doesn't have enough of it's own quirks, I was unclear.
    Basically each procedure is owned by a group and each user has a series of fields that give them read access or write access to a group - or no access by default. But a procedure can be marked as "readable by all" as well over-riding read-level access.

    So in addition to SELECTING records based on given criteria I then need to check to see if this user has group read level access to view this record - by checking the user's table entries for one that matches the group number - unless this procedure is marked "readable by all".

    (The 'readable by all' is designated by making the owner-group negative -4 versus 4 which is okay I guess except he made the ADMIN group 0. Don't get me started.)

    So needless to say the data is NOT organized how I would have done it but I'm stuck with it. So basically I need to

    SELECT records recordetails FROM table1 tabledetails1 WHERE record.field1 matches text LIKE OR (record.field2 matches text LIKE OR record.field3 matches text LIKE) ... IN (SELECT record.ID = detail.ID AND detail.field1 matches text LIKE OR detail.field2 matches text LIKE) IN (SECURITY check)

    //Essentially looping through the fields and their respective details trying to match user-supplied text for both record and the to_many record details and THEN security check the record against the users_ID.

    Ug. I guess I could rearrange the SQL check to fetch the record if it matches the criteria AND makes the security check AND check the details.
    Does SQL bail out at the first failure? At least at that point I wouldn't be fetch record.details for something that will eventually fail the security check anyway.

    More and more I feel like I should fetch the records and then search them post fetch but ASPs record handling is so pathetic that I hesitate to do that.

    Ug. 8P

    TIA,

    =tkk

IMN logo majestic logo threadwatch logo seochat tools logo