Results 1 to 11 of 11

Thread: SQL query help?

  1. #1
    Join Date
    Mar 2003
    Location
    Duluth MN
    Posts
    3,863

    SQL query help?

    I'm trying to figure out how to format the following query to do the following:


    I have 2 tables, one containing a list of locations and IP addresses:

    [id][location_id][ip0][ip1][ip2]

    And then a table containing pins, or access codes

    [id][location_id][pin][active]

    I need a query that will return the pin row based on the following details.

    I'm given a pin, and an IP address. So I need to match the pin to the location of the IP address. The IP address could be one of the 3 in the locations table.

    It's been a LONG time since I've done this kind of join/sql query. So any help in the right direction is much appreciated!

  2. #2
    Join Date
    Mar 2004
    Posts
    1,303
    PHP Code:
    $query "SELECT loc.*, p.* FROM `pins` p 
    INNER JOIN `locations` loc ON  (p.location_id = loc.location_id) WHERE p.pin ='
    $pin' AND (loc.ip0 ='$ip' OR loc.ip1='$ip' OR loc.ip2 = '$ip') LIMIT 1"

  3. #3
    Join Date
    Dec 2002
    Location
    Dallas, TX
    Posts
    210
    Quote Originally Posted by orbitz
    PHP Code:
    $query "SELECT loc.*, p.* FROM `pins` p 
    INNER JOIN `locations` loc ON  (p.location_id = loc.location_id) WHERE p.pin ='
    $pin' AND (loc.ip0 ='$ip' OR loc.ip1='$ip' OR loc.ip2 = '$ip') LIMIT 1"

    Or maybe he should hire a programmer since he didn't say his abilities.
    My Name is Joe!
    ICQ: 280 889 133

  4. #4
    Join Date
    Mar 2004
    Posts
    1,303
    Quote Originally Posted by JoeBannon
    Or maybe he should hire a programmer since he didn't say his abilities.
    you are being childish. Do you have to rob other people's thread too? At least, he has done some homework! Don't let your ego bother you that much Chill out!
    Last edited by orbitz; 06-27-2006 at 07:54 PM.

  5. #5
    Join Date
    Dec 2002
    Location
    Dallas, TX
    Posts
    210
    What ego? Oh, that's right, you like to assume.

    BTW, it' wasn't off topic. I gave advice ;-).
    My Name is Joe!
    ICQ: 280 889 133

  6. #6
    Join Date
    Mar 2004
    Posts
    1,303
    your advice works! such a kiddo. I stopped here

  7. #7
    Join Date
    Apr 2006
    Location
    Devon, UK
    Posts
    193
    Can I ask what the point of having Limit 1 at the end is? I mean if it does select more then 1 its wrong anyway? How is it helping?

  8. #8
    Join Date
    Mar 2004
    Posts
    1,303
    Quote Originally Posted by Kalyse
    Can I ask what the point of having Limit 1 at the end is? I mean if it does select more then 1 its wrong anyway? How is it helping?
    yeah, it doesn't need that limit 1 if the location_id is unique. I wasn't sure why he needs to have "id" on both table for if location_id is a unique id in each table.
    Last edited by orbitz; 06-27-2006 at 09:26 PM.

  9. #9
    Join Date
    Mar 2003
    Location
    Duluth MN
    Posts
    3,863
    Location ID is not unique, but the Pin + Location combination is unique.

    Thanks for the JOIN syntax, I was having a beast of a time trying to remember the exact syntax, and was having problems trying to figure it out based on the mysql documentation at http://dev.mysql.com

    As for questioning my programming ability, I take that as a direct putdown against me. For you assume because I have a question about SQL query syntax, that I do not know how to program. Every programmer has a brain fart every now and then, and good programmers know when to ask for help, rather than waste valuable (billable) time because they have an ego problem and are afraid to ask for help.

    FYI, this query is being used in a mysql 5.0 Stored Procedure being called by a RADIUS server for authentication, with a RoR app managing the datbase on the back-end.

  10. #10
    Join Date
    Mar 2004
    Posts
    1,303
    Quote Originally Posted by Utaria-Aaron

    As for questioning my programming ability, I take that as a direct putdown against me. For you assume because I have a question about SQL query syntax, that I do not know how to program. Every programmer has a brain fart every now and then, and good programmers know when to ask for help, rather than waste valuable (billable) time because they have an ego problem and are afraid to ask for help.
    you should just ignore JoeBannon, he was trying to get my attention by posting his argument on your thread.

  11. #11
    He writes the same garbage in all his recent posts. Carry on ...
    "The only difference between a poor person and a rich person is what they do in their spare time."
    "If youth is wasted on the young, then retirement is wasted on the old"

Posting Permissions

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