Board index » delphi » SQL syntax question

SQL syntax question

A have an address database table and another associated category table
as a logical one to many; i.e. an address can have 0 .. many categories.

I now want to write SQL to return addresses with selected combinations
of criteria; e.g. addresses with (CATA and CATB) or (CATB and CATD and
CATE)

is there a better way than ...

SELECT * FROM ADDRESS A
WHERE (EXISTS             (SELECT 1 FROM CATEgory B
                                            WHERE A.ID = B.ID
                                            AND B.CAT = 'CATA'
                AND EXISTS   (SELECT 1 FROM CATEgory C
                                            WHERE A.ID = C.ID
                                            AND C.CAT = 'CATB'
                )
OR (EXISTS                    (SELECT 1 FROM CATEgory D
                                            WHERE A.ID = D.ID
                                            AND D.CAT = 'CATB'
    ...
                AND EXISTS   (SELECT 1 FROM CATEgory Z
                                            WHERE A.ID = Z.ID
                                            AND Z.CAT = 'CATE'
                )

you see my point !!

any help gratefully received

John.

 

Re:SQL syntax question


Quote
On Mon, 16 Aug 1999 20:18:23 +0100, John <j...@jdavidg.demon.co.uk> wrote:
>A have an address database table and another associated category table
>as a logical one to many; i.e. an address can have 0 .. many categories.

>I now want to write SQL to return addresses with selected combinations
>of criteria; e.g. addresses with (CATA and CATB) or (CATB and CATD and
>CATE)

>is there a better way than ...

What are you really asking for with that query? You want the rows from the
Address table that have a corresponding row in the Category table (based on
the ID column). And, the value in the Category.Cat column must be one of
the values "CATA", "CATB", "CATC", or "CATE".

Use an inner join for the relationship between the two tables on this ID
column. The inner join filters rows from the Address table from the join
result Filter that do not have a match in the Category table. This match is
based on the ID column the two tables have in common. Filter the
intermediate result set produced by the join in the WHERE clause using the
IN predicate, comparing the Category.Cat column to a list of literal
values.

This should be a lot faster than what you posted:

  SELECT B.Cat, A.*
  FROM Address A
    INNER JOIN Category B
      ON (A.ID = B.ID)
  WHERE (B.Cat IN ("CATA", "CATB", "CATC", "CATE"))

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
Steve Koterski                               "You may already be a loser."
Felton, CA                                      -- Form letter received by
                                                   Rodney Dangerfield

Other Threads