Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Query Help - Join

Posted on 2011-09-14
7
Medium Priority
?
279 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

719 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