Query help - multiple where in inline select

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

Query help - multiple where in inline select

Author

Topic

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 11:08:12

can someone help with this, im pretty new to sql. this is what i have so far.

select p.user_id,
(select email_id from user_profile_master where user_id=p.user_id)
from user_master p
where rec_create_date >=(trunc(sysdate)-30)

this selects all users added in the last 30 days, it shows user id and email address.
I want to be able to
1.only select email addresses that end in abc.com
2.select just count so that a number shows of how many added instead of showing everything. i tried select count (*) p.user id, but that did not work.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:14:48

Is this a SQL Server query in first place? Also I am not sure about your 2nd requirement.

Harsh Athalye
http://www.letsgeek.net/

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 11:19:50

it is sql.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 11:31:49

sql is a generic query language. Which database product are you working on?

Harsh Athalye
http://www.letsgeek.net/

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 11:34:01

sql database.

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 11:43:02

I figured out first part almost.
select p.user_id,
(select email_id from user_profile_master where email_id like '%abc.com%' and user_id=p.user_id )
from user_master p
where rec_create_date >=(trunc(sysdate)-30)

only thing is it shows nulled results for other email addresses.

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 11:48:56

ok, got it .

select
p.user_id,
p.email_id,
u.rec_create_date,
u.first_name,
u.last_name,
u.login_name
from
user_profile_master p,
user_master u
where u.user_id=p.user_id and p.email_id like '%abc.com%' and u.rec_create_date >=(trunc(sysdate)-30)

do you know how I can just select count now ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:03:11

Maybe this?

select
u.user_id,
count(p.email_id)
from 
user_profile_master p,
user_master u
where u.user_id=p.user_id and p.email_id like '%abc.com' and u.rec_create_date >=(trunc(sysdate)-30)
group by u.user_id

Harsh Athalye
http://www.letsgeek.net/

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 12:20:55

thanks harsh, but that did not work.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-31 : 12:22:08

Could you please explain your second requirement more clearly and why my approach didn't work?

Harsh Athalye
http://www.letsgeek.net/

-jay-
Starting Member

14 Posts

Posted - 2010-03-31 : 12:23:52

no worries. hahaha, i just found it, very simple.

select
count (*)
from
user_profile_master p,
user_master u
where u.user_id=p.user_id and p.email_id like '%ge.com%' and u.rec_create_date >=(trunc(sysdate)-30)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources