Multiple table Inner Join in SQL Server R2

Question

I'm having problem with an INNER JOIN. There are three tables Customer, Regular aAnd Master. Using this query, I'm only getting records from customer table

Query

Select 
    Customer.CustomerId, 
    Customer.RefId, 
    Regular.LicenseId, 
    Master.MasterId, 
    Master.FullName, 
    Master.Profile 
From 
    Customer 
Inner Join 
    Regular on Customer.RefId = Regular.Licenseid 
Inner join 
    master on regular.controlid = master.masterid Or customer.refid = master.masterid

Result

enter image description here

From the table Customer, RefId having both record M000001 and R000001. Why the result is only showing RefId starting from R000001 only

Data Source from Customer Master is...

Result will be including all row from Customer table...

enter image description here

Any one can solve this problem and edit my query to get the both type record from customer table...

Can you please add Regular.ControlID into your original query. You use it in the comparison but there is no reference to it in any result set. As such it is hard to identify what is going on with it. — KHeaney, Dec 19 '14 at 15:12
Your query is wrong, look closely at your customer id and full name in the result set. They don't match. Customer 04 is Nande in the customer table, but Salunke in your result set. I suspect the OR in the master table join is causing your problems — Sparky, Dec 19 '14 at 15:16
@Sparky CusromerId is coming from customer table and Full name is from Master Table. how can the same — Sunil Acharya, Dec 19 '14 at 15:21
@KHeaney after adding the Regular.ControlId the result will be same because the MasterId is same as Regular.ControlId — Sunil Acharya, Dec 19 '14 at 15:22
In your example table, C04 refers to a different customer than C04 refers to your result set... — Sparky, Dec 19 '14 at 15:23
@Sparky : you are right let me explain the whole think.. See I have three table Customer, Regular and master its clear. I'm putting data in customer using MasterId from master table and LicenseId from Regular Table. Regular in under MasterId and the field name is ControlId. Now I want to get the Full name and profile from Master Table where RefId from Customer is Regular.LicenseId and Master.MasterId from both table. I hope you understand.... — Sunil Acharya, Dec 19 '14 at 15:31

score 1 · Answer 1 · edited May 23 '17 at 12:28

From this SO question it seems like using an OR condition in your Join is a bad idea anyhow:

https://stackoverflow.com/a/5901901/3915817

As such I would just switch the query outright. you can do this using either Outer Joins

Select Customer.CustomerId, 
       Customer.RefId, 
       Regular.LicenseId, 
       Coalesce(Mast1.MasterId, Mast2.MasterId) AS MasterId,
       Coalesce(Mast1.FullName, Mast2.FullName) AS FullName,
       Coalesce(Mast1.Profile, Mast2.Profile) AS Profile
From Customer 
    Inner Join Regular on Customer.RefId = Regular.Licenseid 
    Left Outer Join master As Mast1 
        on regular.controlid = master.masterid
    Left Outer Join master As Mast2
        on customer.refid = master.masterid
Where (Mast1.masterid Is Not Null
    Or Mast2.MasterId Is Not Null)

Or with a Union clause

Select Customer.CustomerId, 
       Customer.RefId, 
       Regular.LicenseId, 
       Master.MasterId, 
       Master.FullName, 
       Master.Profile 
From Customer 
    Inner Join Regular on Customer.RefId = Regular.Licenseid 
    Inner Join master on regular.controlid = master.masterid

Union

Select Customer.CustomerId, 
       Customer.RefId, 
       Regular.LicenseId, 
       Master.MasterId, 
       Master.FullName, 
       Master.Profile 
From Customer 
    Inner Join Regular on Customer.RefId = Regular.Licenseid 
    Inner Join master on customer.refid = master.masterid

Multiple table Inner Join in SQL Server R2

1 Answers1