Solved

Query with Count Embedded

Posted on 2004-10-08
2
614 Views
Last Modified: 2008-02-26
Here's what I want to do spelled out in "English".  I'm having trouble converting this to SQL ...

SELECT USER_ID, DATE FROM CLASSES_TAKEN WHERE USER_ID IN (SELECT USER_ID FROM AWARDS WHERE USER_ID APPEARS ONLY ONCE IN THE AWARDS TABLE)

Obviously "appears only once in the Awards table" isn't valid SQL Syntax, but that's the spelled out version.

Help!
0
Comment
Question by:UPRRDevelopers
2 Comments
 
LVL 2

Accepted Solution

by:
dramacqueen earned 50 total points
ID: 12259335
Hi UPRRDevelopers,

You need a nested sql something like
SELECT USER_ID, DATE FROM CLASSES_TAKEN WHERE USER_ID IN (
    SELECT USER_ID FROM AWARDS WHERE (USER_ID, 1) in (
        select user_id, count(*) from awards
        group by user_id)
    );
NB you can use this for any particular count of records by changing the '1' in the 2nd line
Hope it helps, have a nice weekend.
0
 
LVL 15

Expert Comment

by:andrewst
ID: 12259389
More simply:

SELECT USER_ID, DATE FROM CLASSES_TAKEN WHERE USER_ID IN (
        select user_id
        from awards
        group by user_id
        having count(*) = 1);
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
exp/imp 25 98
ODBC in excel 2016 in Windows 10 via VBA 16 235
Database Design Dilemma 6 65
UTL_FILE invalid file operation 5 45
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

679 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question