Re: Finding Records

From: Geoff Jones (nodamnspam_at_email.com)
Date: 01/26/05 

Date: Wed, 26 Jan 2005 11:32:18 -0000

Thanks guys!

"Stephen Howe" <stephenPOINThoweATtns-globalPOINTcom> wrote in message
news:%23Bba4dvAFHA.1296@TK2MSFTNGP10.phx.gbl...
>> Can anybody give me some advice on the following?
>>
>> Suppose I have two tables: A and B. There is a one to many relationship
>> between A and B. I would like to find all the child rows in B for a given
>> row in A. Then, from these returned rows, I would like to find all the
> rows
>> which satisfy a given criteria.
>>
>> With me so far? :)
>
> Yes. Use SQL. You don't want to ever do in code what can be done in SQL.
> The SQL engine for most databases is more powerful than what hand-coding
> with loops could achieve. So use it :-)
>
> If you find yourself iterating through Recordsets, comparing doing finds
> etc, this will normally always be a poorer solution than 1 SQL command
> that
> achieves the same results. Same for INSERTs, DELETEs, UPDATEs even if
> complex. So you want to learn how to express yourself in SQL. It is very
> powerful.
>
> For this problem you want an (INNER) JOIN and a WHERE clause
>
> You don't say what you want to do with the rows you get back. I assume you
> want to see them, so SELECT
>
> SELECT A.field1, A.field2, A.fieldN, B.field1, B.field2
> FROM A JOIN B ON A.fieldincommon1=B.fieldincommon2
> [WHERE yourcriteria]
> [ORDER BY 1,2,3]
>
> should do it.
>
> (i) In the SELECT clause put whatever fields you want to see returned
> (ii) In the optional WHERE clause specify your criteria for restriction.
> Without a WHERE clause all matching rows are returned
> (iii) In the optional ORDER clause 1,2,3 means order by the columns
> specified in the SELECT clause. You can do
>
> ORDER BY A.field1, A.field2, A.fieldN
>
> instead. If you don't care about ORDER, records may come back in any old
> order.
>
> In ADO, if this was a Recordset Open() or Command Execute, you will get
> back
> a recordset consisting of the fields you select and only records that
> match
> the WHERE clause.
> For each entry in A you will get back as many B entries that match and A
> fields will be duplicated for that A entry but the B fields in the SELECT
> will be what matches the A entry.
>
> Stephen Howe
>
>

Relevant Pages

  • Re: Finding Records
    ... You don't want to ever do in code what can be done in SQL. ... In the SELECT clause put whatever fields you want to see returned ... In the optional WHERE clause specify your criteria for restriction. ... fields will be duplicated for that A entry but the B fields in the SELECT ...
    (microsoft.public.data.ado)
  • Re: the "having" clause
    ... Start in the FROM clause and build a working table from all of the ... rest of the containing query. ... e) Go to the SELECT clause and construct the expressions in the list. ... As you can see, things happen "all at once" in SQL, not from left to ...
    (microsoft.public.sqlserver.programming)
  • Re: Report Parameters - no results
    ... Allen Browne - Microsoft MVP. ... I did use the WHERE clause you suggested, and now I'm getting the "wrong ... > What else can I do to try to correct my sql? ... >> the WHERE clause in your query. ...
    (microsoft.public.access.reports)
  • =?iso-8859-1?Q?Re:_Abfrage_meherer_MWSt.-S=E4tze?=
    ... der bereits Rechnungen mit x ) Positionen und mehreren Mehrwertsteuersätzen sinnvoll zu gruppieren und zu summieren hatte? ... Obwohl die Hilfe mindestens 70 Einträge zum Thema SQL aufweist, oft mehrere Seiten je Thema bzw. je Befehl mit Querverweisen und auch mit Beispielen geschmückt ist. ...
    (microsoft.public.de.sqlserver)
  • Re: Aliases and the WHERE clause
    ... The one hope we could hold is that future releases of engines will build on ... To me, SQL also feels just like a set of principles, or if you wish, mental ... I was thinking about the ORDER BY clause. ... alias in the WHERE clause. ...
    (microsoft.public.access.queries)