Nested Query Vs Joins

IHello Experts,

I was wondering if the below 2 queries are semantically same. Please let me know if I need to change the JOIN query. I am converting the nested query to a JOIN query.

Please help!!




select * from f_cdr 
where callid in    
	  (select distinct callid 
       from f_cdr 
       where ucid in 
             (select distinct ucid 
	          from IC_DIM_ROUTINGEVENT 
              where contact_key in
		            (select distinct contact_key 
				     from ic_dim_routingevent
					 where contact_key in
                           (SELECT distinct vduid
                            FROM ic_f_contact
                            WHERE vduid = '5239c895000000000a0d6c6823360002'
							)
					)
							
			)
		)

Open in new window


 
SELECT DISTINCT FINAL.*
 FROM F_CDR FINAL
 INNER JOIN F_CDR A
 ON FINAL.callid = A.callid
 INNER JOIN IC_DIM_ROUTINGEVENT B
 ON A.ucid = B.UCID
 INNER JOIN IC_F_CONTACT C
 ON B.contact_key = C.vduid
 AND C.vduid = '5239c895000000000a0d6c6823360002'

Open in new window


Thanks in advance!
ravichand-sqlAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tony303Commented:
I think change the last line to WHERE instead of AND

SELECT DISTINCT FINAL.*
 FROM F_CDR FINAL
 INNER JOIN F_CDR A
 ON FINAL.callid = A.callid
 INNER JOIN IC_DIM_ROUTINGEVENT B
 ON A.ucid = B.UCID
 INNER JOIN IC_F_CONTACT C
 ON B.contact_key = C.vduid
 WHERE C.vduid = '5239c895000000000a0d6c6823360002' 

Open in new window

0
PortletPaulfreelancerCommented:
this isn't sensible:

FROM F_CDR FINAL
INNER JOIN F_CDR A
        ON FINAL.callid = A.callid


I'm not a big fan of aliases like A B C... and prefer something that indicates the origin, often first letters are handy for this
SELECT DISTINCT --<< not a fan of this either
        F.*
FROM f_cdr as F
INNER JOIN IC_DIM_ROUTINGEVENT AS idr
        ON f.ucid = IDR.ucid
INNER JOIN ic_f_contact AS ifc
        ON idr.contact_key = ifc.vduid
WHERE ifc.vduid = '5239c895000000000a0d6c6823360002'
;

Open in new window

{+edit forgot the distinct}
in the original no multiplication of f_cdr rows is possible, but it is possible through joins.

hence that 'distinct' would be required - but if there are many fields from f.* that could be sub-optimal.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
perhaps this
SELECT
        F.*
FROM F_CDR AS F
INNER JOIN (
                SELECT DISTINCT
                        f_cdr.callid
                FROM f_cdr
                INNER JOIN IC_DIM_ROUTINGEVENT AS IDR
                        ON f_cdr.ucid = IDR.ucid
                INNER JOIN ic_f_contact AS IFC
                        ON idr.contact_key = ifc.vduid
                WHERE ic_f_contact.vduid = '5239c895000000000a0d6c6823360002'
           ) AS sq
           ON f.callid = sq.callid
;

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

ravichand-sqlAuthor Commented:
Hello Paul,

I would need couple of columns from ic_f_contact and IC_DIM_ROUTINGEVENT, where do I select them ?
0
PortletPaulfreelancerCommented:
I assume you mean you need to output columns from those 2 tables, in which case you must use joinsand, then it won't really be a precisely equivalent query :)

The query at http:#39519907 uses joins and would permit you to include columns from  ic_f_contact and IC_DIM_ROUTINGEVENT.
0
ravichand-sqlAuthor Commented:
Yes, I want to output few columns from those 2 tables. I understand it wouldn't be the same query. But, can you please help me in modifying your latest query for me ?
0
PortletPaulfreelancerCommented:
Just in case it isn't clear, this is the one :: add only the columns that you require instead of the "F.*". By using only the columns you need you reduce the amount of work that distinct must do.

You don't need help adding columns I presume.
SELECT DISTINCT --<< not a fan of this either
        F.*
FROM f_cdr as F
INNER JOIN IC_DIM_ROUTINGEVENT AS idr
        ON f.ucid = IDR.ucid
INNER JOIN ic_f_contact AS ifc
        ON idr.contact_key = ifc.vduid
WHERE ifc.vduid = '5239c895000000000a0d6c6823360002'
;

Open in new window

If there is "more to it" than just adding columns you might want a new question.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.