We help IT Professionals succeed at work.

Join two Rows in SQL and display results

632 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





Comment
Watch Question

Commented:
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'

Author

Commented:
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

Author

Commented:
sorry forgot to mention that the rows are on the same databse.

Author

Commented:
& same table. the table name is cdr
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
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
Ephraim WangoyaSoftware Engineer
CERTIFIED EXPERT

Commented:
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

Author

Commented:
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'

Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Commented:
:-p  oh.. unforgivable mistake.

I write example base on his description.
forget to check field name versus the image
Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:

:D

It happens....

we are humans

Author

Commented:
lol. Got the query to work but did not get the required output


output.jpg

Commented:
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

Author

Commented:
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

Author

Commented:
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?

Author

Commented:
how would i right a query to filter out 872 from the dstchannel?

Commented:
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

Author

Commented:
just timedout. no results.
Lead Analyst
CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Bhavesh ShahLead Analyst
CERTIFIED EXPERT
Top Expert 2010

Commented:

Dear Author,

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

- Bhavesh
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.