Solved

Join two Rows in SQL and display results

Posted on 2010-09-16
19
574 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 

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

Building an interactive eFuture classroom

Watch and learn how ATEN provided a total control system solution including seamless switching matrix switch, HDBaseT extenders, PDU, lighting control to build an interactive eFuture classroom.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Best website to study network, virtual lan, lan switching 3 64
Strange router problem - can't access hotmail.com 14 106
NFS v4 7 74
patch status tool - free 9 55
Data center, now-a-days, is referred as the home of all the advanced technologies. In-fact, most of the businesses are now establishing their entire organizational structure around the IT capabilities.
Most of the applications these days are on Cloud. Cloud is ubiquitous with many service providers in the market. Since it has many benefits such as cost reduction, software updates, remote access, disaster recovery and much more.
Viewers will learn how to connect to a wireless network using the network security key. They will also learn how to access the IP address and DNS server for connections that must be done manually. After setting up a router, find the network security…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

737 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