By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,659 Members | 1,678 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,659 IT Pros & Developers. It's quick & easy.

How to Query SQL Select with 2 table

Kosal
P: 68
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.
Feb 19 '10 #1
Share this Question
Share on Google+
4 Replies


OraMaster
100+
P: 135
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.
Feb 19 '10 #2

Kosal
P: 68
OK sir

thanks you for your help.
Apr 3 '10 #3

nbiswas
100+
P: 149
I am presenting a scenario.

Suppose you have two tables tblProduct and tblProductImage

tblProduct
Expand|Select|Wrap|Line Numbers
  1. pId     pName
  2. 1    p1
  3. 2    p2
  4. 3    p3
  5. 4    p4
tblProductImage
imageId Pid PName

Expand|Select|Wrap|Line Numbers
  1. 1    1    image1_Prod1
  2. 2    1    image2_Prod1
  3. 3    1    image3_Prod1
  4. 4    2    image1_Prod2
  5. 5    2    image2_Prod2
  6. 6    3    image1_Prod3
  7. 7    3    image2_Prod3
  8. 8    4    image1_Prod4
As per your statement, the output should be

Expand|Select|Wrap|Line Numbers
  1. pid      pName      ImageId          ImageName
  2.  
  3. 1    p1    1    image1_Prod1
  4. 1    p1    2    image1_Prod1
  5. 1    p1    3    image1_Prod1
  6. 2    p2    4    image1_Prod2
  7. 2    p2    5    image1_Prod2
  8. 3    p3    6    image1_Prod3
  9. 3    p3    7    image1_Prod3
  10. 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

Expand|Select|Wrap|Line Numbers
  1. select 
  2.     x.pId
  3.     ,x.pName
  4.     ,x.imageID 
  5.     ,case when x.pId = y.pId then y.imageName end as imageName
  6.  
  7. from (    select 
  8.             p.pId
  9.             ,p.pName
  10.             ,i.imageName
  11.             ,i.imageID
  12.         from tblProduct p join tblImage i
  13.         on p.pId = i.pId)x
  14. join (    select 
  15.             imageID
  16.             ,pId
  17.             ,imageName
  18.          from(select dense_rank() over(partition by pid order by imageid) rn, imageID,pId,imageName from tblImage)x
  19.         where x.rn = 1) y
  20. 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.
Apr 9 '10 #4

Kosal
P: 68
Hello

Many thanks for your help.

Best regards
Kosal
Oct 19 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.