Building lists in sql

I need to build a list from a couple of tables and I can't seem to work out how to build it.

I have a table with bike models and a products table:

bikes

modelno, franchise, description, year,profile
"1","kawasaki","ninja","2009","1-2009"
"2","honda","cbr600","2006","2-2006"

products

productid, category, profile,
"1","carburettor",",1-2009,",
"2","Generic Gear Lever",",1-2009,2-2006,"

the profile field holds a comma delimited list of all the vehicles the part fits.

i can get a list to work for one vehicle by using:

 SELECT
   (SELECT Franchise + ' ' + 
           description + ' ' + 
           year as model from bikes where profile = @model + '-' + 
           @year
   ) 
 as model, catalogueref, tier2
 FROM products
 WHERE profile LIKE '%,' + @model + '-' + @year + ',%'`

which gives me:

bike, lever, sprocket, e开发者_如何学JAVAtc...
kawasaki 250 2009, 123456,234567, etc...

However, I need to perform this function on around 8000 models, and would rather not have to do each one individually. Is there any way I could make it take a list of profiles?

Your requirements are not entirely clear to me since you don't illustrate exactly what the numbers at the end of the record in your desired result set stand for.

However, if you want to execute the example SELECT across all products in your database, you should be able to join the two tables.

SELECT B.Franchise + ' ' + B.description + ' ' + B.year AS model, 
       P.catalogueref, P.tier2
FROM bikes B INNER JOIN products P
ON P.profile LIKE '%,' + B.modelno + '-' + B.year + ',%'`

If you want to filter this for certain profiles you can add (for instance)

WHERE B.Profile IN (. . .)

I'd look at seeing if you can project a view of the actual data in each of your columns for both tables. Once you have the view, you can then write a join query. It won't be fast, but it will break your problem down into manageable chunks.