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.

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

MySQL supports the IN clause. But, it doesn't support sub-selects.

In other words, you can do something like
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:

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

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



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.

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

"...is viewable by all"?!?! I have never heard of such a syntax. Is this some Microsoft SQL server thing?

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