Re: Finding Records

From: Stephen Howe (stephenPOINThoweATtns-globalPOINTcom)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 16:03:52 -0000

> 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)