SQL query. - 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

General SQL Server Forums

New to SQL Server Programming

SQL query.

Author

Topic

timeout2575
Starting Member

4 Posts

Posted - 2012-06-17 : 18:32:00

Hello,

I am new to this page and I hope that my post is in the right section. Anyway, I need some help with a query.

This is the table:

artist_relation

artist0 artist1 type founder begin_year end_year

285850 1352 member of 1974 1980
290121 1352 member of Y 1973
290121 316942 sibling

this is the second table that I need for my query.

artist

id name begin_year end_year type origin gender
938 Clapton,Eric 1945 Person 221 M
1352 AC/DC 1973 Group 13
285850 Scott, Bon 1946 1980 Person 221 M

Here comes the query:

There are some bands, that have changed the bandname, but the band members stayed the same. Give out the bandnames (as pairs) of bands that have changed the name and apart from that stayed the same. (Apparently it doesnt play a role, when they have played for the band)

My idea:

Look up the "artist0"s that make up "artist1" and than search if that exact combination is found in another "artist1"... but that is far beyond my skills...

Any ideas?

Thanks, Tobias

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-17 : 22:00:42

so what should your output? also your sample data is not properly aligned so its a bit confusing. can you post it within code tags?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timeout2575
Starting Member

4 Posts

Posted - 2012-06-17 : 22:28:08

Table: Artist

___id___ / ___name___ /_begin_year_/_end_year_/__type__/_origin_/gender

__938___ /Clapton,Eric/___1945____/___________/_Person_/__221___/__M___
__1352__ /__AC/DC___/___1973____/___________/_Group__/___13___/______
_285850_ /Scott,Bon___/___1946____/__1980_____/_Person_/__221___/__M___

Table: Artist_Relation

artist0_/artist1_/___type____/founder/begin_year/end_year/
285850/1352__/member of__/______/__1974__/__1980____
290121/1352__/member of_/___Y___/__1973__/________
290121/316942/_sibling___/_______/________/_____

Hopefully this is a wee bit better. Sorry about that.

Well like I said, a couple of ARTIST0 make up ARTIST1 and now we want to know, when the same set of ARTIST0 make up a different ARTIST1.

This is a stupid example. 4 ARTIST0 make up the Band 'ABBA', they all have numbers 33544, 240614, 128193. 240608 and they make up ARTIST1 in this case ABBA and ABBA has the number 910. Now I want to know if, that set of ARTIST0 make up a different ARTIST1 and if this is the case, than I need an output that lists them as pairs. In this case the band name ABBA next to the "new" band name... Is that explanation better?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 00:30:28

[code]
select m.artist1_,m.relatedartist
from
(
select ar1.artist1_,ar2.artist1_ as relatedartist,count(distinct artist0_) as cnt
from Artist_Relation ar1
inner join Artist_Relation ar2
on ar2.artist0_ = ar1.artist0_
and ar2.artist1_ <> ar1.artist1_
group by ar1.artist1_,ar2.artist1_
)m
join (select artist1_ ,count(distinct artist0_) as cnt
from Artist_Relation
group by artist1_ )ar
on ar.artist1_ = m.artist1_
and ar.cnt = m.cnt
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

timeout2575
Starting Member

4 Posts

Posted - 2012-06-18 : 08:55:47

Thank you very much for helping me right away!!!

There are a few more questions related to that query.

Postgres posted an error message. The program issued the following message:

ERROR: column reference "artist0" is ambiguous
LINE 1: ...ist1, ar2.artist1 as relateartist, count(distinct artist0) a...
^
I "fixed" that by changing it to ar1.artist0, BUT I am not sure if that "fix" is actually part of the following problem. After that change, the query gives out this result.

This is the query on my system:

mosv=# SELECT m.artist1, m.relatedartist FROM (SELECT ar1.artist1, ar2.artist1 AS relatedartist, COUNT(DISTINCT ar1.artist0) AS cnt FROM artist_relation ar1 INNER JOIN artist_relation ar2 ON ar2.artist0 = ar1.artist0 AND ar2.artist1 <> ar1.artist1 GROUP BY ar1.artist1 , ar2.artist1) m JOIN (SELECT artist1, COUNT(DISTINCT artist0) AS cnt FROM artist_relation GROUP BY artist1) ar ON ar.artist1 = m.artist1 AND ar.cnt = m.cnt;

This is the query result on my system:

artist1 | relatedartist
---------+---------------
4 | 115885
44 | 35513
45 | 57
45 | 1070
48 | 123675
48 | 762446
66 | 232997
141 | 51630
213 | 56878
213 | 81386
227 | 47537
318 | 317
318 | 12734
318 | 108711
320 | 147783
375 | 743338
418 | 2361
418 | 54346

The list goes on.

Here is the problem with it:

Let s have a look at artist1 with the number 45.

mosv=# select * from artist_relation where artist1 = '45';
artist0 | artist1 | type | founder | begin_year | end_year
---------+---------+----------------+---------+------------+----------
280675 | 45 | member of band | | |
(1 row)

mosv=# select * from artist_relation where artist1 = '57'; artist0 | artist1 | type | founder | begin_year | end_year
---------+---------+----------------+---------+------------+----------
280676 | 57 | member of band | | 1994 | 2006
280675 | 57 | member of band | | 1994 | 2006
280677 | 57 | member of band | | 1996 | 2006
(3 rows)

My understanding is, that artist1 (band) = 45 had one artist0 (one bandmember) with the number 280675. If you have a look at the related band 57, you find him there as well, but with TWO other artist0 and the query should only give out the artist1 where the band members stayed the same.

Not sure, where the error is. I am pretty sure I copied the query correctly on to my system or maybe it is related to Postgres or maybe one or two lines need to be changed on the query.

By the way, the right results are among them, if you check artist1 = 45 to related artist 57, this is solo singer just changed her name, but like I said, at one point that singer was part of a group and that result should not have been given out.

Maybe someone can help me.

Thanks in advance.

Tobias

timeout2575
Starting Member

4 Posts

Posted - 2012-06-18 : 08:58:46

One more thing, why is it not giving out the names? Arent the last couple of lines in this query dealing with that issue?

Cheers,
Tobi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-18 : 12:00:39

you're in wrong forum!
This is MS SQL Server forum and we provide ,ostly SQL server specific solutions. Please post in relevant forums like www.dbforums.com if you're using Postgres

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources