Solved

Join two Rows in SQL and display results

Posted on 2010-09-16
19
567 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
Comment Utility
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
Comment Utility
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
Comment Utility
sorry forgot to mention that the rows are on the same databse.
0
 

Author Comment

by:jjohnson502
Comment Utility
& same table. the table name is cdr
0
 
LVL 32

Expert Comment

by:ewangoya
Comment Utility
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:ewangoya
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
:-p  oh.. unforgivable mistake.

I write example base on his description.
forget to check field name versus the image
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 19

Expert Comment

by:Bhavesh Shah
Comment Utility

:D

It happens....

we are humans
0
 

Author Comment

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


output.jpg
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
how would i right a query to filter out 872 from the dstchannel?
0
 
LVL 11

Expert Comment

by:JoeNuvo
Comment Utility
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
Comment Utility
just timedout. no results.
0
 
LVL 19

Accepted Solution

by:
Bhavesh Shah earned 500 total points
Comment Utility
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
Comment Utility

Dear Author,

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

- Bhavesh
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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.
ADCs have gained traction within the last decade, largely due to increased demand for legacy load balancing appliances to handle more advanced application delivery requirements and improve application performance.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
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…

763 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now