?
Solved

Join two Rows in SQL and display results

Posted on 2010-09-16
19
Medium Priority
?
576 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
[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
  • 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
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!

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Short answer to this question: there is no effective WiFi manager in iOS devices as seen in Windows WiFi or Macbook OSx WiFi management, but this article will try and provide some amicable solutions to better suite your needs.
I had an issue with InstallShield not being able to use Computer Browser service on Windows Server 2012. Here is the solution I found.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…

762 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