Return only 1 row per unique ID

Hello All;

OK, This is for our mail script.
What is going on is the following.

We are dealing with 4 tables, to make this work.
(ASkUser) (Lists) (SubCategories) (Media)

What all this does.
If USER
Has joined any Sub-Categories, the UNIQUE ID for them will be stored in the Lists table.

The SubCategories Tables, has a child called Media table
When there are records in the Media Table.
Then an email is generated out to all people that are in the Lists table for the SubCategories.

So.
Right now, I get 2 to 10 emails all duplicated, depending on the user.
So, what I need is the following.

Look through the table "AskUser"
And only send out to the UNIQUE ID, ONE time.
Instead of multiple emails.

Here is the code that I have.
Thanks
Carrzkiss

SELECT AskUser.askid, SubCategories.SubName, Media.MedName, Media.SCID, Media.MedID, Media.Closed, Media.MedDate
FROM  AskUser INNER JOIN
Lists ON AskUser.askid = Lists.AskID INNER JOIN
Media ON Lists.SCID = Media.SCID INNER JOIN
SubCategories ON Media.SCID = SubCategories.SCID
WHERE     
(Media.Closed = 0) AND 
(Media.MedDate > CONVERT(DATETIME, '2011-12-16 00:00:00', 102))

Open in new window


Update
To add to this.
I also tried MIN/MAX
And they did not work in this case. Unless I am just not doing something right this early morning or late at night. 4:31am here, need rest :)
LVL 31
Wayne BarronAuthor, Web DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

JamesMcBrideCommented:
I don't really know what your data looks like, but at a guess you'd need to group everything that you aren't wrapping in min / max.
eg:

SELECT AskUser.askid, SubCategories.SubName, Media.MedName, min(Media.SCID), min(Media.MedID), Media.Closed, Media.MedDate
FROM  AskUser INNER JOIN
Lists ON AskUser.askid = Lists.AskID INNER JOIN
Media ON Lists.SCID = Media.SCID INNER JOIN
SubCategories ON Media.SCID = SubCategories.SCID
WHERE     
(Media.Closed = 0) AND 
(Media.MedDate > CONVERT(DATETIME, '2011-12-16 00:00:00', 102))

group by AskUser.askid, SubCategories.SubName, Media.MedName,  Media.Closed, Media.MedDate

Open in new window

0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
It automatically groups, when you do a MIN/MAX
And unfortunately, that still does not work.
Thanks for the try.

Carrzkiss
0
skullnobrainsCommented:
group by askuser.askid
0
The Five Tenets of the Most Secure Backup

Data loss can hit a business in any number of ways. In reality, companies should expect to lose data at some point. The challenge is having a plan to recover from such an event.

LowfatspreadCommented:
select askid,subname,medname,scid,medid,colsed,meddate
  from (
SELECT AskUser.askid, SubCategories.SubName, Media.MedName
     , Media.SCID, Media.MedID, Media.Closed, Media.MedDate
     ,row_number() over (partition by askuser.askid order by askuser.askid) as rn
FROM  AskUser
INNER JOIN Lists
  ON AskUser.askid = Lists.AskID
INNER JOIN Media
  ON Lists.SCID = Media.SCID
INNER JOIN SubCategories
  ON Media.SCID = SubCategories.SCID
WHERE (Media.Closed = 0)
  AND (Media.MedDate > CONVERT(DATETIME, '2011-12-16 00:00:00', 102))
 ) as x
where rn=1
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Hey Lowfatspread.
Been a long time, hope you are doing well.
Your code as usual, worked great.

Thank you and have a Merry Christmas.
Carrzkiss
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@Lowfatspread
Would you mind explaining to me, and others that may come into this thread.
Exactly how it is that you came up with the creation of the code you provided here?
It would really help out myself and others.

Thank You
Carrzkiss
0
skullnobrainsCommented:
<quote>
It automatically groups, when you do a MIN/MAX
And unfortunately, that still does not work.
Thanks for the try.
</quote>

the automatic group by will be performed on ALL the columns but the one you used for the min/max/avg/... function which is not what you want

