Query on two tables and join them on two others

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Query on two tables and join them on two others

Author

Topic

ZggZg
Starting Member

4 Posts

Posted - 2003-08-08 : 06:35:25

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

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2003-08-08 : 09:35:35

Try to use InStr() function instead of Like operator:

where InStr(YourField, x) > 0

- Vit

ZggZg
Starting Member

4 Posts

Posted - 2003-08-12 : 04:41:40

Hi, I've tried it but doesn't seem to fit in.
ColdFusion5+ has a query of queries feature so in lack of a JET SQL solution with an overall joining query I'm now trying to solve the thing with this feature:

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

query3: select * from query1,query2

Only thing is I now only get output of query1. At least it should be possible in ColdFusionMX to get it to work but it would be nice to get it to work with version 5.0.

If anyone knows a JET SQL alternative, by all means!

ZggZg
Starting Member

4 Posts

Posted - 2003-08-12 : 06:12:11

Hi, both first two queries are separated because both contain a lot of LEFT JOIN statements. In ColdFusion it's not allowed to say something like

SELECT table1.*, table2.*
FROM ((table1 LEFT JOIN tableA ON table1.fkA = tableA.fkA)
LEFT JOIN tableB ON table1.fkB = tableB.fkB),
((table2 LEFT JOIN tableC ON table2.fkC = tableC.fkC)
LEFT JOIN tableD ON table2.fkD = tableD.fkD)
WHERE blahblahblah

ZggZg
Starting Member

4 Posts

Posted - 2003-08-12 : 06:16:02

BTW, by saying SELECT table1.*,table2.* FROM table1,table2 don't you get Cartesian output (or the like) as like for every hit in table1 you get hits from table2?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources