SQL Query Inquiry - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SQL Query Inquiry

Author

Topic

sfnotepad
Starting Member

8 Posts

Posted - 2012-02-29 : 18:32:34

I am having the hardest time thinking of the appropriate commands for this particular query.

The ultimate goal of the query is to obtain e-mail addresses by referencing the same ids from two different tables (CHKIN.id and USER.id) based on a date criteria where CHKIN.checkin = '#' and then display e-mail addresses.

What would be the simplest approach?

CHKIN.checkin
CHKIN.id

USER.e-mail
USER.id

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-02-29 : 18:36:47

Please show us a data sample as I did not follow your explanation.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

sfnotepad
Starting Member

8 Posts

Posted - 2012-02-29 : 18:45:03

SELECT [Check_In], [ID]
FROM CHKINS
WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000')

Check_In ID
2011-08-14 09:10:37.133 44321
2011-08-14 09:15:17.037 44320

SELECT [email],[ID]
FROM USERS

Email ID
none@gmail.com 44321
none@gmail.com 44320

I want to use the first query to obtain IDs for a specific time range to reference the exact ID numbers generated in another table to find the associated e-mail addresses of those IDs since [email],[Check_In], and [ID] are all not in the same table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-02-29 : 22:54:59

simple join would do


SELECT c.[Check_In], c.[ID],u.[email]
FROM CHKINS c
JOIN USERS u
ON u.ID = c.ID
WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000')

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sfnotepad
Starting Member

8 Posts

Posted - 2012-03-01 : 01:45:13

Ah, would I be able to add the following?

SELECT c.[Check_In], c.[ID],u.[email]
FROM CHKINS c
JOIN USERS u
ON u.ID = c.ID
WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000')
GROUP BY [ID]
HAVING COUNT(ID) > 10;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 10:56:57

quote:
Originally posted by sfnotepad

Ah, would I be able to add the following?

SELECT c.[Check_In], c.[ID],u.[email]
FROM CHKINS c
JOIN USERS u
ON u.ID = c.ID
WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000')
GROUP BY [ID]
HAVING COUNT(ID) > 10;

nope. once you apply group by you cant return individual values

however you can tweak it like this


SELECT c.[Check_In], c.[ID],u.[email]
FROM CHKINS c
JOIN USERS u
ON u.ID = c.ID
JOIN (SELECT ID
      FROM CHKINS 
      WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000')
      GROUP BY [ID]
      HAVING COUNT(ID) > 10;
     )c1
ON c1.ID = u.ID
WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000')

or this if you're on sql 2005 and above


SELECT c.[Check_In], c.[ID],u.[email]
FROM (SELECT *,COUNT(1) OVER (PARTITION BY [ID]) AS Cnt
      FROM CHKINS
      WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000') 
      )c
JOIN USERS u
ON u.ID = c.ID
AND c.Cnt>10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sfnotepad
Starting Member

8 Posts

Posted - 2012-03-01 : 14:17:57

Would it be possible to expand on the c references after the SELECT subquery? I'm not sure I follow entirely what c1 means.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-01 : 23:03:48

quote:
Originally posted by sfnotepad

Would it be possible to expand on the c references after the SELECT subquery? I'm not sure I follow entirely what c1 means.

c1 is name given to derived table inside which you filter result to return only ones with 10 or above occurance.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sfnotepad
Starting Member

8 Posts

Posted - 2012-03-02 : 16:26:43

Ah, thank you for help! I don't want to seem like a nuisance, but I have one last issue with the aforementioned code. I want to display unique instance of an e-mail rather than displaying instances of 10 e-mail addresses or more for satisfying the time criteria. I tried to use SELECT DISTINCT in the query, but to no avail. I read http://www.sqlteam.com/article/deleting-duplicate-records, but I'm a bit confused.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 16:35:10

sorry how do you decide what email to be shown? is there a rule for that?
please show with a data sample so that we can understand what exactly you're trying to achieve

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sfnotepad
Starting Member

8 Posts

Posted - 2012-03-02 : 16:45:02

2011-08-15 11:08:24.627 48633 none@gmail.com John Smith
2011-08-26 10:54:19.573 48633 '' John Smith
2011-08-29 10:51:43.810 48633 '' John Smith
2011-08-31 10:52:18.583 48633 '' John Smith
2011-09-02 10:56:41.173 48633 '' John Smith
2011-09-07 10:54:13.587 48633 '' John Smith
2011-09-09 10:54:19.887 48633 '' John Smith
2011-09-12 10:53:04.460 48633 '' John Smith
2011-09-16 10:54:27.560 48633 '' John Smith

So with the current query, it will list every ID for individuals with >10 checkins, but I would rather have the query print only unique e-mail addresses rather than every e-mail address of IDs that visited more than 10 times. So - is one method possible that one can simply create a temp table and delete duplicate rows for a specified column?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 20:14:31

this looks like a presntation issue. why cant you handle this at your front end?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sfnotepad
Starting Member

8 Posts

Posted - 2012-03-02 : 21:51:51

I can handle it at my end by using notepad++ find and replace with "^(.+\r?\n)\1+" by "\1 or using a program called Replace Pioneer and using sort_by_char($match,1,-1,'uniq'), but I'd like to know if a method within SQL exists as I lack the necessary SQL skills to construct a method on my own at the moment.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-02 : 22:47:36

for sql the solution would be a bit ugly
like below


SELECT c.[Check_In], c.[ID],CASE WHEN Rn=1 THEN u.[email] ELSE '' END
FROM (SELECT *,COUNT(1) OVER (PARTITION BY [ID]) AS Cnt,ROW_NUMBER() OVER (PARTITION BY [ID] ORDER BY [Check_In]) AS Rn
      FROM CHKINS
      WHERE (checkin >='2011-08-14 00:00:00.000' AND [checkin] < '2011-12-19 00:00:00.000') 
      )c
JOIN USERS u
ON u.ID = c.ID
AND c.Cnt>10

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sfnotepad
Starting Member

8 Posts

Posted - 2012-03-03 : 02:37:11

Thank you for your assistance!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-04 : 10:29:47

wc

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources