problem with JOINS 1

[ZggZg]

Hi there,

I'm going nuts over an SQL JOIN statement and after trying everything
I hope somebody can hint me on this one:

Let's say I got the tables comp, cities, countries and the related
fields

comp.formal_address = cities.city_id
comp.informal_address = cities.city_id
comp.country_id = countries.country_id

and the WHERE criteria are either formal_address OR informal_address
and #item# is the input from a form (ColdFusion).

How do I join them?
Attempts to do it like this didn't work out:
-----------------------------------------------------------
SELECT * FROM comp INNER JOIN
((INNER JOIN cities ON comp.informal_address = cities.city_id)
LEFT JOIN countries ON comp.country_id = countries.country_id)
cities ON comp.formal_address = cities.city_id
WHERE comp.formal_address LIKE '%#item#%' OR comp.informal_address
LIKE '%#item#%'
-----------------------------------------------------------
Somebody can tell me what's wrong?

 

[MikeRBS]

I suspect you are trying to match records either on informal or formal address. I'd say that requires a UNION. Join first on formal then secondly on informal, then the UNION collects the two possibilities into one set.

 

[ZggZg]

Hi Mike,
But how does that go in one query?

 

[lespaul]

The syntax for a union query is:

SELECT * FROM TABLEA
UNION
SELECT * FROM TABLEB

This only works if both tables have the same number of fields. In your situation, you would do something like:

SELECT * FROM TABLE WHERE Something = "Formal"
UNION
SELECT * FROM TABLE WHERE Something = "Informal"

HTH


Leslie

 

[ZggZg]

Thanks Lespaul. In this case the union works perfect!
Cheers

ZggZg