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

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

Re: Linking Tables in A Particular Order

Show all headers | View raw

David Kaye wrote:
> 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,

My first priority would be to fix this horrendous design error before 
continuing to do anything else. Assuming you are dealing with data imported 
from an external source, you need to generate separate columns for artist 
and song title after importing the data. Details depend on how the data in 
that "title" column is delimited

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

A task that would be supremely easy if you implemented my suggestion, 
wouldn't it?

>
> 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.
<snip>
>
> SELECT songs.artist,songs.title FROM songs,songlookup WHERE
> INSTR(songs.artist,songlookup.artist)

INSTR? Is this Access or SQL Server? Perhaps Access with a SQL Server 
backend?
>
> 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?

Fix the real problem and the solution will be a simple outer join. 

Back to comp.databases.ms-sqlserver | Previous | Next — Previous 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