Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1000

Linking Tables in A Particular Order

Show all headers | View raw

What I need to do is create a SQL that joins content from one table to 
another but in a certain order.  I have a table of songs which includes both 
the artist and title in the title field, and a table of songlookups which 
includes only the artist in the artist field.  What I want to do is find out 
which songs are by artists that match artists already in the songlookup 
table.

But the problem is that I need to link the songlookup table in a specific 
order, namely reverse order by length of artist field.  That way I can try 
finding a match with the band "Guess Who" before finding a match with the 
"Who".  Otherwise, it's more likely I'll match the song with the wrong band.

Here's the original SQL:

SELECT songs.artist,songs.title FROM songs,songlookup WHERE songs.artist 
LIKE songlookup.artist & "%"

or it could be stated as:

SELECT songs.artist,songs.title FROM songs,songlookup WHERE 
INSTR(songs.artist,songlookup.artist)

But the match is determined by which record the SQL pulls first, and it 
appears to relate to which data just happens to be accessed first.  I want 
it to be able to pull the longest songlookup.artist first.

Ideas anyone?

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Linking Tables in A Particular Order "David Kaye" <sfdavidkaye2@yahoo.com> - 2012-04-20 04:36 -0700
  Re: Linking Tables in A Particular Order Erland Sommarskog <esquel@sommarskog.se> - 2012-04-20 14:35 +0200
  Re: Linking Tables in A Particular Order "Bob Barrows" <reb01501@NOSPAMyahoo.com> - 2012-04-20 10:21 -0400

csiph-web