select * from

[sasha hantz]

i got 2 tables
table 1 called "products" and it got 2 columns : "name" and "loc"
table 2 called "locations" with 2 columns : "id" and "loc"

i need to run search in the products table
i need to find all the records from table "products" that got in their "loc" column
the same locations as in the locations table from row id 7 to id 9

for example if table location -id=7 loc=usa
id=8 loc=uk
id=9 loc=canada
i want to get from the products table all the record where the column "loc"
have usa or uk or canada...

mySQL="SELECT * FROM products WHERE loc=("SELECT * FROM Locations where id>1 and id<10 )
i wrote this select line but it not working...where i got wrong?

10k's

[Daemonspyre]

You are not getting this right because your subquery (the "SELECT * FROM Locations where id>1 and id<10 ) will return more than 1 row, causing your main query to error. Also, you have a double quote in the subquery which would cause it to error outright.

Try

mySQL="SELECT * FROM products WHERE loc IN (SELECT * FROM Locations WHERE id > 1 AND id < 10 )"

OR

mySQL="SELECT * FROM products WHERE loc IN (SELECT * FROM Locations WHERE id BETWEEN 1 AND 10 )"

Lastly, unless absolutely necessary, NEVER USE SELECT *. It does not use indexes (not optimized) because it forces your database to search the ENTIRE table. Always tell the query which fields you want to see.

[sasha hantz]

you meen to specify a column name instead the *
?

[rafiki]

stop cross posting, here or the mysql forum!

[Daemonspyre]

Yes, I mean use the column (or field) names, not just *.

Say your table1 has 3 columns in it... col1, col2, col3... but you only want to see 2 of those columns.

Instead of

SELECT * FROM table1

which would do a full table scan and not use indexes, and you would have to programatically 'ignore' one field, you would use

SELECT col1, col3 FROM table1

When you tell the database which fields you want to look at, it can use INDEXes that you cannot use with SELECT *.

Hope this helps, and in the future, only post in one forum. It may not be in the right place, but that's what moderators are for - to move posts to the right spots.