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.
| Author |
Topic |
|
sonjan
Starting Member
22 Posts |
 Posted - 2011-03-21 : 02:29:53
|
| HiIs there a smart way of displaying unique records if criteria matches two values? I want to display locations that have values in both 'ROAD' and 'PARK' fields.Using an inner join but not sure whether to use a case statement, sub query or group by to eliminate duplicate results.ThanksSonja |
|
|
aoriju
Posting Yak Master
156 Posts |
Posted - 2011-03-21 : 02:51:10
|
| You can use Group By and Having |
 |
|
|
lionofdezert
Aged Yak Warrior
885 Posts |
Posted - 2011-03-21 : 07:36:16
|
| SELECT DISTINCT [CommaSeparatedColumnsName]FROM [yourtable]WHERE location IN ('ROAD','PART')--------------------------http://connectsql.blogspot.com/ |
|
|
|
sonjan
Starting Member
22 Posts |
Posted - 2011-03-21 : 19:39:30
|
| Thanks, tried the select distinct. The query involves two tables. The criteria of road and park is in the second table, and the select statement is from the first table (using inner join). Problem is results display twice - once for road, again for park. I was hoping there was a solution to display location once. Following example of script:select distinct locations.location, locations.description, lochierarchy.systemidfrom locations with (nolock)inner join lochierarchy with (nolock) on lochierarchy.location = locations.location and lochierarchy.orgid = locations.orgid and lochierarchy.siteid = locations.siteidwhere lochierarchy.systemid in ('road','park')and locations.orgid = 'rsc'and locations.siteid = 'ops'Example of result:1001 REDLAND CITY PARK1001 REDLAND CITY ROADThanksSonja |
|
|
|
sonjan
Starting Member
22 Posts |
Posted - 2011-03-23 : 01:44:09
|
| Thank you - used the group by and having clause with count(*) > 1 and it worked! Woo hoo! |
|
|
|
|
|
|
Display distinct records