query and join two tables on two other tables

[ZggZg]

Hi,

I've two tables, priv and bedr.
I need to find all values for parameter 'x' in fields priv.priv_name, bedr.bedr_name, bedr.bez_name, bedr.verv_name in one query. The table priv contains a foreign key (priv_x_bedr_id) to bedr.bedr_id.

SELECT * FROM priv, bedr WHERE priv.priv_name LIKE '%#x#' OR bedr.bedr_name LIKE '%#x#' OR bedr.bez_name LIKE '%#x#' OR bedr.verv_name LIKE '%#x#'

doesn't do the job. (The parameter reference is the right way in ColdFusion which I'm using)

Step 2 would be to LEFT JOIN tables priv and bedr ON tables cities and countries, f.i.

SELECT * FROM ((priv LEFT JOIN cities ON priv.priv_x_cit_id = cities.cit_id)
LEFT JOIN countries ON priv.priv_x_count_id = countries.count_id),
((bedr LEFT JOIN cities ON bedr.bedr_x_id = cities.cit_id)
LEFT JOIN countries ON bedr.bedr_x_count_id = countries.count_id)
WHERE "as in first query"


If somebody could help me out on this one, my job's at stake here

 

[DanChard]

I think Access prefers *s to %s so maybe your first query should be more like:

SELECT * FROM priv, bedr WHERE priv.priv_name LIKE '*#x#*' OR bedr.bedr_name LIKE '*#x#*' OR bedr.bez_name LIKE '*#x#*' OR bedr.verv_name LIKE '*#x#*'

Might be of some use...

 

[ZggZg]

Hi Guys, the problem doesn't have to do with the wildcards because '%#x#' works fine. The second query is more a refinement on the first so it's not that both are used at the same time.

 

[DanChard]

Your second query results in selecting all from 2 separate queries which don't seem to be joined in any way which is almost certainly incorrect:

SELECT *
FROM ((priv
LEFT JOIN cities
ON priv.priv_x_cit_id = cities.cit_id)
LEFT JOIN countries
ON priv.priv_x_count_id = countries.count_id) AS SUB_TABLE1,
((bedr
LEFT JOIN cities
ON bedr.bedr_x_id = cities.cit_id)
LEFT JOIN countries
ON bedr.bedr_x_count_id = countries.count_id) AS SUB_TABLE2
WHERE "as in first query"

(I have referenced the first and second sub-tables as SUB_TABLE1 and SUB_TABLE2 respectively)

SUB_TABLE1 and SUB_TABLE2 are not joined so every row returned from SUB_TABLE2 will be displayed for every row returned from SUB_TABLE1 which will result in a fairly massive output! If you're sure that this is how you want it to look then you should just be able to plug your where condition onto the end of it...

 

[ZggZg]

Hi DanChard,
What I'm after is combine both tables to one output. ColdFusion5+ has a Query of queries feature. I'm now trying to combine two queries into this one query of queries, which should be possible (at least it is in ColdFusionMX):

query1: select fields from table1
query2: select fields from table2

query3: select * from query1,query2

I now only get results from the first query but if I'll get it to work I'll get back to you.
In the meantime, if anyone has a JET-SQL alternative for this one, by all means!