Click here to Skip to main content
14,920,592 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi!

My question is regarding to join table

I have a Product and Image table. ProductId is a foreign key in Image table.
I mean to say that Image table contain multiple image for single product.

I need such query that return all the data of product and single image of every product to display in homepage.

Product Field are following
==============================
ProductId
ProductName
Detail
CountryId
StateId
CityId
CategoryId
CompanyId
StartingDate
ClosingDate
BidPrice
BidIncrement
Model
RegisterNo
IsAvailable
Status


Following are Image table field
================================
ImageId, ProductId, ProductImage, IsActive
Posted
Comments
pradiprenushe 29-Aug-12 1:04am
   
Your question itself tells problem.
"ProductId is a foreign key in Image table.
I mean to say that Image table contain multiple image for single product"
Then how can you find unique image?
ravi sharma11 29-Aug-12 1:13am
   
I want only first image from Image table

You have multiple images for single product and is that fine if you display any images?

Then use Top 1 image from imagetable

SQL
select * from product p
inner join image i on p.ProductId=i.productid
where imageid in (select top 1 imageid from image where productid=p.productid)
   
Comments
ravi sharma11 29-Aug-12 1:28am
   
yes, I just want to display only single image
Santhosh Kumar Jayaraman 29-Aug-12 1:29am
   
Then use this.It will give one record
Nithin Sundar 29-Aug-12 1:44am
   
Simple. 5.
Hello friend i have put some dummy data and make one query to select the first images from Image table, i thought it might helps you.


SQL
CREATE TABLE PRODUCT(
    ProductId INT IDENTITY(1,1)
    ,ProductName NVARCHAR(MAX)
    ,Detail NVARCHAR(MAX)
    ,CountryId INT
    ,StateId INT
    ,CityId INT
    ,CategoryId INT
    ,CompanyId INT
    ,StartingDate DATETIME
    ,ClosingDate DATETIME
    ,BidPrice FLOAT
    ,BidIncrement INT
    ,Model VARCHAR(50)
    ,RegisterNo VARCHAR(50)
    ,IsAvailable BIT
    ,Status BIT
)


CREATE TABLE IMAGES(
    ImageId INT IDENTITY(1,1)
    ,ProductId INT
    ,ProductImage VARCHAR(50)
    ,IsActive BIT
)



INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod1','Test Prod 1',1000)
INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod2','Test Prod 2',1000)
INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod3','Test Prod 3',1000)
INSERT INTO PRODUCT(ProductName,Detail,BidPrice) VALUES('Prod4','Test Prod 4',1000)


INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(1,'Prod1 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(1,'Prod1 Image 2',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(1,'Prod1 Image 3',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(2,'Prod2 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(2,'Prod2 Image 2',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(3,'Prod3 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(4,'Prod4 Image 1',1)
INSERT INTO IMAGES(ProductId,ProductImage,IsActive) VALUES(4,'Prod4 Image 2',1)



SELECT * FROM PRODUCT
SELECT * FROM IMAGES

SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY ProductImage ) AS RCNT FROM IMAGES) AS DATA
WHERE DATA.RCNT = 1


and the OUT PUT is like this

ImageID ProductID ProductIMage IsActive RCNT
1	1	Prod1 Image 1	1	1
4	2	Prod2 Image 1	1	1
6	3	Prod3 Image 1	1	1
7	4	Prod4 Image 1	1	1



And with Product Table And Image Table Join

SQL
SELECT PRODUCT.ProductId,PRODUCT.ProductName,PRODUCT.Detail ,PRImage.ProductImage FROM PRODUCT
INNER JOIN (
SELECT * FROM (
SELECT *,ROW_NUMBER() OVER(PARTITION BY PRODUCTID ORDER BY ProductImage ) AS RCNT FROM IMAGES) AS DATA
WHERE DATA.RCNT = 1) AS PRImage ON PRImage.PRODUCTID = PRODUCT.PRODUCTID


OUT PUT
ProductId ProductName     Detail        ProductImage
1	   Prod1	Test Prod 1	Prod1 Image 1
2	   Prod2	Test Prod 2	Prod2 Image 1
3	   Prod3	Test Prod 3	Prod3 Image 1
4	   Prod4	Test Prod 4	Prod4 Image 1



You can modifiy this query as per your requirement.
   
v2

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