Solved

3 table INNER JOIN

Posted on 2009-07-06
14
417 Views
Last Modified: 2012-05-07
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 + '%'
0
Comment
Question by:byte1
  • 8
  • 6
14 Comments
 
LVL 41

Expert Comment

by:ralmada
ID: 24786387
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
 
LVL 41

Expert Comment

by:ralmada
ID: 24786404
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
 

Author Comment

by:byte1
ID: 24786457
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 41

Expert Comment

by:ralmada
ID: 24786462
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
 
LVL 41

Expert Comment

by:ralmada
ID: 24786510
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
 

Author Comment

by:byte1
ID: 24786541
No that didnt help either, its same as my new query.
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24786545
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
 
LVL 41

Expert Comment

by:ralmada
ID: 24786560
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
 

Author Comment

by:byte1
ID: 24786590
"and" on line 2 ? I dont think that would work.
0
 

Author Comment

by:byte1
ID: 24786604
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
 

Author Comment

by:byte1
ID: 24786608
Sorry my bad :
 "It shouldnt be And but an OR"
0
 
LVL 41

Expert Comment

by:ralmada
ID: 24787241
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
 

Author Comment

by:byte1
ID: 24787737
No sorry, that didnt help either
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 24787889
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

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

828 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question