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