grouping on all other columns will only squeeze together lines where all other columns are identical. this is also the reason why the first post did not work.

unless i'm mistaken, lowfatspread's solution works as well and adds the guaranty that the colums that are not used in the group by clause will not be filled with data from random rows (well not really random, since they should at least be in the set of corresponding rows)

if order by worked properly in subqueries, you would even be able to get the same row each time
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
Thanks of the incite Skull.
This information I already knew, however, it would not give me the results that I needed.

Thanks anyway, Lowfats code performed as needed.
Thank You
Carrzkiss
0
LowfatspreadCommented:
Hello All;

OK, This is for our mail script.
What is going on is the following.

We are dealing with 4 tables, to make this work.
(ASkUser) (Lists) (SubCategories) (Media)

What all this does.
If USER
Has joined any Sub-Categories, the UNIQUE ID for them will be stored in the Lists table.

The SubCategories Tables, has a child called Media table
When there are records in the Media Table.
Then an email is generated out to all people that are in the Lists table for the SubCategories.

So.
Right now, I get 2 to 10 emails all duplicated, depending on the user.
So, what I need is the following.

Look through the table "AskUser"
And only send out to the UNIQUE ID, ONE time.
Instead of multiple emails.

Here is the code that I have.
Thanks
Carrzkiss


the underlined point was what i keyed onto...

taking you query as the basic process ... it has the relationships pre defined...
it was obvious that you wanted to select a single row for each user...
so using row_number is an easy way to achieve that... with correct data associations for
the other columns...

select askid,subname,medname,scid,medid,colsed,meddate
  from (
SELECT AskUser.askid, SubCategories.SubName, Media.MedName
     , Media.SCID, Media.MedID, Media.Closed, Media.MedDate
     ,row_number() over (partition by askuser.askid order by askuser.askid) as rn
FROM  AskUser
INNER JOIN Lists
  ON AskUser.askid = Lists.AskID
INNER JOIN Media
  ON Lists.SCID = Media.SCID
INNER JOIN SubCategories
  ON Media.SCID = SubCategories.SCID
WHERE (Media.Closed = 0)
  AND (Media.MedDate > CONVERT(DATETIME, '2011-12-16 00:00:00', 102))
 ) as x
where rn=1    -- take first row of each ASKID

-- you question didn't identify any preference for how to select the other associated information...

I don't think i'd personally have written it this way myself...
if i just wanted a list of people to mail... then i'd probably have used an exists query...

more like this

SELECT distinct askid
FROM  Lists 
where exists (select askid
 from JOIN Media  
INNER JOIN SubCategories 
  ON Media.SCID = SubCategories.SCID
WHERE (Media.Closed = 0)
  AND Media.MedDate > '20111216'
  and Lists.SCID = Media.SCID 
) 

Open in new window


and if i needed information about the media items changed i'd have concatenated the information together somehow...
probably using a for xml path('') subquery....
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
That works as well.

(Had to remove a JOIN) :)

SELECT distinct askid
FROM  Lists 
where exists (select askid
 from Media  
INNER JOIN SubCategories 
  ON Media.SCID = SubCategories.SCID
WHERE (Media.Closed = 0)
  AND Media.MedDate > '20111216'
  and Lists.SCID = Media.SCID 
)

Open in new window


What happens, is when the mail is generated, which "WILL" be done on every Saturday, for the last weeks collection.
A list of all articles are sent to those that are subscribed.
Which will contain, the columns of which I provided in my original code.

Everything seems to be working as it needs to work.
I just have to fix one issue that has me plagued, and that is.
When the user is not subscribed to a certain Topic, then they do not get that in their email.
It works ONLY if that person is the only person in the list, if more people are in the list, then everyone receives the same data.
So. Once that part is fixed, then it will be completed.

Thanks once again lowfat.
Once again, your coding work was much appreciated and well received.

Carrzkiss
0
Wayne BarronAuthor, Web DeveloperAuthor Commented:
@lowfatspread
Could you please have a look at the following thread for me?
http://www.experts-exchange.com/Q_27537846.html
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.