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
Solved

Join two Rows in SQL and display results

Posted on 2010-09-16
19
570 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

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

Expert Comment

by:ewangoya
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:ewangoya
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Macbook Sierra OS OpenVPN issue 13 110
How to read network slash info 7 44
Guest Wireless in a Business Environment 6 90
Oracle DB Slows After Datapump Until Next Reboot 27 91
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you're not part of the solution, you're part of the problem.   Tips on how to secure IoT devices, even the dumbest ones, so they can't be used as part of a DDoS botnet.  Use PRTG Network Monitor as one of the building blocks, to detect unusual…
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.
Internet Business Fax to Email Made Easy - With  eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, f…

809 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