Re: sql union self-join syntax

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ken Snell [MVP] (kthsneisllis9_at_ncoomcastt.renaetl)
Date: 03/13/05 

Date: Sat, 12 Mar 2005 21:58:26 -0500

 By the way, this query is designed to bring back only the records where the
person has no "significant other" or only one person from the "significant
other" partnership. After you confirm that this query does exactly this,
then we will work on getting the name info for the "significant other" for
your setup. I don't think we'll need to use the concatenate function to do
this.... 

-- 
        Ken Snell
<MS ACCESS MVP>
"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
news:ORcxYZ3JFHA.1528@TK2MSFTNGP09.phx.gbl...
> sigh... and then I left in an extraneous parenthesis....
>
> SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
> [LastName] AS [Member Name], Nz(c1.[CompanyName]," ") AS
> [Company Name], Nz(c1.[ContactNickName],c1.
> [ContactFirstName]) & " " & c1.[ContactLastName] AS
> ContactName, c1.[MailingAddress] & ", "+c1.[OptAddress] AS
> Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
> c1.PostalCode, c1.SignificantOtherID, c1.SignificantOtherID
> FROM Contacts AS c1
> WHERE c1.SignificantOtherID > c1.ContactID Or
> c1.SignificantOtherID Is Null
> ORDER BY c1.PostalCode;
>
> -- 
>
>        Ken Snell
> <MS ACCESS MVP>
>
>
> "Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl> wrote in message 
> news:%23LcTFP3JFHA.2212@TK2MSFTNGP12.phx.gbl...
>> You changed the WHERE statement that I'd suggested you use. Try this:
>>
>> SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
>> [LastName] AS [Member Name], Nz(c1.[CompanyName]," ") AS
>> [Company Name], Nz(c1.[ContactNickName],c1.
>> [ContactFirstName]) & " " & c1.[ContactLastName] AS
>> ContactName, c1.[MailingAddress] & ", "+c1.[OptAddress] AS
>> Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
>> c1.PostalCode, c1.SignificantOtherID, c1.SignificantOtherID
>> FROM Contacts AS c1
>> WHERE c1.SignificantOtherID > c1.ContactID Or
>> c1.SignificantOtherID) Is Null
>> ORDER BY c1.PostalCode;
>>
>>
>> Let's get the records correct here, and then we can turn to the 
>> Concatenate function that you suggest using in a later post, ok?
>>
>> -- 
>>
>>        Ken Snell
>> <MS ACCESS MVP>
>>
>>
>> "SillySally" <silsal@discussions.microsoft.com> wrote in message 
>> news:657c01c5276b$67e8a150$a501280a@phx.gbl...
>>> Ken, you came back for more!  Thanks for the posts- using
>>> your code with a slight modification, I was able to bring
>>> back all of the records I'm looking for.  Sort of- I
>>> brought back "duplicate" records, but don't know what to
>>> do with them.  John Doe ContactID=7 is married to Jane
>>> Deer ContactID=8.  That means that in John Doe's record,
>>> SignificantOtherID=8 (linking him to Jane); for Jane Deer,
>>> SignificantOtherID=7 (linking her to John).  For their
>>> singular mailing label,I want:
>>> Jane Doe
>>> John Deer
>>> Address
>>> City, State Zip
>>>
>>> How can I handle this?  Thanks.
>>>
>>> SELECT Nz(c1.[NickName],c1.[FirstName]) & " " & c1.
>>> [LastName] AS [Member Name], Nz(c1.[CompanyName]," ") AS
>>> [Company Name], Nz(c1.[ContactNickName],c1.
>>> [ContactFirstName]) & " " & c1.[ContactLastName] AS
>>> ContactName, c1.[MailingAddress] & ", "+c1.[OptAddress] AS
>>> Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
>>> c1.PostalCode, c1.SignificantOtherID, c1.SignificantOtherID
>>> FROM Contacts AS c1
>>> WHERE (((c1.SignificantOtherID) Is Not Null Or
>>> (c1.SignificantOtherID) Is Null))
>>> ORDER BY c1.PostalCode;
>>>
>>>
>>>
>>>>-----Original Message-----
>>>>And, upon further reading, the DISTINCT modifier should
>>> be unnecessary, and
>>>>I find a few other typos:
>>>>
>>>>SELECT Nz(c1.[NickName],c1.[FirstName]) & " " &
>>>>c1.[LastName] AS [Member Name], Nz(c1.[CompanyName]," ")
>>> AS
>>>>[Company Name], Nz(c1.[ContactNickName],
>>>>c1.[ContactFirstName]) & " " & c1.[ContactLastName]) AS
>>> [Contact
>>>>Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS
>>>>Address, c1.City, UCase(c1.[StateOrProvince]) AS State,
>>>>c1.PostalCode
>>>>FROM Contacts c1
>>>>WHERE c1.significantotherid IS NULL OR
>>>>c1.significantotherid > c1.contactid
>>>>ORDER BY c1.PostalCode;
>>>>
>>>>-- 
>>>>
>>>>        Ken Snell
>>>><MS ACCESS MVP>
>>>>
>>>>
>>>>"Ken Snell [MVP]" <kthsneisllis9@ncoomcastt.renaetl>
>>> wrote in message
>>>>news:ObkCxH0JFHA.2648@TK2MSFTNGP14.phx.gbl...
>>>>>I am not sure if my first reply will be all the records
>>> that you're
>>>>>seeking. I take it that the Contacts table contains a
>>> field
>>>>>(significantohterid) that is filled in if the person in
>>> that record has a
>>>>>partner? If so, how do you plan to "weed out" that
>>> partner when the partner
>>>>>also is a record in the table -- is the assumption that
>>> the two records
>>>>>will have some other commonality that would allow a
>>> union query to get rid
>>>>>of duplicates? That can't be done when you're returning
>>> names in the union
>>>>>query's results (I assume that the names of the two
>>> partners are not
>>>>>exactly the same < g >).
>>>>>
>>>>> If it doesn't matter which 'significant other' person
>>> gets the letter,
>>>>> then perhaps this query will do what you seek. It
>>> selects records with no
>>>>> value in the significantotherid field and records where
>>> the "id" value in
>>>>> the significantotherid field is greater than the "id"
>>> value in the
>>>>> record's contactid field (this is an arbitrary way to
>>> select just one of
>>>>> the two records for the two related people):
>>>>>
>>>>> SELECT DISTINCT c1.Nz([NickName],[FirstName]) & " " &
>>>>> [LastName] AS [Member Name], c1.nz([CompanyName]," ") AS
>>>>> [Company Name], c1.nz(Nz([ContactNickName],
>>>>> [ContactFirstName]) & " " & [ContactLastName]) AS
>>> [Contact
>>>>> Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS
>>>>> Address, c1.City, c1.UCase$([StateOrProvince]) AS State,
>>>>> c1.PostalCode
>>>>> FROM Contacts c1
>>>>> WHERE c1.significantotherid IS NULL OR
>>>>> c1.significantotherid > c1.contactid
>>>>> ORDER BY c1.PostalCode;
>>>>>
>>>>> -- 
>>>>>
>>>>>        Ken Snell
>>>>> <MS ACCESS MVP>
>>>>>
>>>>>
>>>>>
>>>>>
>>>>> "SillySally" <silsal@discussions.microsoft.com> wrote
>>> in message
>>>>> news:64bd01c5273b$e449c240$a501280a@phx.gbl...
>>>>>> Hi.  I can't seem to get my syntax correct.  I hope the
>>>>>> first part of the union query will give me everyone
>>> with a
>>>>>> SignificantOtherID (avoiding duplicates) and the second
>>>>>> part will give me everyone without a Significant Other.
>>>>>> I'm trying to make mailing labels without sending
>>>>>> duplicates to the married/SO people.  Thanks, Sal
>>>>>>
>>>>>> SELECT DISTINCT c1.Nz([NickName],[FirstName]) & " " &
>>>>>> [LastName] AS [Member Name], c1.nz([CompanyName]," ")
>>> AS
>>>>>> [Company Name], c1.nz(Nz([ContactNickName],
>>>>>> [ContactFirstName]) & " " & [ContactLastName]) AS
>>> [Contact
>>>>>> Name], c1.[MailingAddress] & ", "+c1.[OptAddress] AS
>>>>>> Address, c1.City, c1.UCase$([StateOrProvince]) AS
>>> State,
>>>>>> c1.PostalCode
>>>>>> FROM Contacts c1, contacts c2
>>>>>> WHERE ((((Contacts.Newsletter)=Yes)), c1.contactid =
>>>>>> c2.significantotherid)
>>>>>> ORDER BY Contacts.PostalCode
>>>>>> union
>>>>>> SELECT Nz([NickName],[FirstName]) & " " & [LastName] AS
>>>>>> [Member Name], nz([CompanyName]," ") AS [Company
>>> Name], nz
>>>>>> (Nz([ContactNickName],[ContactFirstName]) & " " &
>>>>>> [ContactLastName]) AS [Contact Name], [MailingAddress]
>>>>>> & ", "+[OptAddress] AS Address, City,
>>>>>> UCase$([StateOrProvince]) AS State, PostalCode
>>>>>> FROM Contacts
>>>>>> WHERE ((SignificantOtherId) Is Null);
>>>>>
>>>>>
>>>>
>>>>
>>>>.
>>>>
>>
>>
>
>

Relevant Pages