To preserve my sanity when thinking this through I renamed all
occurrences of 'add' to 'ad' and 'searchword' to 'keyword'
This is untested
FROM keywords k, regions r, adds a, ads_keywords ak
WHERE k.keyword_id = ak.keyword_id
AND ak.ad_id = a.ad_id
AND a.region_id = r.region_id
AND r.region_id = 1;
[email]bjorn.bergstromjadestone.se[/email] wrote:> Hi,
> I'm helping a friend to build a system to handle advertisments from
> local dealers in different regions/cities. In this system, an
> advertisment belongs to a region and is connected to one or more
> categories (in this context called searchwords).
> One of the things that I need to extract is a list of all categories
> that have 1 or more adds targeted for a specific region. These are the
> TABLE regions - contains the regions that a specific add can belong to
> TABLE adds - contains the advertisements
> regionid (corresponds to regions.id)
> TABLE searchwords - contains searchwords that each add can be
> categorized as
> 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?
> Anyone that can help me?