CODEWITHSUNDEEP
sqlsql-servert-sql
ThunD3eR
ThunD3eR

Reputation: 3456

How to select a column only if condition is met in TSQL

I have two tables. Customers and Images.

I join these and i select an Image Id from the Images table ONLY if the image is the profilepicture.

However if a user has several pictures but only on of the pictures is the profile picture i still get back several rows with the same user.

my code: 

SELECT DISTINCT c.Id, c.Name, c.LastName, c.Email, c.MobilePhoneNumber,
(SELECT CASE WHEN im.Isprofile = 1 THEN im.Id END) AS ImageId,
 im.ContentType 
   FROM Customers c
JOIN Images im ON im.CustomerId = c.Id

The "CASE WHEN" is obviously wrong and this feels overly simple thing to do but I have been at this for a while and can't figure it out.

EDIT: I get this result: enter image description here

I only want one row here since ImageId is null

Upvotes: 0

Views: 1046

Answers (4)

Chilli
Chilli

Reputation: 123

Try It :

SELECT 
    DISTINCT cust.Id
        ,cust.Name
        ,cust.LastName
        ,cust.Email
        ,cust.MobilePhoneNumber
        ,img.Id AS ImageId
        ,img.ContentType 
FROM Customers as cust
LEFT OUTER JOIN Images as img ON img.CustomerId = cust.Id and img.Isprofile = 1

Upvotes: 1

Subin Chalil
Subin Chalil

Reputation: 3660

SELECT DISTINCT c.Id,
  c.Name, 
  c.LastName,
   c.Email,
  c.MobilePhoneNumber,
 (im.Id) AS ImageId, im.ContentType FROM Customers c
LEFT JOIN Images im ON im.CustomerId = c.Id and im.Isprofile = 1

Hope this helps

Upvotes: 2

Matthew Haugen
Matthew Haugen

Reputation: 13296

It's up to your implementation, but it sounds like you might benefit in the long-run from a view for this. Here's the equivalent to what I'd do there, but with a CTE in its place.

WITH ProfilePictures AS (
    SELECT ID, ContentType, CustomerId
    FROM Images i
    WHERE i.IsProfile = 1
)

SELECT c.Id, c.Name, c.LastName, c.Email, c.MobilePhoneNumber, im.Id AS ImageId, im.ContentType
FROM Customers c
    LEFT OUTER JOIN ProfilePictures im ON im.CustomerId = c.Id

This implementation assumes that every customer has exactly zero or one images marked with IsProfile = 1.

Just to be clear, this is overkill if you're only looking at this logic once or twice. I'm just figuring tying customers to their pictures might be something you do a lot, in which case abstracting that logic out could come in handy.

For what it's worth, if my interpretation is correct, and certainly this is up to you, I'd be tempted to drop the IsProfile bit, and add a field on Customers for ProfileImageId with an appropriate foreign key. Then the join will be extremely straight-forward, and you won't have to worry about maintaining that logic.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Use OUTER APPLY:

SELECT c.Id, 
       c.Name, 
       c.LastName, 
       c.Email, 
       c.MobilePhoneNumber, 
       im.ID AS ImageID, 
       im.ContentType 
FROM Customers c
OUTER APPLY(SELECT TOP 1 ID, ContentType FROM Images im 
            WHERE im.CustomerId = c.Id AND im.Isprofile = 1) im

Upvotes: 2

Related Questions