Thread: sql select help

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12

    Question sql select help


    I have two table having a one to many relationship

    device
    device_id
    device_name

    ip_address
    ip_id
    ip_address
    device_id

    I want to select those device_name that doesn't have an ip address record in the ip_address table
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2003
    Location
    #devshed @ irc2.beyondirc.net
    Posts
    231
    Rep Power
    12
    Code:
    SELECT device_name FROM device WHERE device_id IN (
         SELECT device_id FROM table2 WHERE ip_address = NULL
    )
    .
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    13
    A device that hasn't an IP adress shouldn't be in the ip_adress table.

    so it could be :

    Code:
    select * from device
    where not exists (
      select 1 from ip_adress
      where device.device_id=ip_adress.device_id
    );

    Or if a device that hasn't an IP adress has a null IP :

    Code:
    select device.*
    from device inner join ip_address using (device_id)
    where ip_adress is null;
    here the subselect is useless

    (sorry for my english)
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jun 2003
    Posts
    40
    Rep Power
    12

    relationship headache


    I also very confuse about the relationship between device and ip address because other than server, tape drive and storage device doesn't have any ipaddress. Since I decide to keep all device in the same table I am doing in this way.
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2002
    Location
    France
    Posts
    55
    Rep Power
    13

    Re: relationship headache


    Originally posted by wanaka
    other than server, tape drive and storage device doesn't have any ipaddress. Since I decide to keep all device in the same table I am doing in this way.
    Yes, you can keep this way, and when a device isn't connected to the network it has no record in the IP_ADDRESS table

    And I think it isn't a one to many relashionship, it is :

    DEVICE (0-1) ------------------- (1-1) IP_ADDRESS

    Because a device can have no or 1 ip address and an IP adress has 1 and only 1 device.

    All devices appear in the DEVICE table and only the one that have an IP have their ID in the IP_ADDRESS table.

IMN logo majestic logo threadwatch logo seochat tools logo