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{imageID, 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.pID

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]

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,img2,img3,img4), product2 has 3 images(img5,img6,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