How to fetch the values in 1 row instead of multiple rows in SQL

Question

I have table where all item names and their itemcodes are displayed.

TableItem

ItemID, ItemName
772729918, 'ABC'
772729921, 'BCD'
772729922, 'EFG'
772729923, 'HIJ'
772729926, 'KLM'

I have another table which has the item image paths defined.

TableImages

itemimageID, Imagesurface,ImagePath, fitemID
111,'FRONT', '772729918_1_1_FRONT.tif', 772729918
112,'BACK', '772729918_1_1_BACK.tif',772729918
222,'FRONT','772729921_1_1_FRONT'.tif,772729921
223,'BACK','772729921_1_1_BACK.tif',772729921
333,'FRONT','772729922_1_1_FRONT.tif',772729922
332,'BACK', '772729922_1_1_BACK.tif',772729922
444,'FRONT','772729923_1_1_FRONT.tif',772729923
442,'BACK', '772729923_1_1_BACK.tif',772729923
555,'FRONT','772729926_1_1_FRONT.tif',772729926
552,'BACK', '772729926_1_1_BACK.tif',772729926

If I join these two table i am getting values as below.

Select ItemID,ItemName, Imagesurface,ImagePath from TableItem i inner join TableImages ti on ti.fitemID = i.ItemID

772729918,'ABC','FRONT','772729918_1_1_FRONT.tif'
772729918,'ABC','BACK','772729918_1_1_BACK.tif'
772729921,'BCD','FRONT','772729921_1_1_FRONT.tif'
772729921,'BCD','BACK','772729921_1_1_BACK.tif'
772729922,'EFG','FRONT','772729922_1_1_FRONT.tif'
772729922,'EFG','BACK','772729922_1_1_BACK.tif'
772729923,'HIJ','FRONT','772729923_1_1_FRONT.tif'
772729923,'HIJ','BACK','772729923_1_1_BACK.tif'
772729926,'KLM','FRONT','772729926_1_1_FRONT.tif'
772729926,'KLM','BACK','772729926_1_1_BACK.tif'

Actually I want to show them in one row instead of 2 line for each item. Thanks for helping me.

ItemID, ItemName, ImageSurface, ImageFront, ImageBack
772729918,'ABC','FRONT','772729918_1_1_FRONT.tif','772729918_1_1_BACK.tif'
772729921,'BCD','FRONT','772729921_1_1_FRONT.tif','772729921_1_1_BACK.tif'
772729922,'EFG','FRONT','772729922_1_1_FRONT.tif','772729922_1_1_BACK.tif'
772729923,'HIJ','FRONT','772729923_1_1_FRONT.tif','772729923_1_1_BACK.tif'
772729926,'KLM','FRONT','772729926_1_1_FRONT.tif','772729926_1_1_BACK.tif'

Adriano Carneiro · Accepted Answer · 2011-11-23 19:46:04Z

4

SELECT itemid, 
       itemname, 
       imagesurface, 
       fti.imagepath AS imagefront, 
       bti.imagepath AS imageback 
FROM   tableitem i 
       LEFT JOIN tableimages fti 
         ON fti.fitemid = i.itemid 
            AND fti.imagesurface = 'FRONT' 
       LEFT JOIN tableimages bti 
         ON bti.fitemid = i.itemid 
            AND fti.imagesurface = 'BACK'

edited Nov 23, 2011 at 19:46

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

answered Nov 23, 2011 at 19:45

Jason

561 bronze badge

Sign up to request clarification or add additional context in comments.

Comments

Jeremy Wiggins · Accepted Answer · 2011-11-23 19:51:12Z

2

That's how an INNER JOIN works... since you have multiple rows that match on your join condition, you will get multiple rows in your result set as well.

You can get what you want using sub-queries though.

SELECT   ItemId
        ,ItemName
        --,(SELECT TOP 1 ImageSurface FROM TableImages WHERE fItemId = ItemId)
        ,(SELECT TOP 1 ImagePath FROM TableImages WHERE fItemId = ItemId AND ImageSurface = 'FRONT') ImageFront
        ,(SELECT TOP 1 ImagePath FROM TableImages WHERE fItemId = ItemId AND ImageSurface = 'BACK') ImageBack
FROM    TableItem

Note that I commented out the ImageSurface field because it seemed unnecessary.

answered Nov 23, 2011 at 19:51

Jeremy Wiggins

7,3396 gold badges44 silver badges56 bronze badges

1 Comment

desi Over a year ago

thanks Jeremy Wiggins, This may be bit slow when we look into performance. Please advise me.

Chris · Accepted Answer · 2011-11-23 19:38:56Z

0

Try a Left Join instead of an inner join

Select i.ItemID, i.ItemName, ti.Imagesurface, ti.ImagePath 
from TableItem i Left join TableImages ti     
on ti.fitemID = i.ItemID

This will include all the items from your left hand table once and match all the items in the right hand table with the id if they exist. NUll if they don't

answered Nov 23, 2011 at 19:38

Chris

3,1521 gold badge21 silver badges27 bronze badges

Comments

Jake Feasel · Accepted Answer · 2011-11-23 19:42:12Z

0

You need to do what's called a "pivot". Here's an example:

SELECT pivottable.* FROM
(
  Select ItemID,ItemName, Imagesurface,ImagePath from TableItem i inner join TableImages ti on ti.fitemID = i.ItemID 
) as baseTable
PIVOT
(
min(imagepath) for ImageSurface in ( [FRONT],[BACK] )
) as pivottable

answered Nov 23, 2011 at 19:42

Jake Feasel

17k5 gold badges55 silver badges68 bronze badges

1 Comment

Jake Feasel Over a year ago

Note - this is just a start toward what I believe is the right direction. Often getting it 100% right will require some continued tweaking through trial and error.

Collectives™ on Stack Overflow

How to fetch the values in 1 row instead of multiple rows in SQL

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related