[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Join two Rows in SQL and display results

Posted on 2010-09-16
19
Medium Priority
?
583 Views
Last Modified: 2012-05-10
Need help to run a sql query on two rows in the same database. Where the unique id is the same first 10 characters.

Row 1
Then I need to display the results of Row1 (cal1.jpg) which includes: userfield, dcontent=ext-queues


Row 2Then I need to display the results of Row 2 (call2.jpg) which includes: dst, disposition=ANSWERED, amaflag





0
Comment
Question by:jjohnson502
  • 9
  • 4
  • 4
  • +1
19 Comments
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33696778
How about this query?

SELECT
A.userfield, A.dcontent,
B.dst, B.disposition, B.amaflag
FROM
[tablename] as A
INNER JOIN [tablename] as B
ON LEFT(A.uniqueid, 10) = LEFT(B.uniqueid, 10)
WHERE
A.dcontent='ext-queues'
AND
B.disposition='ANSWERED'
0
 

Author Comment

by:jjohnson502
ID: 33696837
SQL query:  

SELECT A.userfield, A.dcontent, B.dst, B.disposition, B.amaflag
FROM [cdr] AS A
INNER JOIN [cdr] AS B ON LEFT( A.uniqueid, 10 ) = LEFT( B.uniqueid, 10 )
WHERE A.dcontent = 'ext-queues'
AND B.disposition = 'ANSWERED'
LIMIT 0 , 30

MySQL said:  

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '[cdr] as A
INNER JOIN [cdr] as B
ON LEFT(A.uniqueid, 10) = LEFT(B.uniqueid, 10)
' at line 9
0
 

Author Comment

by:jjohnson502
ID: 33696875
sorry forgot to mention that the rows are on the same databse.
0
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.

 

Author Comment

by:jjohnson502
ID: 33696883
& same table. the table name is cdr
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33697888
Remove the square brackets

SELECT A.userfield, A.dcontent, B.dst, B.disposition, B.amaflag
FROM cdr AS A
INNER JOIN [cdr] AS B ON LEFT( A.uniqueid, 10 ) = LEFT( B.uniqueid, 10 )
WHERE A.dcontent = 'ext-queues'
AND B.disposition = 'ANSWERED'
LIMIT 0 , 30
0
 
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 33697889
All square brackets
SELECT A.userfield, A.dcontent, B.dst, B.disposition, B.amaflag
FROM cdr AS A
INNER JOIN cdr AS B ON LEFT( A.uniqueid, 10 ) = LEFT( B.uniqueid, 10 )
WHERE A.dcontent = 'ext-queues'
AND B.disposition = 'ANSWERED'
LIMIT 0 , 30
0
 

Author Comment

by:jjohnson502
ID: 33698698
Tried both examples, neither worked. I ma running mysql if that makes any difference

SQL query:  

SELECT A.userfield, A.dcontent, B.dst, B.disposition, B.amaflag
FROM cdr AS A
INNER JOIN cdr AS B ON LEFT( A.uniqueid, 10 ) = LEFT( B.uniqueid, 10 )
WHERE A.dcontent = 'ext-queues'
AND B.disposition = 'ANSWERED'
LIMIT 0 , 30

MySQL said:  

#1054 - Unknown column 'A.dcontent' in 'field list'

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33698975
Dear  All,

All solution is right.
just column name u putting is wrong.

its dcontext not the dcontent
SELECT A.userfield, A.dcontext, B.dst, B.disposition, B.amaflag
FROM cdr AS A
INNER JOIN cdr AS B ON LEFT( A.uniqueid, 10 ) = LEFT( B.uniqueid, 10 ) 
WHERE A.dcontext = 'ext-queues'
AND B.disposition = 'ANSWERED'

Open in new window

0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33699715
:-p  oh.. unforgivable mistake.

I write example base on his description.
forget to check field name versus the image
0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 33699737

:D

It happens....

we are humans
0
 

Author Comment

by:jjohnson502
ID: 33703717
lol. Got the query to work but did not get the required output


output.jpg
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33704159
not get required output?
can you sample with your expecting result?

is it that you want to show 2 lines of data?
1 line with dcontext=ext-queues
another line with disposition=ANSWERED
both line having same first 10 characters for uniqueid
0
 

Author Comment

by:jjohnson502
ID: 33704303
need to see the data of these in the output:

Row1 (cal1.jpg) which includes: userfield, dcontent=ext-queues
Row 2 (call2.jpg) which includes: dst, disposition=ANSWERED, amaflag
0
 

Author Comment

by:jjohnson502
ID: 33704434
figured out another way to run this query. how can i do this?

if i can filter out the dst channel so that it querys only the three digit extension from Local/872@from-internal-f45e,1.  So now i only need to filter out one row.

what is the code for that?
0
 

Author Comment

by:jjohnson502
ID: 33704474
how would i right a query to filter out 872 from the dstchannel?
0
 
LVL 11

Expert Comment

by:JoeNuvo
ID: 33706417
regardless of new question about filter out 872
please check if below code can give you the expected result?

SELECT cdr.*
FROM cdr
INNER JOIN
	(
	SELECT
		A.uniqueid as ID1,
		B.uniqueid as ID2
	FROM
		cdr AS A
		INNER JOIN cdr AS B
		ON LEFT( A.uniqueid, 10 ) = LEFT( B.uniqueid, 10 ) 
	WHERE
		A.dcontext = 'ext-queues'
		AND B.disposition = 'ANSWERED'
	) As SubQuery 
ON    cdr.uniqueid = SubQuery.ID1 
   OR cdr.uniqueid = SubQuery.ID2
ORDER BY cdr.uniqueid

Open in new window

0
 

Author Comment

by:jjohnson502
ID: 33713815
just timedout. no results.
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 2000 total points
ID: 33816684
Hi,

If you are looking for 872 from dstchannel.

e.g. Local/872@from-internal-f45e

If slash is always coming before extension  and @ coming is after extension then

SELECT
SubString(dstChannel, InStr(dstChannel,"/")+1, InStr(dstChannel,"@")-1)

apart from syntax error, you surely get 872

0
 
LVL 19

Expert Comment

by:Bhavesh Shah
ID: 34029194

Dear Author,

does your problem resolve?
Please update on this...

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

Tech spooks aren't just for those who are tech savvy, it also happens to those of us running a business. Check out the top tech spooks for business owners.
In this article I will be showing you how to subnet the easiest way possible for IPv4 (Internet Protocol version 4). This article does not cover IPv6. Keep in mind that subnetting requires lots of practice and time.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

834 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