sql select help
I have two table having a one to many relationship
I want to select those device_name that doesn't have an ip address record in the ip_address table
SELECT device_name FROM device WHERE device_id IN (
SELECT device_id FROM table2 WHERE ip_address = NULL
A device that hasn't an IP adress shouldn't be in the ip_adress table.
so it could be :
select * from device
where not exists (
select 1 from ip_adress
Or if a device that hasn't an IP adress has a null IP :
here the subselect is useless
from device inner join ip_address using (device_id)
where ip_adress is null;
(sorry for my english)
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.
June 10th, 2003, 07:57 AM
Re: relationship headache
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.