Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL Query Help - Join

Posted on 2011-09-14
7
Medium Priority
?
284 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Integration Management Part 2
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

564 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