Solved

union 2 datasets to fill in empty fields

Posted on 2011-02-15
6
342 Views
Last Modified: 2012-05-11
Can I union 2 select statements so that similar records will combine fields, such as
select names.id, names.first, names.last, '' as address
from tblnames
union
select addrs.nameid, '' as first, '' as last, addrs.address
from addrs
and the result would be like
id   first    last     addr
1     Tom  Jones   123 Maple
2     Jon   Smith     342 1st
etc
I'm trying something similar, but, I get (i've attached my code, but, it's much different...just trying to show what's happening)
id     name     email         phone
1      Tom     1@2.com
1      Tom                         123-345
2      Jon       3@2.com
2      Jon                          121-545
Select	DPid1.WebID, Contact1.Name as DP, Contact1.Email as DPEmail, '' as GM, '' as GMEmail
From	tblsfContact as Contact1 Inner Join
		(
		SELECT	WebIDs.WebID, MIN(contact.sfContactID) as ContactID
		FROM	tblsfInstalledBase AS Prod INNER JOIN
				tblHistWebIDDetails AS WebIDs ON Prod.sfWebID = WebIDs.sfWebID LEFT OUTER JOIN
				tblsfContact AS Contact ON WebIDs.sfAccntID = Contact.sfAccntID
		WHERE	(WebIDs.Period = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) AND (Prod.ProductCode = '14.285.00') AND (WebIDs.HasProCare = 0) AND 
				(WebIDs.HasSearch = 0) AND (Contact.Status = 'Active') AND (Contact.Role LIKE '%Dealer Principal%') AND (Contact.Email IS NOT NULL) 
		GROUP BY WebIDs.WebID
		) as DPid1 on DPid1.ContactID = Contact1.sfContactID
UNION
Select	DPid.WebID, '' as DP, '' as DPEmail, Contact.Name as GM, Contact.Email as GMEmail
From	tblsfContact as Contact Inner Join
		(
		SELECT	WebIDs.WebID, MIN(contact.sfContactID) as ContactID
		FROM	tblsfInstalledBase AS Prod INNER JOIN
				tblHistWebIDDetails AS WebIDs ON Prod.sfWebID = WebIDs.sfWebID LEFT OUTER JOIN
				tblsfContact AS Contact ON WebIDs.sfAccntID = Contact.sfAccntID
		WHERE	(WebIDs.Period = DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0)) AND (Prod.ProductCode = '14.285.00') AND (WebIDs.HasProCare = 0) AND 
				(WebIDs.HasSearch = 0) AND (Contact.Status = 'Active') AND (Contact.Role LIKE '%General Manager%') AND (Contact.Email IS NOT NULL) 
		GROUP BY WebIDs.WebID
		) as DPid on DPid.ContactID = Contact.sfContactID

Open in new window

0
Comment
Question by:avoorheis
  • 3
  • 2
6 Comments
 
LVL 32

Expert Comment

by:ewangoya
ID: 34901093


dont use UNION, use inner join and isnull
example

select ISNULL(A.FirstName, B.FirstName) FirstName, ISNULL(A.LastName, B.LastName) LastName
from People A
inner join People B on B.ID = A.ID
0
 

Author Comment

by:avoorheis
ID: 34901121
thanks, I'll try....so, union doesn't do that, am I confused with my Access days (or just totally confused)?
0
 
LVL 32

Expert Comment

by:ewangoya
ID: 34901174
Union will just leave null spaces in between
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34901201
not UNION... you may need a FULL OUTER JOIN  (if data is not guaranteed to be on both tables..


select coalesce(a.id,b.nameid) as id
     , a.first, a.last, b.address
from tblnames as a
full outer join addrs as b
 on a.id=b.nameid
select addrs.nameid, '' as first, '' as last, addrs.address
from addrs

Open in new window

0
 

Author Comment

by:avoorheis
ID: 34901228
hmmmm....
I think I have something different. I'm trying to add fields to the first dataset. So,
select names.id, names.first, names.last, '' as address
from tblnames
union
select addrs.nameid, '' as first, '' as last, addrs.address
from addrs
tblnames does not have a field addrs, so, I want to "append" it on to the results, so, that the list from tblnames will also include the addresses.
0
 

Author Comment

by:avoorheis
ID: 34901311
thanks lowfat, full outer is working
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

758 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

19 Experts available now in Live!

Get 1:1 Help Now