Hi Terry,
You have to have an unbroken line of relations between the tables to be able to
run the query. That means, that you cannot make a query between one table with
relations to many other tables, but from table 1 to table 2 to table 3 to table
4 ... is possible.
I have sometimes used subqueries (Select xx from table YY where column aa in
(select column bb from table zz ...) to be able to make queries between
non-base tables and other tables. But it is very tediuos and sometimes it is
easier to make a DBMS link to an Access database where you have set up the
query based on Access tables.
Best regards
Jacob K. Joergensen
Watertech a/s
Soendergade 53
8000 Aarhus C
watertech.dk Tel.: +45 8732 2020
Fax: +45 8732 2021
Direct tel.: +45 8732 2078
[EMAIL PROTECTED]
-----Oprindelig meddelelse-----
Fra: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] På vegne af Terry McDonnell
Sendt: 15. november 2005 12:27
Til: Peter Horsbøll Møller; [EMAIL PROTECTED]
Emne: RE: [Mapinfo-l] Inadequacies in MB's Select - SQL ???
Peter
Thanks again for continued attention and interest. Hmmm, main table in the
middle, but what about when there are 3 or 5? I'll be experimenting today but
haven't had a chance to try anything more ambitious than the example below.
Terry
-----Original Message-----
From: Peter Horsbøll Møller [mailto:[EMAIL PROTECTED]
Sent: 15 November 2005 10:30
To: Terry McDonnell
Subject: RE: [Mapinfo-l] Inadequacies in MB's Select - SQL ???
Terry,
You are right that the order might seem a bit strange...
I think the idea behind this order is to put the main table in the middle.
Normally - when joining two tables - you would put the main table first.
I'm not sure if the order of the conditions also matters ?!
-----Original Message-----
From: Terry McDonnell [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 15, 2005 10:37 AM
To: Peter Horsbøll Møller
Subject: RE: [Mapinfo-l] Inadequacies in MB's Select - SQL ???
Hi Peter
Thanks very much. That worked. However, I'm intrigued as to what governs the
order. The main table is FacMast, yet that you've put second in order. You
have "where (look-up).PK = (main).FK and (main).FK = (look-up).PK". The below
was just an example (and as far as I got experimenting in the MB window) and
I'll need to add further tables (and for other entities too).
Anyway, you've got me on my way, as usual
Cheers
Terry
-----Original Message-----
From: Peter Horsbøll Møller [mailto:[EMAIL PROTECTED]
Sent: 15 November 2005 08:15
To: Terry McDonnell; [EMAIL PROTECTED]
Subject: RE: [Mapinfo-l] Inadequacies in MB's Select - SQL ???
Terry,
You can select from more than 2 tables using MapInfos SQL. It's just a question
of the order of the tables...
You could try this instead:
select * from facdesc, facmast, buszoner
where FacDesc.fac_code = facmast.fac_code
and facmast.zone = buszoner.zonenumber
I haven't tested this, but in most cases the problem is the order of the tables
-----Original Message-----
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Terry McDonnell
Sent: Monday, November 14, 2005 5:07 PM
To: [EMAIL PROTECTED]
Subject: [Mapinfo-l] Inadequacies in MB's Select - SQL ???
Dear List
The Help on Select SQL is fairly scratchy but does state that it's based on GP
SQL. It doesn't give any examples of accessing more than 2 tables.
Now then, I have a table with foreign key references to at least two other
tables. e.g. The Stops table bears a foreign ref, ZoneNo, to the zone in which
it resides, and one to its county, and one referring to the Zone Group that
comprises its catchment area.
I'm creating a bespoke "info tool" whereby I can display all the info
pertaining to the facility, but I want to get the full info from the referenced
tables, e.g.
select * from facmast, facdesc, buszoner
where facmast.fac_code = FacDesc.fac_code
and facmast.zone = buszoner.zonenumber
Issuing the above select I get the error messgage:
"Incorrect tables are joined. Invalid join condition in where clause."
But this is just bog-standard SQL, which I've used extensively in other
languages. I've tried different permutations, and "Select ... Where .... And
... In ( Select ....)" all to no avail.
I've come to the conclusion that, despite how the Help command syntax suggests,
you can't select from more than 2 tables except maybe when you're selecting
from the other tables on the same key.
Any advise?
'ppreciate it.
Terry McDonnell
_______________________________________________
Mapinfo-l mailing list
[email protected]
http://www.directionsmag.com/mailman/listinfo/mapinfo-l
_______________________________________________
Mapinfo-l mailing list
[email protected]
http://www.directionsmag.com/mailman/listinfo/mapinfo-l
_______________________________________________
Mapinfo-l mailing list
[email protected]
http://www.directionsmag.com/mailman/listinfo/mapinfo-l
