SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SQL - JOIN using UNION ?? UNION using JOIN ?? with
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

user811568
Starting Member

USA
1 Posts

Posted - 07/05/2011 :  19:20:06  Show Profile  Reply with Quote


I was asked this question during one of my interviews. Can you do JOIN using UNION keyword? Can you do UNION using JOIN keyword?

That is - 1. I should get same output as JOIN without using JOIN keyword, but using UNION Keyword? 2. I should get same output as UNION without using UNION keyword, but using JOIN Keyword?

Can you give me an example of how to do this if possible?

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 07/05/2011 :  20:15:51  Show Profile  Reply with Quote
There may be contrived examples where one could get the same result set from a UNION as one can from a JOIN, but I don't know.

UNION and JOIN serve completely different purposes - one unions two result sets, the other joins two (or more) tables allowing you to, for example retrieve columns from both tables that match the join conditions.
Go to Top of Page

EZHILARASAN
Starting Member

2 Posts

Posted - 07/06/2011 :  08:55:28  Show Profile  Reply with Quote
select A.* from (
select
client.CLIENTID,
campaign.CAMPAIGNID,
banner.BANNERID,
affiliate.AFFILIATEID,
zone.ZONEID,

ifnull(client.clientname,'') ADVERTISER,
ifnull(campaign.campaignname,'') CAMPAIGN,
ifnull(banner.description,'') BANNER,
ifnull(affiliate.name,'') PUBLISHER,
ifnull(zone.zonename,'') ZONE,
sum(ifnull(ad_hourly.clicks,0)) CLICKS,
sum(ifnull(ad_hourly.impressions,0)) IMPRESSION,
sum(ifnull(ad_hourly.requests,0)) CTR,
sum(ifnull(ad_hourly.conversions,0)) CONVERSION
from
ox_clients client
join ox_campaigns campaign on client.clientid = campaign.clientid
join ox_banners banner on campaign.campaignid = banner.campaignid
join ox_ad_zone_assoc zone_assoc on banner.bannerid = zone_assoc.ad_id
join ox_affiliates affiliate on affiliate.affiliateid = zone_assoc.link_type
join ox_zones zone on zone.zoneid = zone_assoc.zone_id and zone.affiliateid = affiliate.affiliateid
left outer join ox_data_summary_ad_hourly ad_hourly on zone.zoneid = ad_hourly.zone_id and zone_assoc.ad_id = ad_hourly.ad_id
where campaign.weight > 0 AND date_time like '2010-06-25%'
group by
client.clientname,
campaign.campaignname,
banner.description,
affiliate.name,
zone.zonename
with rollup ) A
where A.ADVERTISER <> ''
order by A.ADVERTISER,
A.CAMPAIGN,
A.BANNER,
A.PUBLISHER,
A.ZONE
Go to Top of Page

EZHILARASAN
Starting Member

2 Posts

Posted - 07/06/2011 :  08:56:49  Show Profile  Reply with Quote
CAN ANY ONE SAY HOW TO SUM THE VALUE BY DIFFERENT FIELD
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 07/06/2011 :  09:15:10  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
Were you given a specific example?
A join extends the width of the resultset and a union extends the cardinality.
A general replacement of joins with union or vice-versa isn't possible.

Maybe that's what you were meant to say?


==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15676 Posts

Posted - 07/06/2011 :  09:20:37  Show Profile  Visit robvolk's Homepage  Reply with Quote
Ezhilarasan-

Do not hijack someone else's thread, post a new thread for your questions.
Go to Top of Page

Asif5566
Starting Member

9 Posts

Posted - 07/06/2011 :  09:49:43  Show Profile  Reply with Quote
quote:
Originally posted by EZHILARASAN

CAN ANY ONE SAY HOW TO SUM THE VALUE BY DIFFERENT FIELD



Hello, If I understand u correctly,

You can do it using:
'WITH CUBE' statement after Group By.

Also, u can Group By (showing only Summed up values) using another clause called GROUPING SETS.

I had experimented with these two a while back and seemed quite useful.
By googling u can have good tutorials on these.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.39 seconds. Powered By: Snitz Forums 2000