Eggybread Eggybread - 2 months ago 9
Vb.net Question

Selecting data from another SQL table to add to string text

the following code selects 3 random items(photos) with prices and passes it to my website as a string..

SELECT TOP 3
thisweeksDate
,'<br/><a href="catalog/images/'
+ [filename] +
'" class="nyroModal" rel="gal" title="'
+ [price] +
'" ><img src="catalog/images/thumbnails/'
+ [filename] +
+ '" /></a>'
+ [price]
as strText
,fileID
FROM [OCBUser].[tblItems]
WHERE thisweeksDate = @thisweeksDate and price <> ''
ORDER BY NEWID()


The above works perfectly but I don't know enough SQL yet to do the following..

I need it to also select

friendlyOrderID from [OCBUser].[tblOrders]
where [OCBUser].[tblItems].accountID = [OCBUser].[tblOrders].accountID


and add it to the strText with a hyperlink prefix so it can be clicked on. So it would need to inclide something like...

<a href="www.mysite.com/' + [friendlyorderID] + '"


Any ideas ?

Many Thanks.

Answer

use a join

  SELECT TOP 3 thisweeksDate, '<br/><a href="catalog/images/' + [filename] + '"    class="nyroModal" rel="gal" title="' + [price] + '" ><img   src="catalog/images/thumbnails/' + [filename] + '" /></a>' + [price] as strText, fileID
  ,'<a href="www.mysite.com/' + [friendlyorderID] + '">link</a>'

  FROM [OCBUser].[tblItems]
  JOIN [OCBUser].[tblOrders] on [OCBUser].[tblItems].accountID = [OCBUser].[tblOrders].accountID 
  WHERE thisweeksDate = @thisweeksDate and price <> ''
  ORDER BY NEWID()
Comments