SQL Select from Table A, B, even if there are no matches A = B

[gmela...@gmail.com]

Hi,

I'm starting to think this isn't possible but here it goes. Trying to perform an SQL statement between table A (about 500 gyms), and a few records in table B (about 200 pools), as not all gyms have pools and it is not practical to combine the information into a single table. They are all point records.

Trying to get a simple table such as this:

Select

Gym.Franchise, Gym.Location, Gym.Members, Pools.Length, Pools.Depth

from Gyms, Pools

where Gym.obj intersect Pool.obj into Gym_Info

The problem is that this only generates the 200 records where there is a join.

Is there a workaround? 

I've thought of selecting two tables, one where gym obj intersects pools object and another where it doesn't. But that leaves me with two tables which is not practical. I really, really need just the 1 table with 200 with gym+pool info and 300 with only gym info.

Any advice would be appreciated.

Mel.

[Peter Horne]

Hi Mel,

Your sql query works fine and gives you the answer for Gyms with pools. Save the result from that query as Gyms_Info (200 records) and open that table. Ensure that table has the GYM columns first (and it should from your SQL query).  Then if you select INVERT option from the menu (image attached from V2019) and browse that selection, I am hoping you will see the Gyms that don;t have pools (300 records), Copy and paste these 300 records into your table Gyms_Info and then you should have 500 records showing the info you need. I am also assuming that the columns you have mentioned in your above query are the only columns in these 2 data sets.

HTH

Peter H

[gmela...@gmail.com]

Hi Peter,

Thanks for your quick response! The invert feature does do part of what I want (selecting the gyms without pool), so thanks for that.

But unfortunately it seems that it is still not possible to have the results show up in a single "query" format but 2 separate queries. Saving the results as a local table is not an option for this particular problem.

Mel.

[Timothy Mashford]

What about using Add Column statement (or update column menu item) to add temporary columns (pool length + depth) to the gym table, only those with a pool will be populated.

Then run SQL over the gym table to return only the 5 columns you want.

--
--
You received this message because you are subscribed to the
Google Groups "MapInfo-L" group.To post a message to this group, send
email to mapi...@googlegroups.com
To unsubscribe from this group, go to:
http://groups.google.com/group/mapinfo-l/subscribe?hl=en
For more options, information and links to MapInfo resources (searching
archives, feature requests, to visit our Wiki, visit the Welcome page at
http://groups.google.com/group/mapinfo-l?hl=en

---
You received this message because you are subscribed to the Google Groups "MapInfo-L" group.
To unsubscribe from this group and stop receiving emails from it, send an email to mapinfo-l+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/mapinfo-l/33c7940e-9c25-4395-a581-e067a28997f6n%40googlegroups.com.

[gmela...@gmail.com]

Hi tmarshford,

Thanks for the suggestion, it works.

I have always been a bit 'scared' of temporary columns so didn't consider it, I'm afraid I might forget and accidentally save my table or something :( but if this is the only solution I'll happily do something like that.

Will have to practice more codes using temporary columns to get comfortable :)

Mel.