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
Posted
Updated 15-Jan-13 23:52pm
v2

Hello


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

SQL
SELECT f.productId,f.productTypeId,CatalogueId,imageId,imagepath 
 FROM finallevelcatalogue f
INNER JOIN imagemaster ON f.productId = imagemaster.productId
INNER JOIN
(
   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
   
v2
Try the below query to get the first image for each ProductID
SQL
SELECT * FROM finallevelcatalogue FLC
INNER JOIN imagemaster IM ON FLC.productId = IM.productId
INNER JOIN
(
   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