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 |
|
Iconic
Starting Member
1 Post |
 Posted - 2010-03-23 : 09:56:18
|
| Sounds simple enough...I have a table of media Stations (TV, Radio, etc) which contains a field called CALL_LETTER and one called BAND_TYPE (TV, Radio, etc).I have ANOTHER table called Vendor, which has the name of who's paying the bill.In some cases, the Vendor.VENDOR_NAME is the Station.CALL_LETTER and some other cases, the VENDOR_NAME is the CALL_LETTER and the BAND_TYPE. I did my WHERE statement like this:((v.VENDOR_NAME = s.CALL_LETTERS + '-' + s.BAND_CODE) OR (v.VENDOR_NAME = s.CALL_LETTERS))That solved 90% of my problems. However, in some RARE cases, I have duplicates where the only difference is the VENDOR_NAME (one is with just CALL_LETTER and the other is CALL_LETTER + '-' + BAND_TYPESo instead of getting x number of records, I'm getting x*2 number of records.*OTHER FIELDS are identical for both records------------------------------------------------------------------ CALL_LETTER - BAND_TYPE - VENDOR_NAME - OTHER FIELDS* ------------------------------------------------------------------- WPIX - TV - WPIX-TV - -- WPIX - TV - WPIX - -- WATL - TV - WATL-TV - -- WKBL - TV - WKBL - ------------------------------------------------------------------How do I make it so that only ONE of the WPIX stations show but not both, while keeping the WATL and the WKBL as well??Does that make sense?Thanks |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-23 : 10:19:10
|
Accept the "duplicates", have a look at them and then correct your data in the Vendor table.That should be the best way.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-23 : 10:21:21
|
| [code]SELECT required columns....FROM(SELECT yourcurrentcolumns...,ROW_NUMBER() OVER (PARTITION BY CALL_LETTER,BAND_TYPE ORDER BY LEN(VENDOR_NAME) DESC) AS SeqFROM....rest of query)tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-03-23 : 10:29:49
|
Yes you can use Visakh's solution and go on with that messy data 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
DBA in the making
Aged Yak Warrior
638 Posts |
Posted - 2010-03-23 : 10:46:38
|
| How many vendors are there for each station? If each station only has a single vendor, then there should be a Vendor column in the station table which is a foreign key, related to the primary key of the station table. Using the vendor name to determine which station records are related to which vendor records is a bad idea. Using key columns makes much more sense.There are 10 types of people in the world, those that understand binary, and those that don't. |
|
|
|
|
|
|
|
|
Either Or But Not Both