• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 343
  • Last Modified:

SQL lookup

I have a table with columns:
id, userid, atype, address

atype can only be 1 or 0

I need to do an SQL lookup to SELECT userid WHERE atype = 0 but ... here's the important bit... where there is ONLY one entry in the table for that userid

I tried and failed with this:

SELECT userid, atype
FROM tbladdress
GROUP BY userid, atype
HAVING count(userid)=1 and atype=0

Please help

/ Tobzzz
0
tobzzz
Asked:
tobzzz
  • 6
  • 5
  • 4
  • +1
1 Solution
 
AnuroopsunddCommented:
SELECT userid, atype
FROM tbladdress
where atype =0
GROUP BY userid, atype
HAVING count(userid)=1
0
 
tobzzzAuthor Commented:
thanks - testing this i get 2 or more rows in some cases (where userid has an entry atype=1 once or more than once in addition to 1 instance of atype=0. I need it where there's only 1 single entry in the table for a userid, and that it's atype=0
0
 
AnuroopsunddCommented:
SELECT userid, atype
FROM tbladdress
where count(userid)=1 and atype =0
GROUP BY userid, atype
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Scott PletcherSenior DBACommented:
>> where there is ONLY one entry in the table for that userid <<

Hmm, you mean only one entry with atype = 0, and other rows possible:


SELECT userid, atype
FROM tbladdress
GROUP BY userid, atype
HAVING SUM(CASE WHEN atype = 0 THEN 1 ELSE 0 END) = 1
0
 
Scott PletcherSenior DBACommented:
>> need it where there's only 1 single entry in the table for a userid, and that it's atype=0 <<


SELECT userid, atype
FROM tbladdress
GROUP BY userid, atype
HAVING SUM(CASE WHEN atype = 0 THEN 1 ELSE 0 END) = 1
     AND SUM(CASE WHEN COALESCE(atype, 1) <> 0 THEN 1 ELSE 0 END) = 0
0
 
SANDY_SKCommented:
try this

select userid, atype from (
select count(userid) , atype , userid as cnt from tbladdress GROUP BY userid, atype
) as tab where cnt = 1;
0
 
tobzzzAuthor Commented:
Hi Scott,

Yep, it;s:
>> need it where there's only 1 single entry in the table for a userid, and that it's atype=0 <<
but your query returned userids that have more than 1 row in the database. In fact the first it returned has 2 rows, one with atype=0 and another with atype=1, there must be only one row in the database for a userid and atype=0. Thanks!
0
 
AnuroopsunddCommented:
SELECT userid, atype
FROM tbladdress
where count(userid)=1 and atype =0
GROUP BY userid, atype

what is the result you are getting from above sql statement?
0
 
tobzzzAuthor Commented:
Group By in a statement requires Having clause too so it will throw an error
0
 
AnuroopsunddCommented:
SELECT userid, atype
FROM tbladdress
GROUP BY userid, atype
HAVING (count(userid)=1) and atype=0
0
 
Scott PletcherSenior DBACommented:
I'll re-post my very last post, which I'm pretty sure does what you need:


SELECT userid, atype
FROM tbladdress
GROUP BY userid, atype
HAVING SUM(CASE WHEN atype = 0 THEN 1 ELSE 0 END) = 1
     AND SUM(CASE WHEN COALESCE(atype, 1) <> 0 THEN 1 ELSE 0 END) = 0
0
 
tobzzzAuthor Commented:
Scott and Anuroopsundd,

Your queries bring back the exact same 1,262 results and as I said, unfortunately it's still picking up userid thats have 2 or more rows

so if I had this table:

id    |    userid    |    atype    |
1    |    33    |    0    |
2    |    42    |    0    |
3    |    33    |    1    |
4    |    67    |    1    |
5    |    33    |    1    |
6    |    88    |    0    |
7    |    91    |    0    |
8    |    91    |    1    |

I want this result:

userid    |    atype    |
      42    |     0       |
      88    |     0       |

Thanks
0
 
Scott PletcherSenior DBACommented:
Are you using the LAST version of my query?  The one I re-posted above?
0
 
tobzzzAuthor Commented:
Yes sir, word-for-word except for adding 'ORDER BY userid' as a final line so i can see the results easily
0
 
Scott PletcherSenior DBACommented:
D'OH, (somehow) just noticed "atype" was in the SELECT and GROUP BY, D'OH D'OH:


SELECT userid
FROM tbladdress
GROUP BY userid
HAVING SUM(CASE WHEN atype = 0 THEN 1 ELSE 0 END) = 1
     AND SUM(CASE WHEN COALESCE(atype, 1) <> 0 THEN 1 ELSE 0 END) = 0
ORDER BY userid
0
 
tobzzzAuthor Commented:
That's the stuff! Great job, Scott, thanks. Many thanks to all others for their time.
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 6
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now