CODEWITHSUNDEEP
sqlsql-serversql-server-2008-r2
Sunil Acharya
Sunil Acharya

Reputation: 1183

Multiple table Inner Join in SQL Server R2

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...

Upvotes: 0

Views: 143

Answers (1)

KHeaney
KHeaney

Reputation: 785

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

Upvotes: 1

Related Questions