t-sql statement one-one match

22 Jun 2006 2:31 PM

Joe K.

How can the sql statement listed below be modified to test the
UserProfiles_Number table that UserID field has only one record in the
PhoneNumber table that corresponds to the UPID field?

I would like to output the records from UserProfiles_Number that do not have
single match between UserID field and UPID from PhoneNumber table?

Please help with this task.

Thank You,

select *
from UserProfiles_Number
where UserID NOT IN (Select UPID from PhoneNumber)

22 Jun 2006 2:39 PM

Tracy McKibben

Joe K. wrote:
Show quote

> How can the sql statement listed below be modified to test the
> UserProfiles_Number table that UserID field has only one record in the
> PhoneNumber table that corresponds to the UPID field?
>
> I would like to output the records from UserProfiles_Number that do not have
> single match between UserID field and UPID from PhoneNumber table?
>
> Please help with this task.
>
> Thank You,
>
> select *
> from UserProfiles_Number
> where UserID NOT IN (Select UPID from PhoneNumber)

SELECT
    UserProfiles_Number.UserID,
    COUNT(PhoneNumber.UPID)
FROM UserProfiles_Number
INNER JOIN PhonNumber
    ON UserProfiles_Number.UserID = PhoneNumber.UPID
GROUP BY UserProfiles_Number.UserID
HAVING COUNT(PhoneNumber.UPID) > 1

22 Jun 2006 2:42 PM

Roy Harvey

select *
from UserProfiles_Number as UP
where 1 =
(select count(*) from PhoneNumber as PN
where UP.UserID = PN.UPID)

Roy Harvey
Beacon Falls, CT

On Thu, 22 Jun 2006 07:31:02 -0700, Joe K. <Joe
K*@discussions.microsoft.com> wrote:

Show quote

>
>How can the sql statement listed below be modified to test the
>UserProfiles_Number table that UserID field has only one record in the
>PhoneNumber table that corresponds to the UPID field?
>
>I would like to output the records from UserProfiles_Number that do not have
>single match between UserID field and UPID from PhoneNumber table?
>
>Please help with this task.
>
>Thank You,
>
>select *
>from UserProfiles_Number
>where UserID NOT IN (Select UPID from PhoneNumber)

22 Jun 2006 3:52 PM

Arnie Rowland

This should work for you. It returns a list of UserProfiles_Number that have MORE than one entry in the PhoneNumber table.

SELECT
     up.UserID
   , count( pn.UPID )
FROM UserProfiles_Number up
   JOIN PhoneNumber pn
      ON up.UserID = pn.UPID
GROUP BY upn.UserID
HAVING count( pn.UPID ) > 1

--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."

*Yet Another Certification Exam

Show quote

"Joe K." <Joe K*@discussions.microsoft.com> wrote in message news:AF5523C9-8F23-4714-AF1E-193841C63BF1@microsoft.com...
>
> How can the sql statement listed below be modified to test the
> UserProfiles_Number table that UserID field has only one record in the
> PhoneNumber table that corresponds to the UPID field?
>
> I would like to output the records from UserProfiles_Number that do not have
> single match between UserID field and UPID from PhoneNumber table?
>
> Please help with this task.
>
> Thank You,
>
> select *
> from UserProfiles_Number
> where UserID NOT IN (Select UPID from PhoneNumber)

23 Jun 2006 6:54 AM

Omnibuzz

Hi Joe,
Your question is a bit confusing :)

>I would like to output the records from UserProfiles_Number that do not have
>single match between UserID field and UPID from PhoneNumber table?

Do you mean you need those records in UserProfiles_Number that has no match
or more than one match in Phone number?

If that is the case, then try this query...

select *
from UserProfiles_Number
where UserID NOT IN (Select UPID from PhoneNumber group by UPID having
count(UPID)= 1)

--
-Omnibuzz (The SQL GC)

http://omnibuzz-sql.blogspot.com/

23 Jun 2006 6:55 AM

Omnibuzz

try this.. if you want all the records that doesn't have a single match...
(it includes no match and more than one match)

select *
from UserProfiles_Number
where UserID NOT IN (Select UPID from PhoneNumber group by UPID having
count(UPID)= 1)