Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5

Thread: select * from

  1. #1
    New to the CF scene
    Join Date
    May 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Question select * from

    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

  • #2
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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

    Code:
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.

  • #3
    New to the CF scene
    Join Date
    May 2007
    Posts
    7
    Thanks
    0
    Thanked 0 Times in 0 Posts
    you meen to specify a column name instead the *
    ?

  • #4
    Senior Coder rafiki's Avatar
    Join Date
    Aug 2006
    Location
    Floating around somewhere...
    Posts
    2,038
    Thanks
    19
    Thanked 42 Times in 42 Posts
    stop cross posting, here or the mysql forum!

  • #5
    Regular Coder
    Join Date
    Mar 2007
    Posts
    505
    Thanks
    1
    Thanked 19 Times in 19 Posts
    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.
    To say my fate is not tied to your fate is like saying, 'Your end of the boat is sinking.' -- Hugh Downs
    Please, if you found my post helpful, pay it forward. Go and help someone else today.


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •