CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
volume one
volume one

Reputation: 7543

How to combine multiple rows from different tables into a single row?

I am trying to get back all accounts registered in my database, with image filepaths for their 'profile' image and 'cover' image (facebook style). Image filepaths are stored in a different table to their account table.

My code is like this:

SELECT 
  a1.AccountID, 
 a1.Forename, 
  a1.Surname, 
  img1.Filename AS ProfileImg, 
  img2.Filename AS CoverImg
FROM
  dbo.Account a1
LEFT JOIN
  dbo.AccountImage img1 ON --table with images in
  img1.AccountID = a1.AccountID AND ProfileImg = 1 --boolean column to mark it as profile pic
LEFT JOIN
  dbo.AccountImage img2 ON --table with images in
  img2.AccountID = a1.AccountID and CoverImg = 1 --boolean column to flag as cover pic

Account table:

AccountID | Forename | Surname |
12345     | Ben      | Hur     |
12346     | Frodo    | Baggins |
12349     | Bill     | Gates   |

AccountImage table:

AccountID | Filename     | ProfileImg | CoverImg
12345     | face.jpg     | True       | NULL
12345     | bg.jpg       | NULL       | True
12346     | graph.png    | NULL       | NULL
12349     | sunset.jpg   | NULL       | True

This currently returns:

|AccountID|Forename|Surname|ProfileImg|CoverImg  |
|    12345|     Ben|    Hur|NULL      |bg.jpg    |
|    12345|     Ben|    Hur|face.jpg  |NULL      |

Result I need

|AccountID|Forename|Surname|ProfileImg|CoverImg  |
|    12345|     Ben|    Hur|face.jpg  |bg.jpg    |

I just want to return a single row for each AccountID which contains both their account details and image paths respectively (there are hundreds of accounts). Or else I end up with duplicate rows - one containing their ProfileImg filepath and one containing their CoverImg filepath.

Just to note - it is possible that they don't have a profile or cover pic. Which means both columns can be NULL.

Upvotes: 0

Views: 82

Answers (3)

Michael
Michael

Reputation: 188

Here is a view that will do it. You will need to ensure that you have unique indexes set up on the account image table to prevent more than one image of each type being setup up on a particular account.

SELECT AccountID, Forename, Surname,
(SELECT TOP (1) Filename
FROM dbo.AccountImage
WHERE (AccountID = dbo.Account.AccountID) AND (ProfileImg <> 0)) AS ProfileImg,
(SELECT TOP (1) Filename
 FROM dbo.AccountImage AS AccountImage_2
 WHERE (AccountID = dbo.Account.AccountID) AND (CoverImg <> 0)) AS CoverImg
FROM dbo.Account
WHERE ((SELECT COUNT(*) AS Expr1
FROM dbo.AccountImage AS AccountImage_1
WHERE (AccountID = dbo.Account.AccountID) AND (ProfileImg <> 0) OR
(AccountID = dbo.Account.AccountID) AND (CoverImg <> 0)) = 2)

Upvotes: 0

shweta kokate
shweta kokate

Reputation: 21

Just u have to use inner join instead of left join

SELECT 
  a1.AccountID, 
  a1.Forename, 
  a1.Surname, 
  img1.Filename AS ProfileImg, 
  img2.Filename AS CoverImg
FROM
  dbo.Account a1
INNER JOIN
  dbo.AccountImage img1 ON 
  img1.AccountID = a1.AccountID AND ProfileImg = 1 
INNER JOIN
  dbo.AccountImage img2 ON
  img2.AccountID = a1.AccountID and CoverImg = 2

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

SELECT 
  a1.AccountID, 
 a1.Forename, 
  a1.Surname, 
  max(img1.Filename) AS ProfileImg, 
  max(img2.Filename) AS CoverImg
FROM
  dbo.Account a1
LEFT JOIN
  dbo.AccountImage img1 ON --table with images in
  img1.AccountID = a1.AccountID AND ProfileImg = 1 --boolean column to mark it as profile pic
LEFT JOIN
  dbo.AccountImage img2 ON --table with images in
  img2.AccountID = a1.AccountID and CoverImg = 2
GROUP BY 
 a1.AccountID, 
 a1.Forename, 
  a1.Surname

Upvotes: 2

Related Questions