Should be a simple query but...

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 2000 Forums

Transact-SQL (2000)

Should be a simple query but...

Author

Topic

rickratayczak
Starting Member

5 Posts

Posted - 2002-11-20 : 03:56:18

I have a simple music database. When they add a song to the user master table,
it pulls it from the master table. They then can edit the data from the user
master table. I've been stumped on how to join this for days.

What my question is, and what I am stumped on is: How can I create a view where
I spit out "artist, title, discno, trackno" and replace the artist and title
from the master table from the user master if it is not null and does exist in
the user master table.

Below is both table schemas. The @ denotes the primary key of the table.


/* *** Master Table *** */
_ Title
_ Artist
_ Description (Manufacturer)
@ DiscNo
@ TrackNo

/* *** User Master Table *** */
_ Title
_ Artist
_ Description (Manufacturer)
@ DiscNo
@ TrackNo
@ Email
@ SongbookName

Thanks!

Rick Ratayczak

mr_mist
Grunnio

1870 Posts

Posted - 2002-11-20 : 04:02:44

Not sure if I totally understood your requirement but try..

SELECT
CASE when umt.artist is not null then umt.artist else mt.artist end as artist,
CASE when umt.title is not null then umt.title else mt.title end as title,
discno,
trackno
FROM [master table] mt
left outer join [user master table] umt on
umt.trackno = mt.trackno and
umt.discno = mt.discno

Edited by - mr_mist on 11/20/2002 04:03:12

rickratayczak
Starting Member

5 Posts

Posted - 2002-11-20 : 04:16:32

That is exactly what I was looking for. However, my join was all screwed up, and I did not know how to case the artist-title fields.

Thank you very much!

Rick

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources