Select Anoter column if populated? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

Select Anoter column if populated?

Author

Topic

chrispy
Posting Yak Master

107 Posts

Posted - 2006-03-15 : 09:03:06

Hi all,

I am working on populating a DB of musical CDs with lyrics that I am scraping from a website (FYI Original program that created the DB is no longer supported and the embedded scraper is failing).

For the sake of argument I have two tables:
Albums
ID
CDJID (ID for each CD)
Artist
Title (Album Title)
Tracks
ID
CDJID (relationship to the CD it is from in Albums)
Artist
Title (Song title)

Now the problem lies with the Artist, as they are in each Table. The Albums.Artist (actually it always has an entry) could be populated with "Various Artist" as in a movie sound track. If this is the case then the Tracks.Artist column would be populated with the actual artist name. If Artist.Artist was say "Eric Clapton", then the Tracks.Artist column would be empty.

Problem is when I go to scrape the lyrics. For example "Money for Nothing by Various Artist" is unknown. I do have the select statement below working, but it only pulls the Album.Artist. What I need is if the Tracks.Artist is populated to have that instead. So if there is an Artist name in Tracks.Artist then get that, if not then use the Albums.Artist. Hopefully that makes sense.

Any help would be appreciated.

Thanks in advance,
Chris

SELECT top 25000 Tracks.CDJID, Tracks.ID, Tracks.Title as title2, Tracks.Lyrics as lyrics2, Albums.Artist FROM Tracks INNER JOIN Albums ON (Tracks.CDJID = Albums.CDJID)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2006-03-15 : 09:12:11

Will this work for you?


SELECT top 25000 
	Tracks.CDJID, 
	Tracks.ID, 
	Tracks.Title as title2, 
	Tracks.Lyrics as lyrics2, 
	COALESCE(Tracks.Artist, Albums.Artist) AS Artist
FROM 
	Tracks 
INNER JOIN 
	Albums 
	ON (Tracks.CDJID = Albums.CDJID)

chrispy
Posting Yak Master

107 Posts

Posted - 2006-03-15 : 09:30:55

Thanks!

But for sure, I am about to have mud on my face.(for posting in the worng place as well) as I forgot that this was in M$ Access, so the COALESCE is out.

So how to do it without COALESCE?

Thanks,
Chris

jhermiz

3564 Posts

Posted - 2006-03-15 : 09:47:06

quote:
Originally posted by chrispy

Thanks!

But for sure, I am about to have mud on my face.(for posting in the worng place as well) as I forgot that this was in M$ Access, so the COALESCE is out.

So how to do it without COALESCE?

Thanks,
Chris

IIRC,

Access uses IIF ?

So

SELECT top 25000
Tracks.CDJID,
Tracks.ID,
Tracks.Title as title2,
Tracks.Lyrics as lyrics2,
IIF(Tracks.Artist IS NULL, Albums.Artist, Tracks.Artist) AS Artist
FROM
Tracks
INNER JOIN
Albums
ON (Tracks.CDJID = Albums.CDJID)

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

chrispy
Posting Yak Master

107 Posts

Posted - 2006-03-15 : 09:56:21

Yep the IIF() worked perfectly. I was Just learning the hard way that NZ() was not supported in ADO when I figured I would do the same with IIF().

Either way it worked perfectly.

Thanks to all for the help,
Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources