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
tables:

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?

Anyone that can help me?

TIA,
Björn

How about this?
SELECT r.id
, r.name
FROM regions r
WHERE EXISTS
(SELECT *
FROM searchwords s
, adds a
, adds_searchwords as
WHERE s.name = r.name
AND a.regionid = r.id
AND as.searchwordid = s.id
AND as.addid = a.id
)

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.

You have a useless, vague, non-relational, universal magical "id" in
this narrative pseudo-code. That cannot exist in an RDBMS, so if
they were ignorant to do that, we have to start over with a proper
design.