By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,237 Members | 1,483 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,237 IT Pros & Developers. It's quick & easy.

burning JOIN question

P: n/a
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 of 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?
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
If you are okay with returning all records matching "item" then I'd
just use a Union query. Something like this:

SELECT * FROM comp
INNER JOIN cities ON comp.informal_address = cities.city_id
LEFT JOIN countries ON comp.country_id = countries.country_id
WHERE comp.informal_address LIKE '%#item#%'
UNION SELECT * FROM comp
INNER JOIN cities ON comp.formal_address = cities.city_id
LEFT JOIN countries ON comp.country_id = countries.country_id
WHERE comp.formal_address LIKE '%#item#%'

Will that work for ya?

al***********@yahoo.com (ZggZg) wrote in message news:<7e**************************@posting.google. com>...
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 of 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?

Nov 13 '05 #2

P: n/a
Thanks JoJo9, in this case UNION does the trick. Cheers!
Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.