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

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

Linking Tables in A Particular Order

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  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

#1000 — Linking Tables in A Particular Order

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?

[toc] | [next] | [standalone]

#1001

David Kaye (sfdavidkaye2@yahoo.com) writes:
> 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)
> 

Since the syntax you use is not legal syntax in SQL Server, I conclude 
that you are using another product. I think you are better off asking 
in a forum devoted to your product, as what is a good solution in SQL
Server may not work in your environment.

I don't understand what you mean with "But the match is determined by which 
record the SQL pulls first", since in SQL Server at least, you will get 
all rows that match the conditions. But if you want rows in any certain
order, you need to use an ORDER BY clause, for instance 

   ORDER BY len(songs.artist) DESC

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#1002

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. 

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web