I have a Users table where i have username, Country and IP and i also have a scalar function returning the name of a country from the IP. The function fn_GetCountryName works very well.
I want to compare and select all the Users name, country and IP where the function returning the country name are not the same.
I try that but is not working :
SELECT username, Country, Ip, (dbo.fn_GetCountryName(Ip)) as CountryTemp
FROM Users
WHERE Countrytemp != Country
SELECT username, Country, Ip, (dbo.fn_GetCountryName(Ip)) as CountryTemp
FROM Users
WHERE (dbo.fn_GetCountryName(Ip)) <> Country
u can optimize ur query in some ways.
look u call the function fn_GetCountryName in query that act as subquery and call this as total as row numbers + call for where clause as total as row numbers.
u can optimize this.
a query that stores Countries name result in a temporary table
Mattw67
Member
355 Points
491 Posts
SELECT users Countries that is no matching
Apr 29, 2012 08:06 PM|LINK
Hi,
I have a Users table where i have username, Country and IP and i also have a scalar function returning the name of a country from the IP. The function fn_GetCountryName works very well.
I want to compare and select all the Users name, country and IP where the function returning the country name are not the same.
I try that but is not working :
SELECT username, Country, Ip, (dbo.fn_GetCountryName(Ip)) as CountryTemp
FROM Users
WHERE Countrytemp != Country
What im missing here?
Thanks
gimimex
Participant
1052 Points
157 Posts
Re: SELECT users Countries that is no matching
Apr 29, 2012 08:46 PM|LINK
Hi,
Try:
Hope this helps.
patuary
Member
425 Points
143 Posts
Re: SELECT users Countries that is no matching
Apr 30, 2012 03:54 AM|LINK
SELECT username, Country, Ip, (dbo.fn_GetCountryName(Ip)) as CountryTemp
FROM Users
WHERE (dbo.fn_GetCountryName(Ip)) <> Country
u can optimize ur query in some ways.
look u call the function fn_GetCountryName in query that act as subquery and call this as total as row numbers + call for where clause as total as row numbers.
u can optimize this.
a query that stores Countries name result in a temporary table
then use that table by joining someway.
sandy060583
Star
8728 Points
1626 Posts
Re: SELECT users Countries that is no matching
Apr 30, 2012 12:06 PM|LINK
Hi , Here are the steps to achieve the same :
1) Create TempTable with following columns (IP , CountryName)
2) Fill the TempTable with following query
Insert Into TempTable(IP, CountryName)
SELECT IP , dbo.fn_GetCountryName(Ip)
FROM Users
3) Use the above table in join and fetch rows needed
SELECT u1.username, u1.country , u1.ip , u2.CountryName
FROM Users u1,
TempTable u2
WHERE
u1.ip = u2.ip AND
u1.Country <> u2.CountryName
Hope this will help !!!
Ramani Sandeep (My Blog)
(MCTS, MCC-2011)