CODEWITHSUNDEEP
sqlsql-server-2005sql-server-2008t-sql
desi
desi

Reputation: 486

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

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'

Upvotes: 1

Views: 166

Answers (4)

Jeremy Wiggins
Jeremy Wiggins

Reputation: 7299

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.

Upvotes: 2

Jason
Jason

Reputation: 56

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'

Upvotes: 4

Jake Feasel
Jake Feasel

Reputation: 16945

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

Upvotes: 0

Chris
Chris

Reputation: 3162

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

Upvotes: 0

Related Questions