combining info in tables for a query (1 Viewer)

[stef123]

Hi there,

I just can't figure out how to solve my next problem. What I need to do is to make a query showing all the country's next to germany(nr.5) with their capitals. There are three tables which I can use, nr.1 showing the country_id's with their "neighbour country's", nr.2 showing the name of the country's with their capital_id's and nr.3 showing the name of the capital with their corresponding country-id.
Is there anybody who could help me with this problem, or has some suggestions? If you find this message unclear, i would love to e-mail you the tables.
As i am dutch my english isn't that good.
Thanks in advance

1:
country_1 country_2
1 2
1 3
1 4
1 5
2 4
2 5
2 6
2 10
3 5
4 5
5 7
5 12
6 12
8 9
10 11
13 14

2:
id name capital_id
1 Belgium 1
2 France 4
3 Netherlands 9
4 Luxembourg 14
5 Germany 6
6 Italy 12
7 Denmark 15
8 United Kingdom 11
9 Ireland 16
10 Spain 17
11 Portugal 18
12 Austria 19
13 Sweden 20
14 Finland 21
15 Greece 22

3:
id name people country_id
1 Brussel 137000 1
2 Antwerpen 497000 1
3 Gent 235700 1
4 Parijs 2176243 2
5 Lyon 418476 2
6 Berlijn 3400000 5
7 Bonn 293000 5
8 Munchen 1267000 5
9 Amsterdam 1038000 3
10 Rotterdam 576000 3
11 Londen 6678000 8
12 Rome 2826000 6
13 Milaan 1515000 6
14 Luxemburg 79000 4
15 Kopenhagen 482000 7
16 Dublin 526000 9
17 Madrid 3200000 10
18 Lissabon 807000 11
19 Wenen 1512000 12
20 Stockholm 651000 13
21 Helsinki 484000 14
22 Athene 1000000 15

 

[Pat Hartman]

The data in table1 makes that difficult. It looks incomplete. Shouldn't it be:
1:
country_1 country_2
1 2
1 3
1 4
1 5
2 1
2 4
2 5
2 6
2 10
3 1
3 5
4 1
4 2
4 5
5 1
5 2
5 3
5 4
5 7
5 12
6 2
6 12
7 5
8 9
9 8
10 2
10 11
11 10
12 5
12 6
13 14
14 13
Hopefully I got all the missing rows.

 

[stef123]

No, I see what you mean, but the table that i've got is exactly the same as I put it in here. All the information in it is only shown once, so if country nr. 1 is next to nr.2, the table only shows 1 connected to 2, and not :1 connected to 2 and 2 conected to 1.
Is there still a possibility to work it out, without adjusting the table?
Thanks again,
Stef

 

[Pat Hartman]

You have to create a union query.
Query1:
Select country_1, country_2
From tbl1
Union Select country_2, country_1
From tbl1;

Then use Query1 rather than tbl1 to do any selection criteria.