Solved

SQL Query Help - Join

Posted on 2011-09-14
7
275 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

752 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