SQLite Union Trouble

Databases
1

I am trying to Union two tables.

Both have a column called lastName and a column called indexID. I want the last names from both tables where the index id is the same in both tables. When I run the following SQL, I do not get the entry from the alternateNames table.

SELECT DISTINCT a.lastName as LastName FROM entries a WHERE a.indexID = ?
UNION
SELECT DISTINCT b.lastName as LastName FROM alternateNames b WHERE b.indexID = ?
ORDER BY LastName COLLATE NOCASE

If I remove one (it doesn’t matter which one) or both of the where clauses the sql works as expected.

Any Ideas?

Thanks, Thomas

2

You want a list of distinct names out of both tables?

SELECT DISTINCT a.lastname
   from entries a, alternameNames b
where a.indexid=b.indexid
3

So it prepares but just gives the wrong results ?

4

Norman - Yes - unless the where clauses are removed as described.
Dave - that would work but I want a list of lastNames from both tables for a specific index id

5

Then you original query should work … FYI. you can remove the DISTINCT, as UNION imposes that unless you specify UNION ALL

6

Dave - I agree but It doesn’t unfortunately…

7

What if you manually compose the sql by hand ?

Wondering if this is a prepared statement bug

8

And you’re passing the ID twice?

9

what Dave suggested should work…

select a.lastName as entLastName, b.lastName as altLastName
from entries a
join alternateNames b on b.indexID = a.indexID and a.indexID = ?
10

Shouldn’t it be:

SELECT a.lastName AS entLastName, b.lastName AS altLastName FROM entries a JOIN alternateNames b ON b.indexID = a.indexID WHERE a.indexID = ?

11

Tim, no - I only pass the indexID once and I confirmed it’s the same value for both where clauses.
Norman, I will try that - thanks.

All, Maybe I wasn’t being clear. Maybe this will help.

Assume the entries table looks like this (other columns not shown):

lastName indexID
Hatfield 1
Hare 2
Gould 1

and AlternateNames looks like this (other columns not shown):

lastName IndexID
Palardy 1
Schneppmueller 2

The result of my UNION

SELECT a.lastName as LastName FROM entries a WHERE a.indexID = 1
UNION
SELECT b.lastName as LastName FROM alternateNames b WHERE b.indexID = 1
ORDER BY LastName COLLATE NOCASE

should be this:
Gould
Hatfield
Palardy

What I am getting is this (again, unless I remove one or both of the where clauses in the statement):
Gould
Hatfield

BTW, the statement works in the program ‘DB Browser for SQLite’

12

Norman was right - It had something to do with a prepared statement bug -

I changed this:
Dim stmt As PreparedSQLStatement = Self.Prepare(“SELECT a.lastName as LastName FROM entries a WHERE a.indexID = ? UNION SELECT b.lastName as LastName FROM alternateNames b WHERE b.indexID = ? ORDER BY LastName COLLATE NOCASE”)

stmt.BindType(0, SQLitePreparedStatement.SQLITE_INTEGER)
stmt.Bind(0,App.IndexID)

Dim rs As RecordSet = stmt.SQLSelect

to this:
Dim sql as String = “SELECT a.lastName as LastName FROM entries a WHERE a.indexID =” + Str(App.IndexID) + " UNION SELECT b.lastName as LastName FROM alternateNames b WHERE b.indexID = " + Str(App.IndexID) + " ORDER BY LastName COLLATE NOCASE"

Dim rs As RecordSet = self.SQLSelect(sql)

And it works fine.

Thanks everyone. I will open a bug report.

13

Curious: you had two ? in the statement… did you provide two parameters when calling it?

14

Jeff!!! I sooooo suck
That had to be it - I was only setting one.

I don’t deserve to code somethimes…

15

I am the master of overlooking the obvious

16

It’s only obvious once you’ve seen it!

Sometimes I hate that our brain, knowing what we expect to see, lets us see it that way.

It’s similar to what happens to aging jocks and cheerleaders when they look in the mirror.

17

Perhaps I wasn’t clear enough when I asked.