How to Query SQL Select with 2 table

[Kosal]

Could you help to give me the sample about SQL Query please?

I have 2 table :

- Table Product{pID, pName, pType, pDetail, pStock}
- Table ProductImage{im ageID, pID, imageName, imageFile}

One Product have many Image so I want to query all product to display but for one product one image but I cannot query becuase when I query all product they show all product all image. but I need one product one image.

[OraMaster]

Hi,

I guess you need below query to execute

select Product .*,ProductImage .*
from Product join ProductImage
on Product.pID = ProductImage.pI D

But if above query is not useful to you then try to post the sample data for both tables along with expected output.

[Kosal]

OK sir

thanks you for your help.

[nbiswas]

Solution to How to Query SQL Select with 2 table

I am presenting a scenario.

Suppose you have two tables tblProduct and tblProductImage

tblProduct

pId     pName
1	p1
2	p2
3	p3
4	p4

tblProductImage
imageId Pid PName

1	1	image1_Prod1
2	1	image2_Prod1
3	1	image3_Prod1
4	2	image1_Prod2
5	2	image2_Prod2
6	3	image1_Prod3
7	3	image2_Prod3
8	4	image1_Prod4

As per your statement, the output should be

pid      pName      ImageId          ImageName

1	p1	1	image1_Prod1
1	p1	2	image1_Prod1
1	p1	3	image1_Prod1
2	p2	4	image1_Prod2
2	p2	5	image1_Prod2
3	p3	6	image1_Prod3
3	p3	7	image1_Prod3
4	p4	8	image1_Prod4

i.e. for all products , all information pertaining to that product present in the productIamge table should appear but only the first image will come for every distinct product.

i.e. If product1 has 4 images(img1,img 2,img3,img4), product2 has 3 images(img5,img 6,img7) in the output all the informations will appear but for product1 always the image column will be filled with img1 and for product2 always will be img5 as per the example I have set.

If this asumption of mine is correct, then the query is

select 
	x.pId
	,x.pName
	,x.imageID 
	,case when x.pId = y.pId then y.imageName end as imageName

from (	select 
			p.pId
			,p.pName
			,i.imageName
			,i.imageID
		from tblProduct p join tblImage i
		on p.pId = i.pId)x
join (	select 
			imageID
			,pId
			,imageName
		 from(select dense_rank() over(partition by pid order by imageid) rn, imageID,pId,imageName from tblImage)x
		where x.rn = 1) y
on x.pId = y.pId

This code will work for Sql Server 2005+.

In your case just add the relevant column names for both the tables.

Hope this helps.

[Kosal]

Hello

Many thanks for your help.

Best regards
Kosal