February 19, 2007 at 5:57 am
I'm currently migrating from Access 2000 to SQL Server.
I got one table with candidates (PK: ID_Candidate).
In another table I have the emailadresses of these people:
ID_Mailadress
ID_Candidate [FK]
Mailadress
primary
the column "primary" contains a bit/boolean value which says if the email adress is the primary mailadress to use, when sending mail automatically. Not every person has a primary mail adress since most of them have just one. But its also possible, that a person has multiple mailadresses and none of them is set to "primary"
What I had in Access was a query like this:
SELECT
ID_Mailadress,
ID_Candidate,
Mailadress,
primary
FROM tblMailadress
ORDER BY tblMailadress.primary;
Now I have all mailadresses, those having a primary = true coming first.
Next I had query based on this query:
SELECT qryOrderedMailadresses.ID_Candidate, Min(qryOrderedMailadresses.primary) AS MinOfPrimary
FROM qryOrderedMailadresses
GROUP BY qryOrderedMailadresses.ID_Candidate;
Now I had a query, showing me all candidate IDs in the mailadresstable once only, showning the ones having a primary = true if existent, plus the "primary"-values. Lets call it qryMinOfPrimary
This query I joined to the mailadresstable again like this:
SELECT
FIRST(tblMailadress.Mailadress) AS EMail, tblMailadress.ID_Candidate
FROM
qryMinOfPrimary
INNER JOIN tblMailadress ON (qryMinOfPrimary .MinOfPrimary = tblMailadress .primary)
AND (qryMinOfPrimary.ID_Candidate = tblMailadress.ID_Candidate)
GROUP BY tblMailadress.ID_Candidate;
So, I had the columns Mailadress and ID_Candidate, for every candidate once only, but if the candidate had a Mailadress set to "primary", this one is selected.
Now in SQL Server 2005, there is no Aggregate Function like "First". I tried different things, but I always get multiple Mailadresses, if the candidate has more than one Mailadress with primary = false (primary = true is only allowed for one adress)
I hope this is the right forum. If you have any further questions, feel free to ask. I'm not a noob anymore, but far from professional, so pls be indulgent if my queries aren't very smart
Thanks in advance!
February 19, 2007 at 7:32 am
I am not quite sure what you want but the following may help you:
-- *** Test Data ***
DECLARE @t TABLE
(
ID_MailAddress int NOT NULL PRIMARY KEY
,ID_Candidate int NOT NULL
,MailAddress varchar(255) NOT NULL
,PrimaryAddress bit NOT NULL -- 0 = false, 1 = true
)
INSERT INTO @t
SELECT 1, 1, 'aaaaa@zzz.com', 0 UNION ALL
SELECT 2, 1, 'bbbbb@zzz.com', 1 UNION ALL
SELECT 3, 2, 'ccccc@zzz.com', 0 UNION ALL
SELECT 4, 2, 'ddddd@zzz.com', 0 UNION ALL
SELECT 5, 3, 'eeeee@zzz.com', 0 UNION ALL
SELECT 6, 4, 'fffff@zzz.com', 1
-- *** End Test Data ***
SELECT T1.ID_Candidate, T1.MailAddress
FROM @t T1
WHERE T1.PrimaryAddress = 1
UNION ALL
SELECT T2.ID_Candidate, T2.MailAddress
FROM @t T2
WHERE EXISTS (
SELECT *
FROM @t T3
WHERE NOT EXISTS (
SELECT *
FROM @t T4
WHERE T4.PrimaryAddress = 1
AND T4.ID_Candidate = T3.ID_Candidate
)
GROUP BY T3.ID_Candidate
HAVING MIN(T3.ID_MailAddress) = T2.ID_MailAddress
)
February 19, 2007 at 8:22 am
I want to select only one mailadress per candidate. If there is a primary adress, thake this, otherwise don't care. Your query does this exactly. Thanks a lot.
Just to get it right:
you first take all primary adresses. Then union them with all adresses being not primary, but are also not in the ones, selected first. These are grouped by the Candidate_IDs, so just one comes out as a result. Ok. Think I got it.
Very nice and much smarter than mine (which wasn't even working).
thanks again!
February 19, 2007 at 8:28 am
If you are not worried about which secondary email to get, then the following will be better:
SELECT T1.ID_Candidate, T1.MailAddress
FROM @t T1
WHERE T1.PrimaryAddress = 1
UNION ALL
SELECT T2.ID_Candidate, MIN(T2.MailAddress)
FROM @t T2
WHERE NOT EXISTS (
SELECT *
FROM @t T3
WHERE T3.PrimaryAddress = 1
AND T3.ID_Candidate = T2.ID_Candidate
)
GROUP BY T2.ID_Candidate
February 19, 2007 at 9:27 am
Thanks again! Works great!
February 20, 2007 at 6:17 am
ROW_NUMBER is a good way to deal with this type of problem. This is guaranteed to return 1 email per candidate, even if you have more than one flagged as primary.
SELECT
ID_Candidate, Email FROM (
SELECT ID_Candidate, Mailadress AS Email,
ROW_NUMBER() OVER (PARTITION BY ID_Candidate ORDER BY [Primary] DESC) AS RowNumber
FROM tblMailadress
) AS x
WHERE RowNumber = 1
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply