CODEWITHSUNDEEP
sqldata-partitioning
user802599
user802599

Reputation: 838

trying to use partitioning instead of sub querys

I have two tables one in my current system that has the current email, and one that I have created and exported all the email addresses from the Global Address Book, along with a BIT value for which is the primary email address and a user id value. I wrote a query using sub querys to find what email addresses should change, but I though I should be able to do this without using sub querys but couldn't figure out how?

Does anyone know how or if this could be done better without using sub queries?

What I have works but I want to learn how to write SQL better.

I though I should have been able to do this using OVER (Partition BY Member_ID) and MAX(CAST( [primary] AS int)), but spent a few hours trying to figure it out and was unsucsessful. 

User_Emails
{
email varchar(75)
}

eg:
User_Emails
[email protected]
[email protected]
[email protected]
[email protected]

...

Global_Address_Book
{
Member_ID int
primary BIT
email_address varchar(75)
}

eg:
Member_ID        | primary | email_address
1                | 1       | [email protected]
1                | 0       | [email protected]
1                | 0       | [email protected]
2                | 1       | [email protected]
2                | 0       | [email protected]
2                | 0       | [email protected]
2                | 0       | [email protected]
3                | 1       | [email protected]
3                | 0       | [email protected]
4                | 1       | [email protected]

...

SELECT Changed_Email_list.email AS Old_Email, New_Email_List.email_address AS New_Email
FROM (SELECT Member_ID, email
      FROM [Database].[Global_Address_Book] INNER JOIN [Database].[User_Emails]
       ON [Global_Address_Book].[email_address] = [User_Emails].[email]) AS     
      Changed_Email_list LEFT JOIN 
      (SELECT Member_ID, email_address 
       FROM [Database].[Global_Address_Book]
       WHERE [primary] = 1) AS New_Email_List
       ON Changed_Email_list.Member_ID = New_Email_List.Member_ID

Upvotes: 0

Views: 57

Answers (1)

Sankara
Sankara

Reputation: 1479

this should do i guess

select UserEmails.Email as OldEmail,GAB.Email as NewEamil
from   UserEmailsfromAddressBook  
inner join UserEmails on UserEmails.Email=UserEmailsfromAddressBook.Email
      and isprimary =0
inner join UserEmailsfromAddressBook  GAB on GAB.memberId=UserEmailsfromAddressBook.memberID
     and GAB.isprimary=1

test script in sqlfiddle

Upvotes: 2

Related Questions