Min record from TWO tables

well actually from one table, but two groups of records

The table has ID, NAME, DATA

I need all the records where ID is value1 or ID is value2
BUT … there could be the same name for both values, I only want the one with lowest ID value

  • a given name may be in the table with just one id with value1
  • a given name may be in the table with just one id with value2
  • it could be in the table with BOTH, but I only want the one with value1

Actually it would use “base” to decide…

SELECT id,name,data,0 as  base
    FROM myTABLE
  WHERE  id=value1
UNION
SELECT id,name,data,1 as  base
    FROM myTABLE
  WHERE  id=value2

a given NAME will be in the table a maximum of twice

This just worked on the Chinook database, and I think gives you what you want.

SELECT MIN(ArtistID), Name FROM Artist WHERE ArtistID =3 or ArtistID=4

Yeah I just realized I explained it incorrectly, plus there is an additional field involved… .my bad

I need the NAME and DATA from VALUE1
unless there is no VALUE1 then I need the one for VALUE2

VALUE1 itself may be numerically higher, which is why I used “BASE” instead

An additional field to return, or in the filter?

You can’t keep changing things in the original post after I try to answer, I can’t help you that way. Keep adding posts, don’t edit the original.

Return

And Tim… I didn’t change it after you answered… :slight_smile: I changed WHILE you were answering

With the description, I think you should push some of this logic into Xojo and make a second query. Just for simplicity sake.

Edit: but then again you’re obfuscating things to the point where I don’t know enough, so my recommendations are probably not in line with what you need :confused:

Dave,

Add to the end of your (edited) query:

ORDER BY base LIMIT 1

[quote=422216:@Jay Madren]
Add to the end of your (edited) query:

ORDER BY base LIMIT 1

Clever!

that won’t work… you assume I’m gathering only one NAME value

100 DAVE 0
110 DAVE 1
120 SUZY 1
150 FRED 0

I want 100,120 and 150,

using LIMIT 1 would return only #100

Is id not unique? I assumed that it was, therefore that you were only retrieving one NAME at a time. We need a better explanation of what you’re trying to accomplish.

Not sure what you think is obfucating to the point the problem is not clear… let me try again

The table as ID, NAME and DATA
a given NAME may appear once or twice for a given ID (if twice the value of DATA is different)
however a given ID may have multiple but unique names (think of ID as a GROUP_ID)

VALUE1 and VALUE2 come from elsewhere in the program… Value1 could be greater than Value2

This is why I added the BASE field (0 if ID=value1 and 1 if ID=value2)

For each NAME that is in the group defined by Value1 and/or Value2 I need all the ones where ID=VALUE2, unless the Name is ONLY in the group identified by Value2.

Or simply… If the name is only in one of the two groups. I want which ever group… if it is in both groups I want the one from VALUE1

Yes, the problem was not clear. Your restatement reveals this to be more complicated that you initially let on. At this point, I’m with Tim - you should retrieve the possible records then sort them out within Xojo code. Could it be done purely in SQL? Probably. But why create a very complicated SQL statement when you could easily arrive at your desired result in Xojo?

I was trying to avoid that, but any SQL solution was as you said quite complex… and I have 8 “flavors” that need to be run… but they all use a common output method. So I’m adding logic that compares the fields to the previous record and decides what do do

Thanks

I’m trying to visualize your table schema…

[quote=422220:@Dave S]

The table as ID, NAME and DATA
a given NAME may appear once or twice for a given ID (if twice the value of DATA is different)
however a given ID may have multiple but unique names (think of ID as a GROUP_ID)[/quote]
‘Twice’ and ‘unique’ are mutually exclusive.

In the first post stated you wanted the lowest value of ID…

[quote]VALUE1 and VALUE2 come from elsewhere in the program… Value1 could be greater than Value2

This is why I added the BASE field (0 if ID=value1 and 1 if ID=value2) [/quote]
BASE gives a preference for a certain pair of ID values.
Your small sample had 4 values for ID.
Do you plan to run this query multiple times (once for each ID pair)?

In reality this is for an app that will analyze a Xojo source code.

Name is the name of a property, method or event, ID is the name of the class it is in.

Value1 identifies the SUBCLASS and Value2 identifes the SUPERCLASS

So if it is ONLY in Value1 then it is a method that is unique to the Subclass
If only in Value2 then it is inherited from the Superclass
and if in both then it is Overridden by the Subclass

[quote]I need all the records where ID is value1 or ID is value2
BUT … there could be the same name for both values, I only want the one with lowest ID value[/quote]

SELECT id,name,data,0 as base FROM myTABLE WHERE id=value1 UNION SELECT id,name,data,1 as base FROM myTABLE WHERE id=value2

The ‘base’ query turns whatever value1 and value2 are, into more useable 1 and 0

[quote]100 DAVE 0
110 DAVE 1
120 SUZY 1
150 FRED 0

I want 100,120 and 150,[/quote]

There will be better methods, Im sure but consider this query:

select name, min(base) as lowestbase from ( SELECT id,name,data,0 as base FROM myTABLE WHERE id=value1 UNION SELECT id,name,data,1 as base FROM myTABLE WHERE id=value2 )

This query will return
DAVE 0
SUZY 1
FRED 0

Now link it to the original
(usual caveats about untested code apply… this is typed straight in)

[code]
select * from
(
SELECT id,name,data,0 as base
FROM myTABLE
WHERE id=value1
UNION
SELECT id,name,data,1 as base
FROM myTABLE
WHERE id=value2
) as original

inner join

(
select name, min(base) as lowestbase
from (
SELECT id,name,data,0 as base
FROM myTABLE
WHERE id=value1
UNION
SELECT id,name,data,1 as base
FROM myTABLE
WHERE id=value2
)
) as minimal
on minimal.name = original.name
and minimal.lowestbase = original.base;[/code]

This syntax seems to work in SQLite:

SELECT id,name,data,MIN(base) AS base FROM (SELECT id, name, data, 0 AS base FROM myTABLE WHERE (id=value1) UNION SELECT id, name, data, 1 AS base FROM myTABLE WHERE (id=value2) ) GROUP BY name;

Group (sort) (results of added virtual ‘base’ column) by name, and display the row with minimum ‘base’

Odd.

If you don’t include ID in the group by clause, Oracle and SQL Server will kick up a fuss.
What does SQLLite do here with these two rows?

10,FRED,1
20,FRED,0

Name = FRED because we group by it
min( base) is 0

, but without an aggregate function on ID, would it return 10 (the right one), or 20 (the wrong one)?

and both Min(ID) and Max(ID) would return the wrong value sometimes… you need the ID associated with the row on which the lowest base exists.

Wouldn’t you be better off using 1 & 2 for your values? Then you could use sum to determine the answer 1 = super, 2 = inherited, 3 = overridden.