Click here to Skip to main content
15,073,511 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
hello all,
i have a problem related to joining three tables,
i have three tables -----
1. " sublevelcatalogue" in which i have column 'productTypeId','CatalogueId',
2. "finallevelcatalogue" in which i have column 'productTypeId' , 'productId' , 'productname'

3. "imagemaster" in which i have column 'imageId','imagepath','productId'

there are multiple image on one 'productId' and i want to select only first image for all product... I AM USING SQL DATABASE
how to join these tables,,,,give suggetions....thnx in advance
Updated 15-Jan-13 23:52pm


You Want to Result Like That:-
productId,productTypeId,CatalogueId,imageId,imagepath So Simply You Can Use

SELECT f.productId,f.productTypeId,CatalogueId,imageId,imagepath 
 FROM finallevelcatalogue f
INNER JOIN imagemaster ON f.productId = imagemaster.productId
   SELECT Top 1 ImageID, imagepath ,productId FROM imagemaster where productId=f.productId 
) tab ON f.productId= tab.productId

It Will Give You ONe Recore Associated To PRoduct Id I Hope Your Problem Will Solve IF not Give me Your Review
Try the below query to get the first image for each ProductID
SELECT * FROM finallevelcatalogue FLC
INNER JOIN imagemaster IM ON FLC.productId = IM.productId
   SELECT MIN(ImageID) AS ImageID, productId FROM imagemaster
   GROUP BY productId  
) T ON IM.ImageID = T.ImageID

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900