Solved

SQL Query Help - Join

Posted on 2011-09-14
7
271 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

679 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