SQL Query Help Required

[thainjm]

Hi,

I'm working on a music discography site. The test version of which can be seen here...

http://62.137.131.204/lbdb/search.asp

The best way to
see what I'm trying to accomplish is by searching for 'get loose' as a title on the search page (this is the only record in the database that is so far complete).

My SQL server database has 2 tables; LBPLATTERS & LBTRACKS. The LBPLATTERS table holds information that I consider to 'belong' to a physical piece of vinyl (such as artist, title, urls for sleeve and label scans, label name, country of issue, format (12" or LP) etc.

The LBTRACKS table contains info which I consider 'belongs' to a track.Such as ARTIST, TITLE, WRITER, PRODUCER,TRACK LENGTH etc.

Both tables use a column called PLID (platterid) to link tracks to whichever record they belong to and vice versa. I've not actually done anything in SQL to link these tables. (I don't know how to, or indeed if I need to).

It all works quite nicely at the moment, except that when you search for a TITLE on the search page, the recordset used is one formed from the LBPLATTERS table. (i.e. only platter titles are found, so you can't search for an LP track title). I want to be able to search both
tables for titles, and then display the parent record (i.e. the vinyl's LBPLATTERS record).

Does anybody know the SQL to do this? My current SQL is:

SELECT *
FROM dbo.lbplatters
WHERE artist LIKE '%MMColParam1%' and title LIKE '%MMColParam2%' and
label like '%MMColParam3' and year like '%MMColParam4%' and format
like '%MMColParam5%' and country like '%MMColParam6%'

I'm using Macromedia Ultradev as my development tool.

Thanks,

Jason.
SQL Novice.

 

[Vancouverite]

Try this:

SELECT Distinct p.*
FROM dbo.lbplatters p
INNER JOIN dbo.lbtracks t on p.plid = t.plid
WHERE p.artist LIKE '%MMColParam1%' and (p.title LIKE '%MMColParam2%' OR t.title LIKE '%MMColParam2%') and p.label like '%MMColParam3' and p.year like '%MMColParam4%' and p.format like '%MMColParam5%' and p.country like '%MMColParam6%')

 

[Andel]

It would be nice if you could post sample data on each table and how you want the result of your query to look like.
Andel
andel@barroga.net

http://andyb.8m.com

 

[thainjm]

Vancouverite,

Thanks for the answer. It's almost there, but the brackets don't seem to match up and I can't seem to get them right.

It if search for track "confusion" it now returns the fileds for the LP which contains that track. However, it's not returning the entries for the 2 12" singles of that name.

Also if I now leave all search fields blank (this should display EVERY (19) lbplatter record, its only returning 2 entries (those that have a track which is also the same as their platter name).

Any ideas?

Thanks,

Jason.


Here's how the data is set up:

LBPLATTERS table (commas separate columns)
plid, artist, title, label, format, year
1, aleem, get loose, nia, 12", 1984
17, aleem, casually formal, atlantic, LP, 1985
18, aleem, confusion, nia, 12", 1985
19, aleem, confusion, atlantic, 12", 1985

LBTRACKS table (commas separate columns)
plid, artist, placement, title, writer, length
1, aleem, a, get loose, burgess, 6:58
1, aleem, b, get loose (dub), burgess, 7:02
17, aleem, a1, think, burgess, x:xx
17, aleem, a2, confusion, burgess, x:xx
17, aleem, b1, dance, burgess, x:xx
17, aleem, b2, stay, burgess, x:xx
18, aleem, a, confusion, burgess, x:xx
18, aleem, b, confusion (dub), burgess, x:xx
19, aleem, a, confusion, burgess, x:xx
19, aleem, b, confusion (dub), burgess, x:xx

 

[thainjm]

Aaaah, hang on....

I've removed ALL the brackets from your SQL and this now works:

SELECT Distinct p.*
FROM dbo.lbplatters p INNER JOIN dbo.lbtracks t on p.plid = t.plid WHERE p.artist LIKE '%MMColParam1%' and p.title LIKE '%MMColParam2%' OR t.title LIKE '%MMColParam2%' and p.label like '%MMColParam3' and p.year like '%MMColParam4%' and p.format like '%MMColParam5%' and p.country like '%MMColParam6%'

...except, If I leave all search fields blank, I don't get ALL lbplatters records displayed like I used to, I now only get records with plid, 1, 17, 18 & 19 displaying (i.e. ONLY records where lbplatters.title EQUALS lbtracks.title

Help!

 

[thainjm]

OK, I think I got it. This is what I need...

SELECT Distinct p.*
FROM dbo.lbplatters p INNER JOIN dbo.lbtracks t on p.plid = t.plid WHERE p.artist LIKE '%MMColParam1%' and (p.title LIKE '%MMColParam2%' OR t.title LIKE '%MMColParam2%') and p.label like '%MMColParam3' and p.year like '%MMColParam4%' and p.format like '%MMColParam5%' and p.country like '%MMColParam6%'

The reason it wasn't returning some lbplatters records is because they didn't have any associated entries in the lbtracks table. I guess this throws out the join.

Thanks for the help. Wouldn't have got there on my own.