Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL Query Help - Join

Posted on 2011-09-14
7
Medium Priority
?
282 Views
Last Modified: 2012-08-13
Team -

I have a query which populates data like below

SRID     Owner     CustomerName  
12345     A           ABCD
33221     B           DEFG



I have another table where I am capturing certain customer names as "Hot" where the table structure is like below:

CustomerName  Hot
ABC                    Active
DEF                    Active


What I really need to do is..,

SRID    Owner    CustomerName   Hot
12345     A           ABCD                 Active
33221     B           DEFG                 Active


I want to join the Hot status with the customername. It should be "Like" syntax as sometimes, ABC company will be written as ABCCo., & hence the request.

Kindly help
0
Comment
Question by:davidi1
7 Comments
 
LVL 3

Expert Comment

by:Srm74
ID: 36535868
Can you paste the sql for the 2 first queries..
0
 
LVL 2

Author Comment

by:davidi1
ID: 36535872
First Query:

select  SR_ID_NO, Severity, SR_Owner, Days_Open, SR_Status, Cust_Name,  Days_Since_Last_Update from Flex.dbo.Backlograw where [Group] in ('Beta1 Chat', 'VNXe VIC/Web') and ReportTimeStamp =  (Select Distinct Top 1 ReportTimeStamp from Flex.dbo.Backlograw order by ReportTimeStamp desc)

Second Query:

select CustomerName, HotStatus from Flex.dbo.HotAccount
0
 
LVL 3

Expert Comment

by:Srm74
ID: 36535938
Something like this..

select  
    B.SR_ID_NO,
B.Severity, B.SR_Owner, B.Days_Open, B.SR_Status, B.Cust_Name,  B.Days_Since_Last_Update,
H.HotStatus

from
Flex.dbo.Backlograw B INNER JOIN  Flex.dbo.HotAccount  H ON B.Cust_Name = H.CustomerName
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 2

Author Comment

by:davidi1
ID: 36535968
I already tried Join query.


select a.SR_ID_NO, b.HotStatus, a.Severity, a.SR_Owner, a.Days_Open, a.SR_Status, a.Cust_Name,
a.Days_Since_Last_Update from
(select  SR_ID_NO, Severity, SR_Owner, Days_Open, SR_Status, Cust_Name,
Days_Since_Last_Update from Flex.dbo.Backlograw where [Group] in ('Beta1 Chat', 'VNXe VIC/Web', 'VNXe Core', 'VNXe DH') and ReportTimeStamp =
(Select Distinct Top 1 ReportTimeStamp from Flex.dbo.Backlograw order by ReportTimeStamp desc)) a
Full outer join
(select CustomerName, HotStatus from Flex.dbo.VNXeHotAccount ) b
on a.Cust_Name = b.CustomerName where a.SR_ID_No <> ''
order by Cast(Days_Open as decimal(10,0)) desc

But the problem is.., it does exact match. What I want is like match.

0
 
LVL 2

Author Comment

by:davidi1
ID: 36535971
& i dont want Inner join but full outer as I want to display all Customer's + which includes Hot as well
0
 
LVL 9

Accepted Solution

by:
mimran18 earned 2000 total points
ID: 36536179
Hi,
   Here we go.

 
Drop table test1
Go
Create table test1
(SRID int,
[Owner] nvarchar(50),
[CustomerName] nvarchar(50))
Go


Drop table test2
Go
Create table test2
([CustomerName] nvarchar(50),
[Status] nvarchar(50))
Go

Insert  into test1 values (12345,'A','ABCD')
Insert  into test1 values (33221,'B','DEFG')
GO

Insert  into test2 values ('ABC','Active')
Insert  into test2 values ('DEF','Active')


Go
SELECT test1.*,test2.Status
FROM test1
INNER JOIN test2 ON test1.[CustomerName] LIKE '%' + Convert(nvarchar(50),test2.[CustomerName]) + '%'

Open in new window

0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 36536240
Just do the inner join with CustomerName
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Integration Management Part 2

971 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