Re: Help with SQL query.... I'm sure there is a way!
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-idAND 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