3 Replies - 648 Views - Last Post: 21 May 2015 - 06:56 PM

#1 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

How to join two tables with column value condition

Posted 05 May 2015 - 12:43 AM

Hello

I would like to display the value of associated of two tables.
Because of that, I want to join two tables with condition

If the value of column CustomerLeadID is not null , select column CustomerLeadID
otherwise select column MemberLeadID,
then join the value of column LeadIDs with Lead table.

After that I found the error following below:
error Invalid column name 'LeadIDs'.

SELECT rhs.AppointmentNo AS RunningNo,
CASE WHEN rhs.CustomerLeadID IS NOT NULL
         THEN rhs.CustomerLeadID
     ELSE 
              rhs.MemberLeadID
     END AS LeadIDs,
     l.LeadFullName
FROM ReservationHS rhs
INNER JOIN Lead l
ON l.LeadID = LeadIDs  -- error Invalid column name 'LeadIDs'.



How to solve this ?
Please guide me.

Thanks

From
tokei

Is This A Good Question/Topic? 0
  • +

Replies To: How to join two tables with column value condition

#2 maceysoftware  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 348
  • View blog
  • Posts: 1,493
  • Joined: 07-September 13

Re: How to join two tables with column value condition

Posted 05 May 2015 - 04:20 AM

Hello,

I am not the best at SQL however you could just do the logic again in the join condition:

SELECT rhs.AppointmentNo AS RunningNo,
CASE WHEN rhs.CustomerLeadID IS NOT NULL
         THEN rhs.CustomerLeadID
     ELSE 
              rhs.MemberLeadID
     END AS LeadIDs,
     l.LeadFullName
FROM ReservationHS rhs
INNER JOIN Lead l
ON  rhs.CustomerLeadID IS NOT NULL AND l.LeadID = rhs.CustomerLeadID
	OR rhs.CustomerLeadID IS NULL AND l.LeadID = rhs.MemberLeadID

Was This Post Helpful? 0
  • +
  • -

#3 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon


Reputation: 6979
  • View blog
  • Posts: 14,598
  • Joined: 16-October 07

Re: How to join two tables with column value condition

Posted 05 May 2015 - 05:31 AM

Because LeadIDs doesn't exist yet. First:
SELECT rhs.AppointmentNo AS RunningNo,
    (CASE WHEN rhs.CustomerLeadID IS NOT NULL THEN rhs.CustomerLeadID ELSE rhs.MemberLeadID END) AS LeadIDs
    FROM ReservationHS rhs



Actually, stop here. In this particualar case, there is a handy function:
SELECT rhs.AppointmentNo AS RunningNo, IsNull(rhs.CustomerLeadID,rhs.MemberLeadID) AS LeadIDs
    FROM ReservationHS rhs



Then:
select a.RunningNo, a.LeadID, b.LeadFullName
    from (
        SELECT rhs.AppointmentNo AS RunningNo, IsNull(rhs.CustomerLeadID,rhs.MemberLeadID) AS LeadID
            FROM ReservationHS rhs
        ) a
        inner join Lead b
            on b.LeadID = a.LeadID



However, since the IsNull makes this pretty trivial, you can simply do:
select a.AppointmentNo AS RunningNo,
        IsNull(a.CustomerLeadID,a.MemberLeadID) AS LeadID
        b.LeadFullName
    FROM ReservationHS a
        inner join Lead b
            on b.LeadID = IsNull(a.CustomerLeadID,a.MemberLeadID)



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 tokei  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 60
  • Joined: 15-December 11

Re: How to join two tables with column value condition

Posted 21 May 2015 - 06:56 PM

View Postbaavgai, on 05 May 2015 - 08:31 PM, said:

Because LeadIDs doesn't exist yet. First:
SELECT rhs.AppointmentNo AS RunningNo,
    (CASE WHEN rhs.CustomerLeadID IS NOT NULL THEN rhs.CustomerLeadID ELSE rhs.MemberLeadID END) AS LeadIDs
    FROM ReservationHS rhs



Actually, stop here. In this particualar case, there is a handy function:
SELECT rhs.AppointmentNo AS RunningNo, IsNull(rhs.CustomerLeadID,rhs.MemberLeadID) AS LeadIDs
    FROM ReservationHS rhs



Then:
select a.RunningNo, a.LeadID, b.LeadFullName
    from (
        SELECT rhs.AppointmentNo AS RunningNo, IsNull(rhs.CustomerLeadID,rhs.MemberLeadID) AS LeadID
            FROM ReservationHS rhs
        ) a
        inner join Lead b
            on b.LeadID = a.LeadID



However, since the IsNull makes this pretty trivial, you can simply do:
select a.AppointmentNo AS RunningNo,
        IsNull(a.CustomerLeadID,a.MemberLeadID) AS LeadID
        b.LeadFullName
    FROM ReservationHS a
        inner join Lead b
            on b.LeadID = IsNull(a.CustomerLeadID,a.MemberLeadID)



Hope this helps.


Thanks so much for your helps, baavgai
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1