Subscribe to this thread
Home - General / All posts - Select records from table that match a list
ColinD

2,082 post(s)
#14-Feb-23 05:00

I have this M8 query

select*from [Table],[List] where [Name] like [Name_x];

Name_x is a list of names to select from the larger set of Name in Table

How to do this in M9 and how to show the selection in the related drawing are my questions.

Thanks


Aussie Nature Shots

adamw


10,447 post(s)
#14-Feb-23 14:00

[Name_x] should contain a pattern to match, not a list of names. If you want to select all names from a specific list, use IN (... WHERE [name] IN (<name1>, <name2>, ...)).

Manifold 9 also supports LIKE. It's in the Select pane, <select the desired field>, Search, Condition = matches pattern (like), <set value to the desired pattern, may include % and _>. Selecting records in the table that belongs to a drawing automatically selects them in the drawing.

ColinD

2,082 post(s)
#14-Feb-23 21:17

Thanks Adam, but that isn't practical in this case. A sample M8 file is attached.

In M9 I have a list of 93 records to be selected from 3.8 million.

Attachments:
SelectFromList_M8.map


Aussie Nature Shots

tjhb
10,094 post(s)
#15-Feb-23 02:53

Colin

For 8

--SQL8

SELECT t.*

FROM 

    [FloristicListPlots] AS t

    RIGHT JOIN

    [Plots2Select] AS u

    ON LCase(t.[Plot Name]) = LCase(u.[Plot])

or for 9

--SQL9

SELECT t.*

FROM

    [FloristicListPlots] AS t

    RIGHT JOIN

    [Plots2Select] AS u

    ON StringToLowerCase(t.[Plot Name]) = StringToLowerCase(u.[Plot])

If you don't care about case (so that different species can be differentiated by differing case, as for genes I think) then omit the conversion functions.

Why RIGHT JOIN? It depends what you want.

A right join excludes unmatched records on the left (the first table), but lists all records on the right (second table) even if unmatched.

Use LEFT JOIN if you want to see all records from the first table, even if unmatched.

Use FULL JOIN to see all records from both tables, regardless of whether they are matched.

To include all rows from both tables, whether matched or not, use INNER JOIN.

Unmatched rows (if listed) are paired with blanks (NULL).

This is all informative--or can be.

I would say, never use a comma join (as you did). It's nobody's friend.

Instead, you put both tables side by side in your head, then you apply the join condition, then you list the results.

There's no magic, it's just one step after the other.

Tim

tjhb
10,094 post(s)
#15-Feb-23 03:11

Error (sorry, don't know what came over me):

To include all rows from both tables, whether matched or not, use INNER JOIN.

To include rows from either table only if they are matched, use INNER JOIN. (List no unmatched rows from either the left table or the right table. Don't match them against blanks. Just omit them.)

ColinD

2,082 post(s)
#15-Feb-23 06:23

Marvelous, thanks Tim. I popped TableCacheIndexGeoms in front for M9.

PS hope you got through the recent bad weather OK.


Aussie Nature Shots

tjhb
10,094 post(s)
#16-Feb-23 03:00

Thanks for the kind wishes Colin. It's awful for those affected, many of whom had been through similar destruction at least once in the past year.

It badly hit the North Island. We did batten down the hatches in Nelson/Tasman, but here the event produced no more than a smattering of light rain with no wind.

Manifold User Community Use Agreement Copyright (C) 2007-2021 Manifold Software Limited. All rights reserved.