Need help on a simple query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Need help on a simple query

Author

Topic

carlo88
Starting Member

4 Posts

Posted - 2007-03-21 : 10:26:06

I currently have two queries, both return nDeviceID's. I want to link the two quries so only the nDeviceID's which are present in both result sets are returned.

SELECT DISTINCT Device.nDeviceID
FROM Device, DeviceType, NetworkInterface
WHERE Device.nDeviceTypeID = DeviceType.nDeviceTypeID
AND bRemoved = 0
AND DeviceType.sDisplayName = N'Router';

SELECT DISTINCT NetworkInterface.nDeviceID
FROM NetworkInterface
WHERE NetworkInterface.sNetworkAddress LIKE '10.%.1';

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:31:07

[code]SELECT nDeviceID
FROM (
SELECT DISTINCT Device.nDeviceID
FROM Device,
DeviceType,
NetworkInterface
WHERE Device.nDeviceTypeID = DeviceType.nDeviceTypeID
AND bRemoved = 0
AND DeviceType.sDisplayName = N'Router'
) AS t1
INNER JOIN (
SELECT DISTINCT NetworkInterface.nDeviceID
FROM NetworkInterface
WHERE NetworkInterface.sNetworkAddress LIKE '10.%.1'
) AS t2 ON t2.nDeviceID = t1.nDeviceID[/code]The first query is not very well written! You are creating a lot of duplicate records not needed due to CROSS JOINing.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 10:31:50

[code]SELECT DISTINCT Device.nDeviceID
FROM Device, DeviceType, NetworkInterface
WHERE Device.nDeviceTypeID = DeviceType.nDeviceTypeID
AND bRemoved = 0
AND DeviceType.sDisplayName = N'Router'
UNION
SELECT DISTINCT NetworkInterface.nDeviceID
FROM NetworkInterface
WHERE NetworkInterface.sNetworkAddress LIKE '10.%.1'[/code]

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:32:55

What has NETWORKINTERFACE to do with first query?
And bRemoved is not table prefixed.

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 10:33:57

or this?

SELECT 
	DISTINCT Device.nDeviceID 
FROM 
	Device, DeviceType, NetworkInterface
WHERE 
	Device.nDeviceTypeID = DeviceType.nDeviceTypeID 
	and NetworkInterface.nDeviceID = Device.nDeviceTypeID
	AND bRemoved = 0
	AND DeviceType.sDisplayName = N'Router'
	AND NetworkInterface.sNetworkAddress LIKE '10.%.1'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:35:57

This query might perform better.

SELECT DISTINCT	d.nDeviceID 
FROM		Device AS d 
INNER JOIN	DeviceType AS t ON t.nDeviceTypeID = d.nDeviceTypeID
INNER JOIN	NetworkInterface AS i ON i.nDeviceID = d.nDeviceID AND i.sNetworkAddress LIKE '10.%.1'
WHERE		d.bRemoved = 0
		AND t.sDisplayName = 'Router'

Peter Larsson
Helsingborg, Sweden

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-03-21 : 10:44:10

Even better:

SELECT DISTINCT	d.nDeviceID 
FROM		Device AS d 
INNER JOIN	DeviceType AS t ON t.nDeviceTypeID = d.nDeviceTypeID
                AND d.bRemoved = 0 AND t.sDisplayName = 'Router'
INNER JOIN	NetworkInterface AS i ON i.nDeviceID = d.nDeviceID
                AND i.sNetworkAddress LIKE '10.%.1'
                AND d.bRemoved = 0

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-03-21 : 10:55:27

One more before I drive home.

SELECT DISTINCT	d.nDeviceID 
FROM		Device AS d 
INNER JOIN	DeviceType AS t ON t.nDeviceTypeID = d.nDeviceTypeID AND t.sDisplayName = 'Router'
INNER JOIN	NetworkInterface AS i ON i.nDeviceID = d.nDeviceID AND i.sNetworkAddress LIKE '10.%.1'
WHERE		d.bRemoved = 0

Peter Larsson
Helsingborg, Sweden

carlo88
Starting Member

4 Posts

Posted - 2007-03-23 : 14:38:39

Wow, you guys really know your SQL queries. It worked like a charm. Thank you for all your help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources