How Do I Create a Subquery ? - SQL Server Forums

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

How Do I Create a Subquery ?

Author

Topic

liamfitz
Starting Member

10 Posts

Posted - 2012-06-03 : 05:53:41

I suspect the best way to get the Recordset I seek, is as follows. I have a Query ( qrySelectClient_IDByStaff ) which retrieves a single column of DISTINCT Client_ID Numbers i.e. No repetition of this ID Number, although there may be more than one instance of it in the target table, which I want to base another Query on. I need to select ALL Clients names whose Client_ID, is in the result of this query. e.g SELECT Forename, Surname From tblCLIENTS WHERE Client_ID = ( ALL the Client_IDs in my 'qrySelectClient_IDByStaff' recordset ) Any help much appreciated.

ljfitz

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-06-03 : 09:40:25

quote:
Originally posted by liamfitz

I suspect the best way to get the Recordset I seek, is as follows. I have a Query ( qrySelectClient_IDByStaff ) which retrieves a single column of DISTINCT Client_ID Numbers i.e. No repetition of this ID Number, although there may be more than one instance of it in the target table, which I want to base another Query on. I need to select ALL Clients names whose Client_ID, is in the result of this query. e.g SELECT Forename, Surname From tblCLIENTS WHERE Client_ID = ( ALL the Client_IDs in my 'qrySelectClient_IDByStaff' recordset ) Any help much appreciated.

ljfitz

Here is one option:


SELECT c.Forename
     , c.Surname
  FROM tblCLIENTS           c
 WHERE EXISTS (SELECT c2.Client_ID
                 FROM qrySelectClient_IDByStaff     c2
                WHERE c2.Client_ID = c.Client_ID)

Another option - using IN would be:


SELECT c.Forename
     , c.Surname
  FROM tblCLIENTS           c
 WHERE c.Client_ID IN (SELECT c2.Client_ID
                         FROM qrySelectClient_IDByStaff     c2)

Jeff

liamfitz
Starting Member

10 Posts

Posted - 2012-06-03 : 11:46:07

Thank you v.much. I'll try it now and let you know how it went.

ljfitz

liamfitz
Starting Member

10 Posts

Posted - 2012-06-03 : 19:33:28

Both of these suggestions provide the recordset I require ( and allowing additions, most importantly ). So thank you. However, I'm also prompted for the fields Forename and Surname before loading the form correctly ( these prompts can be ignored 'cancelled', or type anything and the same result occurs ).

ljfitz

liamfitz
Starting Member

10 Posts

Posted - 2012-06-03 : 19:39:52

Here's the actual SQl : SELECT c.Title, c.Forename, c.Surname
FROM tblClients AS c
WHERE c.Client_ID IN (SELECT c2.Client_ID
FROM qrySelectDistinctClient_IDByStaff c2)
ORDER BY c.Forename, c.Surname;

It would be perfect without the 'parameter' prompts !

ljfitz

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-06-04 : 13:41:15

It sounds like you are using Access and not SQL Server. If that is the case, I would need to see the qrySelectClient_IDByStaff to see how to embed that into the above queries.

liamfitz
Starting Member

10 Posts

Posted - 2012-06-05 : 11:07:39

Thank you for your insight. I am as you say, designing for Access 2010 ( with a view to adapting the DB to a Client/Server environment ) The 'qrySelectClient_IDByStaff' in full is : SELECT DISTINCT tblReferrals.Client_ID
FROM tblReferrals;
DISTINCT seems to be the catalyst ( which I first used in the main query, rather than the above, which I use as a subquery ). This creates exactly the Recordset(s) I require, but also blocks additions to it ( i.e. tested by running the query stand-alone, and used programatically to populate a form in VBA. Any suggestions ?

ljfitz

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2012-06-05 : 14:27:41

Okay - I am not sure what the issue you are having is. Instead of using the query as your subquery, I would recommend using this:


SELECT c.Title, c.Forename, c.Surname
FROM tblClients AS c
WHERE c.Client_ID IN (SELECT DISTINCT r.Client_ID FROM tblReferrals AS r)

Not sure what you mean when you says it blocks additions. Using a query with DISTINCT will not be updatable because you cannot determine which - of possible many - rows to be updated.

liamfitz
Starting Member

10 Posts

Posted - 2012-06-05 : 14:35:39

I will of course try this. Thanks. I suspected that is why it is not allowing ADDITIONS ( as there may be other records not included in record set, so Access cannot ensure validation rules are met against them, when editing/adding to DB ) I'll need to look at this again, as I cannot see there is any other way of 'noramlising' the relationships/tables involved.

ljfitz

liamfitz
Starting Member

10 Posts

Posted - 2012-06-05 : 15:11:47

I see what you're saying now I think !..... Without making any reference to 'tblReferrals', in my query, but just including it in the design view of the Query, it showed repetition of Client records i.e an instance of the client's details for EVERY referral they had. By taking it away, it returned a recordset of DISTINCT Clients, and accepted additions. Thanks for your questions/ideas.

ljfitz

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources