Yikes, 'simple' query one table, weird criteria,

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 2005 Forums

Transact-SQL (2005)

Yikes, 'simple' query one table, weird criteria,

Author

Topic

corecomps
Starting Member

2 Posts

Posted - 2009-01-28 : 00:29:04

I wish I had a simple one line way to describe it.

Basically I have a table of data that includes the following columns:

Address - string
PCName - string
Speed - int
RAM - int

I would like to run a query that gives me a list of addresses where *all* rows in the database for that address have CPU > X and RAM > Y

For example:

Table Data:

123 Fake Street, PC1, 500, 128
123 Fake Street, PC1, 1000, 256
234 Fake Street, PC1, 1000, 256
234 Fake Street, PC1, 1000, 256
345 Fake Street, PC1, 1000, 256
345 Fake Street, PC1, 1000, 128
345 Fake Street, PC1, 1000, 256

Input (CPU > 750 and RAM > 200)

Result:

234 Fake Street

Details:
123 and 345 are both excluded because at least one row for that address does not meet the Input criteria

==================================================

As extra credit lets say the table has one more column:

Manufacturer - string

I'd like to run the same query but also allow it to use only rows that contain a certain manufacturer.

Example Table Data:

123 Fake Street, Dell PC1, 500, 128
123 Fake Street, IBM, PC1, 1000, 256
234 Fake Street, IBM, PC1, 1000, 256
234 Fake Street, IBM, PC1, 1000, 256
345 Fake Street, Dell, PC1, 1000, 256
345 Fake Street, IBM, PC1, 1000, 256
345 Fake Street, Dell, PC1, 1000, 256

Input (CPU > 750 and RAM > 200 and Manufacturer = Dell)

Result:

234 Fake Street
345 Fake Street

Details:
123 is still excluded because it has at least one Dell that doesn't meet the requirements

345 is suddenly included because while it has PC's that do not meet the requirements, they are not of the type Dell. Or said another way, all the Dell PC's at that address meet the CPU and Ram requirements.

This is a pretty simple thing in theory. I was handed data for 33,000 PC's at 5000 or so addresses. I have requirements for an application that is deployed on an address by address basis (all or nothing) so basically the application can only be deployed to the locations in which all the PC's meet the requirements.

My SQL guy is on vacation and while I can do damage in .Net, I'm not a SQL guy *at all*.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 01:16:59

declare @tbl table (Address varchar(64),PCName varchar(16),Speed int, RAM int)
insert into @tbl select
'123 Fake Street', 'PC1', 500, 128 union all select
'123 Fake Street', 'PC1', 1000, 256 union all select
'234 Fake Street', 'PC1', 1000, 256 union all select
'234 Fake Street', 'PC1', 1000, 256 union all select
'345 Fake Street', 'PC1', 1000, 256 union all select
'345 Fake Street', 'PC1', 1000, 128 union all select
'345 Fake Street', 'PC1', 1000, 256

select distinct t.address,s.pcname,s.speed,s.ram
from (select address,min(speed) as minspeed, min(ram) as minram from @tbl group by address)t
inner join @tbl s on s.address = t.address
where t.minspeed >750 and t.minram > 200

for ist one
and how will u get the 234 for second one manufacture is not there dell

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-28 : 01:18:15

if it will be for second one then u will get that result
check this once
declare @tbl table (Address varchar(64),cname varchar(32),PCName varchar(16),Speed int, RAM int)
insert into @tbl select
'123 Fake Street','Dell', 'PC1', 500, 128 union all select
'123 Fake Street','IBM', 'PC1', 1000, 256 union all select
'234 Fake Street','dell', 'PC1', 1000, 256 union all select
'234 Fake Street','IBM', 'PC1', 1000, 256 union all select
'345 Fake Street','Dell', 'PC1', 1000, 256 union all select
'345 Fake Street','IBM', 'PC1', 1000, 256 union all select
'345 Fake Street','Dell', 'PC1', 1000, 256

select distinct t.address,s.cname,s.pcname,s.speed,s.ram
from (select address,min(speed) as minspeed, min(ram) as minram from @tbl group by address)t
inner join @tbl s on s.address = t.address
where t.minspeed >750 and t.minram > 250 and cname ='dell'

ra.shinde
Posting Yak Master

103 Posts

Posted - 2009-01-28 : 01:21:28

In your second case where Manufacturer = Dell; 234 Fake Street will not appear in result as it is not having dell pc.

DECLARE @table TABLE(Addr1 varchar(50), manufact varchar(50),PCName varchar(50), CPU int, RAM int)

insert into @table
select '123 Fake Street', 'Dell', 'PC1', 500, 128
union all
select '123 Fake Street', 'IBM', 'PC1', 1000, 256
union all
select '234 Fake Street', 'IBM', 'PC1', 1000, 256
union all
select '234 Fake Street', 'IBM', 'PC1', 1000, 256
union all
select '345 Fake Street', 'Dell', 'PC1', 1000, 256
union all
select '345 Fake Street', 'IBM', 'PC1', 1000, 128
union all
select '345 Fake Street', 'Dell', 'PC1', 1000, 256

select Addr1,manufact from @table
where manufact = 'Dell'
group by Addr1,manufact
having MIN(CPU) >= 750 AND MIN(RAM) > 200

Rahul Shinde

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources