Should be a simple query but...

• rickratayczak

  Newbie

  Points: 1

  More actions

  November 20, 2002 at 2:01 am

  #79266

  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

• NPeeters

  SSChampion

  Points: 12503

  More actions

  November 20, 2002 at 2:35 am

  #441802

  Something like this should work :

  
  
  SELECT
  
     IsNULL(M.Title, U.Title),
  
     IsNull(M.Artist, U.Artist),
  
     M.DiscNo,
  
     M.TrackNo
  
  FROM    Master M
  
       LEFT OUTER JOIN UserMaster U
  
          ON     M.DiscNo  = U.DiscNo
  
             AND M.TrackNo = U.TrackNo
  
  

  You will have multiple records when there are more than one user specifying additional information, for a single discno-trackno combination.