CODEWITHSUNDEEP
sqlsql-serverjoinleft-join
imba22
imba22

Reputation: 649

Left join will not produce the expected results

I have this weird issue. below is the best representation of my code, except instead of A_ and B_ are common table expression in my code. Someone suggested outer apply could be a better idea but I am just baffled where I am wrong in first place. I understand thats a lot of code, but I thought this could help others as well

Select c.consumerID,
   ,CASE 
          WHEN lacp.LowestAnnualCost  IS NOT NULL THEN  
                 CASE 
                       WHEN lcp.LowestAnnualCost < lacp.LowestAnnualCost THEN 
                              lcp.LowestAnnualCost 
                       ELSE lacp.LowestAnnualCost  
                 END
          ELSE
                 lcp.LowestAnnualCost 
   END
   AS LowestAnnualCost
From Consumer c  
left join A_ lcp on c.ConsumerID = lcp.ConsumerID
left join B_ lacp on lcp.ConsumerID = lacp.ConsumerID

ConsumerID LowestAnnualCost
1               23
2               19
3               NULL
4               NULL
5               25

But when I look up each of these tables which I am joining above with Consumer table, I get this

Select * from A_ as lcp
ConsumerID LowestAnnualCost
1               23
2               19
3               10
4               54
5               25

Select * from B_ as lacp
ConsumerID LowestAnnualCost
1               23
2               19
3               98
4               NULL
5               25

Upvotes: 1

Views: 68

Answers (2)

deroby
deroby

Reputation: 6002

Unless I've missed somehthing, I'm not sure your code fully represents what you're trying to do. I tried to make a sqlfiddle but I can't get it to run there (build works fine) : http://sqlfiddle.com/#!3/a3619/1

Anyway, when I run this:

CREATE TABLE A (ConsumerID int PRIMARY KEY, LowestAnnualCost int)
CREATE TABLE B (ConsumerID int PRIMARY KEY, LowestAnnualCost int)
CREATE TABLE C (ConsumerID int PRIMARY KEY)

INSERT C (ConsumerID) VALUES (1)
INSERT C (ConsumerID) VALUES (2)
INSERT C (ConsumerID) VALUES (3)
INSERT C (ConsumerID) VALUES (4)
INSERT C (ConsumerID) VALUES (5)

INSERT A (ConsumerID, LowestAnnualCost) VALUES (1, 23)
INSERT A (ConsumerID, LowestAnnualCost) VALUES (2, 19)
INSERT A (ConsumerID, LowestAnnualCost) VALUES (3, 10)
INSERT A (ConsumerID, LowestAnnualCost) VALUES (4, 54)
INSERT A (ConsumerID, LowestAnnualCost) VALUES (5, 25)

INSERT B (ConsumerID, LowestAnnualCost) VALUES (1, 23)
INSERT B (ConsumerID, LowestAnnualCost) VALUES (2, 19)
INSERT B (ConsumerID, LowestAnnualCost) VALUES (3, 98)
INSERT B (ConsumerID, LowestAnnualCost) VALUES (4, NULL)
INSERT B (ConsumerID, LowestAnnualCost) VALUES (5, 25)

GO

Select c.ConsumerID,
   CASE 
          WHEN lacp.LowestAnnualCost  IS NOT NULL THEN  
                 CASE 
                       WHEN lcp.LowestAnnualCost < lacp.LowestAnnualCost THEN 
                              lcp.LowestAnnualCost 
                       ELSE lacp.LowestAnnualCost  
                 END
          ELSE
                 lcp.LowestAnnualCost 
   END
   AS LowestAnnualCost
From C c  
left join A lcp on c.ConsumerID = lcp.ConsumerID
left join B lacp on lcp.ConsumerID = lacp.ConsumerID

it returns me this:

ConsumerID  LowestAnnualCost
1   23
2   19
3   10
4   54
5   25

Upvotes: 0

LDMJoe
LDMJoe

Reputation: 1589

I had to make a few assumptions about your Consumer table, but other than that, try this...

CREATE TABLE #Consumer (ConsumerID int IDENTITY(1,1) NOT NULL, Consumer_Name nvarchar(max) null)
CREATE TABLE #A_ (ConsumerID int NULL, LowestAnnualCost int NULL)
CREATE TABLE #B_ (ConsumerID int NULL, LowestAnnualCost int NULL)


INSERT INTO #Consumer (Consumer_Name) VALUES ('Bob')
INSERT INTO #Consumer (Consumer_Name) VALUES ('Mike')
INSERT INTO #Consumer (Consumer_Name) VALUES ('Larry')
INSERT INTO #Consumer (Consumer_Name) VALUES ('Kevin')
INSERT INTO #Consumer (Consumer_Name) VALUES ('Brian')


INSERT INTO #A_ (ConsumerID, LowestAnnualCost) VALUES (1, 23)
INSERT INTO #A_ (ConsumerID, LowestAnnualCost) VALUES (2, 19)
INSERT INTO #A_ (ConsumerID, LowestAnnualCost) VALUES (3, 10)
INSERT INTO #A_ (ConsumerID, LowestAnnualCost) VALUES (4, 54)
INSERT INTO #A_ (ConsumerID, LowestAnnualCost) VALUES (5, 25)

INSERT INTO #B_ (ConsumerID, LowestAnnualCost) VALUES (1, 23)
INSERT INTO #B_ (ConsumerID, LowestAnnualCost) VALUES (2, 19)
INSERT INTO #B_ (ConsumerID, LowestAnnualCost) VALUES (3, 98)
INSERT INTO #B_ (ConsumerID, LowestAnnualCost) VALUES (4, NULL)
INSERT INTO #B_ (ConsumerID, LowestAnnualCost) VALUES (5, 25)



Select 
  c.consumerID,
  CASE WHEN lacp.LowestAnnualCost  IS NOT NULL THEN  
      CASE WHEN lcp.LowestAnnualCost < lacp.LowestAnnualCost THEN 
          lcp.LowestAnnualCost 
       ELSE
           lacp.LowestAnnualCost  
       END
    ELSE
        lcp.LowestAnnualCost 
     END
   AS LowestAnnualCost
From #Consumer c  
left join #A_ lcp on c.ConsumerID = lcp.ConsumerID
left join #B_ lacp on lcp.ConsumerID = lacp.ConsumerID

--DROP TABLE #Consumer
--DROP TABLE #A_
--DROP TABLE #B_

Now, you never say what exactly your desired result should be, but this is the output generated follows...

consumerID  LowestAnnualCost
----------------------------
1           23
2           19
3           10
4           54
5           25

Upvotes: 1

Related Questions