Re: Help with SQL query.... I'm sure there is a way!

From: Jarl Hermansson <jarl_at_mimer.com>
Date: Tue, 20 Jun 2006 09:23:15 +0000 (UTC)
Message-ID: <Xns97E873D30DD66jarlmimercom_at_62.127.77.84>


bjorn.bergstrom_at_jadestone.se wrote in news:1150793277.523966.290680 _at_h76g2000cwa.googlegroups.com:
>
> TABLE regions - contains the regions that a specific add can belong to
> id
> name
>
> TABLE adds - contains the advertisements
> id
> regionid (corresponds to regions.id)
> text
>
> TABLE searchwords - contains searchwords that each add can be
> categorized as
> id
> name
>
> TABLE adds_searchwords - specifies the categories that a specific add
> belongs to
> addid (corresponds to adds.id)
> searchwordid (corresponds to searchwords.id)
>
>
> So, from a known regions.id I need to get a list of all
> searchwords.name that have at least one add with a corresponding
> searchwordid-addid relationship. Is this possible in a single query?
>
> TIA,
> Björn

Björn,

As always when writing SQL, there are several possible solutions. For example:

SELECT searchwords.name
FROM searchwords
WHERE searchwords.id IN

    (SELECT adds_searchwords.searchwordid

     FROM   adds_searchwords,
            adds
     WHERE  adds.regionid = 123   -- replace 123 with your region-id
       AND  adds_searchwords.addid = adds.id);


another approach:

SELECT DISTINCT searchwords.name
FROM searchwords,

       adds_searchwords,
       adds
WHERE  adds.regionid = 123        -- replace 123 with your region-id
  AND adds.id = adds_searchwords.addid
  AND searchwords.id = adds_searchwords.searchwordsid;

Note that NAME is a reserved word. You'd better rename those columns, or double quote them (e.g. "NAME"), to avoid future problems. For more info on reserved words:
http://developer.mimer.com/validator/sql-reserved-words.tml http://developer.mimer.com/validator/parser200x/vendor-reserved-words- sql200x.tml

HTH,
Jarl Received on Tue Jun 20 2006 - 11:23:15 CEST

Original text of this message