Get unique entries from joins - SQL Server Forums

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)

Get unique entries from joins

Author

Topic

tech
Starting Member

32 Posts

Posted - 2011-04-07 : 11:25:29

A little bizzare....

I have a table called Tracks. it has many fields.
I have a table called Genre. Each track is associated with a genre.

unfortunately due to the way the data import was done and time constraints, what has happened is that there are multiple tracks in the track table because each track can be associated with one or more genres - there should have been a linking table but there isnt and we have to live with that for now.

There is also a table called (which is a joining table!) which associates a track to one or more "stages". for example, if you are in an event, then a track can be played or is suited for more than one stage of an event (such as intro, middle, outro, afterparty etc...)

Because I get multiple tracks given back to me in my search criteria, and because the SPROC is doing paging for me - it makes it rather difficult to get unique tracks in that, if a track is associated with multiple genres or multiple stages, I get multiple tracks shown but with the "correct" data if that makes sense?

What I want to do is that if there are multple entries for a track, to "condense" them down into a single record by say appending the name of a genre or the stage just on a single record on a column. Makes sense?

Example:

quote:

Track
Artist: Elton John
TrackName: Candle in the wind
Genre: slow
Stage: intro

Artist: Elton John
TrackName: Candle in the wind
Genre: slow
Stage: middle

Artist: Elton John
TrackName: Candle in the wind
Genre: slow
Stage: outro

Artist: Lady gaga
TrackName: telephone
Genre: pop
Stage: afterparty

Artist: Beyonce
TrackName: Single Ladies
Genre: r'n'b
Stage: middle

When doing a search, it will bring back all the records as above (simple select with inner joins to the genre and stage table)

What I want it to do, is condense in this case, elton john, into just a single record so it is displayed like so:

quote:

Artist: Elton John
TrackName: Candle in the wind
Genre: slow
Stage: intro, middle, outro

Artist: Lady gaga
TrackName: telephone
Genre: pop
Stage: afterparty

Artist: Beyonce
TrackName: Single Ladies
Genre: r'n'b
Stage: middle

is this possible to do?

tech
Starting Member

32 Posts

Posted - 2011-04-10 : 15:43:36

no one? :-(

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-10 : 16:24:49

I suspect that many people on this forum are more used to reading table definitions and SQL statements than reading descriptions to understand a problem.

If you can post the DDL for the tables along with some sample input data and corresponding sample output data, more likely than not, some of them would respond.

Brett's page here describes how to get the DDL for the tables. http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-04-10 : 17:36:06

tech, here is my attempt to understand what you are trying to do and to produce a solution. I may be off-target - if I am, modify the script to create the correct set of input data and then also include what you want the output data to look like. Then, post the whole query back - that will make it easier for someone to copy it to their computer and run it.

--- CREATE TEST TABLE
create table #Tracks 
(
   Artist varchar(100), 
   TrackName varchar(100),
   genre varchar(100), 
   Stage varchar(100)
);

-- INSERT DATA INTO TEST TABLE
insert into #Tracks values ('Elton John','Candle in the Wind','slow','intro');
insert into #Tracks values ('Elton John','Candle in the Wind','slow','middle');
insert into #Tracks values ('Elton John','Candle in the Wind','slow','outro');
insert into #Tracks values ('Lady Gaga','Telphone','pop','afterparty');
insert into #Tracks values ('Beyonce','Single Ladies','r''n''b','middle');

--- QUERY TO GET RESULTS
select
   a.Artist,
   a.TrackName,
   max(a.genre) AGenre,
   b.Stages
from
   #Tracks a
   cross apply
   (
      select stuff((
         select distinct ','+b.Stage as [text()]
         from #Tracks b
         where b.Artist = a.Artist and b.TrackName = a.TrackName
         order by ','+b.Stage
         for xml path(''),type
         ).value('.','varchar(max)'),1,1,'') Stages
   ) b
group by
   a.Artist,
   a.TrackName,
   b.Stages;

-- CLEANUP
drop table #Tracks;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources