a little join help

1 Jul 2005 6:01 PM

Kurt Schroeder

can anyone help me to join these two queries. they each work, but i'd like
just one query.

select count(u.usrSecID) as isthere
from usr u join sec s on s.secID = u.usrSecID
and s.secUserID = 'Kurt'
will gives 3
select count(u2.usrSecID) as isthere
from usr u2 join sec s2 on s2.secID = u2.usrSecID and u2.usrPorDesc = 'kkk'
and s2.secUserID = 'kurt'
will gives 1

each time i try to join them i get 3 and 3 !!!
thank!!!
kes

1 Jul 2005 6:21 PM

Tom Moreau

Check out:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro05/html/sp05a1.asp?frame=true

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..

"Kurt Schroeder" <KurtSchroe***@discussions.microsoft.com> wrote in message
news:41D23724-7EA1-4EDF-999E-9272ABF77401@microsoft.com...

can anyone help me to join these two queries. they each work, but i'd like
just one query.

select count(u.usrSecID) as isthere
from usr u join sec s on s.secID = u.usrSecID
and s.secUserID = 'Kurt'
will gives 3
select count(u2.usrSecID) as isthere
from usr u2 join sec s2 on s2.secID = u2.usrSecID and u2.usrPorDesc = 'kkk'
and s2.secUserID = 'kurt'
will gives 1

each time i try to join them i get 3 and 3 !!!
thank!!!
kes

1 Jul 2005 6:59 PM

Kurt Schroeder

thanks for your help.

I'm sure THIS ONE could be written better!!

select
x.totcnt,
x2.isthere
from sec s
JOIN
(select usrsecID, count(usrSecID) as totcnt
from usr join sec s on s.secID = usrSecID
and s.secUserID = 'Kurt' group by usrSecID) x on x.usrSecID = s.secid
left JOIN
(select usrsecid, count(usrSecID) as isthere
from usr u2 join sec s2 on s2.secID = usrSecID and usrPorDesc = 'kkk'
and s2.secUserID = 'kurt' group by usrSecID) x2 on x2.usrSecID = s.secid

but, it does what i want it to do!
thanks

Show quote

"Tom Moreau" wrote:

> Check out:
>
> http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsqlpro05/html/sp05a1.asp?frame=true
>
> --
> Tom
>
> ----------------------------------------------------
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> ..
> "Kurt Schroeder" <KurtSchroe***@discussions.microsoft.com> wrote in message
> news:41D23724-7EA1-4EDF-999E-9272ABF77401@microsoft.com...
> can anyone help me to join these two queries. they each work, but i'd like
> just one query.
>
> select count(u.usrSecID) as isthere
> from usr u join sec s on s.secID = u.usrSecID
> and s.secUserID = 'Kurt'
> will gives 3
> select count(u2.usrSecID) as isthere
> from usr u2 join sec s2 on s2.secID = u2.usrSecID and u2.usrPorDesc = 'kkk'
> and s2.secUserID = 'kurt'
> will gives 1
>
> each time i try to join them i get 3 and 3 !!!
> thank!!!
> kes
>
>
>

1 Jul 2005 6:36 PM

Christian Donner

"Kurt Schroeder" schrieb:
Show quote

> can anyone help me to join these two queries. they each work, but i'd like
> just one query.
>
> select count(u.usrSecID) as isthere
> from usr u join sec s on s.secID = u.usrSecID
> and s.secUserID = 'Kurt'
> will gives 3
> select count(u2.usrSecID) as isthere
> from usr u2 join sec s2 on s2.secID = u2.usrSecID and u2.usrPorDesc = 'kkk'
> and s2.secUserID = 'kurt'
> will gives 1
>
> each time i try to join them i get 3 and 3 !!!
> thank!!!
> kes

1. You should put your search criteria into a where clause instead of the
join's on clause.
2. Why not using a union between the two queries?

1 Jul 2005 7:01 PM

Kurt Schroeder

Can I union and get the results in one row?

val1 val2
3 1
And you are right the search should be not where it is!
thanks
kes

Show quote

"Christian Donner" wrote:

> "Kurt Schroeder" schrieb:
> > can anyone help me to join these two queries. they each work, but i'd like
> > just one query.
> >
> > select count(u.usrSecID) as isthere
> > from usr u join sec s on s.secID = u.usrSecID
> > and s.secUserID = 'Kurt'
> > will gives 3
> > select count(u2.usrSecID) as isthere
> > from usr u2 join sec s2 on s2.secID = u2.usrSecID and u2.usrPorDesc = 'kkk'
> > and s2.secUserID = 'kurt'
> > will gives 1
> >
> > each time i try to join them i get 3 and 3 !!!
> > thank!!!
> > kes
>
> 1. You should put your search criteria into a where clause instead of the
> join's on clause.
> 2. Why not using a union between the two queries?