sql select distinct except when in table

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

Transact-SQL (2000)

sql select distinct except when in table

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-28 : 09:27:56

I can do this in a stored proc but i'm not sure how to do this.
I have a table of hits.
it has a id, mydate and ipaddress

now I want to select all distinct ip addresses according date but the catch is I have a table of ipaddresses that it should not count as distinct but all ip addresses.

Please advise?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-12-28 : 09:41:08

Something like this?

Select distinct t1.mydate, t2.ipaddress
from tbl t1 Left Join ipaddresses t2
on t1.ipaddress = t2.ipaddress
where t2.ipaddress Is Null

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

JoeNak
Constraint Violating Yak Guru

292 Posts

Posted - 2006-12-28 : 09:45:23

How about this...

Select
h.ipaddress
, DateAdd(dd, 0, DateDiff(dd, 0, h.mydate)) As mydate
From
YourHitTable h
Inner Join
YourIPTable i On h.ipaddress = i.ipaddress
Union
Select Distinct
h.ipaddress
, DateAdd(dd, 0, DateDiff(dd, 0, h.mydate)) As mydate
From
YourHitTable h
Left Outer Join
YourIPTable i On h.ipaddress = i.ipaddress
Where
i.ipaddress Is Null

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2006-12-28 : 09:50:52

truth is i don't know --should those work.

the trick is it should only look for distinct if the ip is not in the excludeip table

so if there are 1000 hits - it looks at the ips and takes the distinct ones but if the ip appears in the exclude ip table then it counts all those records and not just teh distinct one

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2006-12-28 : 15:18:18

[code]
select distinct t.ipAddress
from yourTable t
left join
excludeTable x
on t.ipAddress = x.ipAddress
where t.date = someDate
and x.ipAddress is null

UNION

select t.ipAddress
from yourTable t
join excludeTable x
on x.ipAddress = t.ipAddress
where t.date = someDate
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources