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?

David Kaye (sfdavidkaye2 (AT) yahoo (DOT) com) writes:
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 (AT) sommarskog (DOT) 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

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

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

INSTR? Is this Access or SQL Server? Perhaps Access with a SQL Server
backend?
Fix the real problem and the solution will be a simple outer join.