3 table INNER JOIN

I am trying to modify my query to get results from all three tables. However I cannot get the same results.

Old Query :
SELECT * FROM oc_SDN WHERE Ent_Num IN
                   (
            SELECT Ent_Num FROM oc_SDN A INNER JOIN FiPort.dbo.[oc_SearchNames] B
            ON A.SDN_Name LIKE B.Names + '%'
                    )
OR Ent_Num IN (
            SELECT Ent_Num FROM oc_Aliases A INNER JOIN FiPort.dbo.[oc_SearchNames] B
            ON A.Alt_Name LIKE B.Names + '%'
                    )
                    
                    
New:
      
      SELECT B.Ent_Num,A.Names,A.Address,A.City,A.State,B.*,A.NameId FROM [oc_SearchNames] A
      INNER JOIN oc_SDN  B ON B.SDN_Name LIKE '%' + A.Names + '%'
       LEFT OUTER JOIN oc_Aliases  C ON C.Alt_Name LIKE '%' + A.Names + '%'
byte1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ralmadaCommented:
What about like this?
select * from OC_SDN a
inner join Fiport.dbo.[oc_searchnames] b on a.Ent_Num = b.Ent_num
where a.SDN_Name like b.Names + '%' or
a.Alt_Name like b.Names + '%'

Open in new window

0
ralmadaCommented:
sorry like this:
select * from OC_SDN a
inner join Fiport.dbo.[oc_searchnames] b on a.Ent_Num = b.Ent_num
inner join Fiport.dbo.[oc_Aliases] c on a.Ent_Num = c.Ent_num
where a.SDN_Name like b.Names + '%' or
c.Alt_Name like b.Names + '%'

Open in new window

0
byte1Author Commented:
OC_SDN has Ent_Num,SDN_Name,
Aliases has Ent_Num,AltName
oc_searchnames has only Names

I dont have Ent_Num on all three tables. searchNAmes has to find matches on both tables and also return its own data.
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ralmadaCommented:
And if you want to include all records from OC_SDN no matter if there is any matching with oc_Aliases, then use the left join like this:
select a.* from OC_SDN a
inner join Fiport.dbo.[oc_searchnames] b on a.Ent_Num = b.Ent_num 
left join oc_Aliases c on a.Ent_Num = c.Ent_num
where a.SDN_Name like b.Names + '%' or
c.Alt_Name like b.Names + '%'

Open in new window

0
ralmadaCommented:
What about like this then?

select * from OC_SDN a
inner join oc_Aliases b on a.Ent_num = b.Ent_num
inner join oc_searchnames c on a.SDN_Name = c.Names
where a.SDN_Name like c.Names + '%' or
b.Alt_Name like c.Names + '%'

Open in new window

0
byte1Author Commented:
No that didnt help either, its same as my new query.
0
ralmadaCommented:
Actually like this:
select * from OC_SDN a
inner join oc_Aliases b on a.Ent_num = b.Ent_num and 
inner join Fiport.dbo.[oc_searchnames] c on a.SDN_Name like c.Names + '%'
where a.SDN_Name like c.Names + '%'

Open in new window

0
ralmadaCommented:
I meant
select * from OC_SDN a
inner join oc_Aliases b on a.Ent_num = b.Ent_num and 
inner join oc_searchnames c on a.SDN_Name like c.Names + '%'
where b.Alt_Name like c.Names + '%'

Open in new window

0
byte1Author Commented:
"and" on line 2 ? I dont think that would work.
0
byte1Author Commented:
I want certain rows of data from oc_searchnames and some from SDN, if matching names from SearchNames is found in either of the other two tables, SDN and Aliases. It shouldnt be And or an OR.
0
byte1Author Commented:
Sorry my bad :
 "It shouldnt be And but an OR"
0
ralmadaCommented:
My mistake, there's an extra AND there. Can you give this a try?
select * from OC_SDN a
left join oc_Aliases b on a.Ent_num = b.Ent_num
left join Fiport.dbo.[oc_searchnames] c on a.SDN_Name like c.Names + '%'
where b.Alt_Name like c.Names + '%'

Open in new window

0
byte1Author Commented:
No sorry, that didnt help either
0
ralmadaCommented:
So basically you just need to pull more columns from OC_SearchNames, am I right? So I would still use your old query but modify it like this:

SELECT t1.*, t2.* FROM oc_SDN t1
left join oc_SearchNames t2 on a.SDN_Name like b.Names + '%'
WHERE Ent_Num IN 
                   (
            SELECT Ent_Num FROM oc_SDN A INNER JOIN FiPort.dbo.[oc_SearchNames] B 
            ON A.SDN_Name LIKE B.Names + '%'
                    )
OR Ent_Num IN ( 
            SELECT Ent_Num FROM oc_Aliases A INNER JOIN FiPort.dbo.[oc_SearchNames] B 
            ON A.Alt_Name LIKE B.Names + '%'
                    )

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.