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 = ?
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.
You want a list of distinct names out of both tables?
SELECT DISTINCT a.lastname
from entries a, alternameNames b
So it prepares but just gives the wrong results ?
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
Then you original query should work … FYI. you can remove the DISTINCT, as UNION imposes that unless you specify UNION ALL
Dave - I agree but It doesn’t unfortunately…
What if you manually compose the sql by hand ?
Wondering if this is a prepared statement bug
And you’re passing the ID twice?
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 = ?
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 = ?
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):
and AlternateNames looks like this (other columns not shown):
The result of my UNION
SELECT a.lastName as LastName FROM entries a WHERE a.indexID = 1
SELECT b.lastName as LastName FROM alternateNames b WHERE b.indexID = 1
ORDER BY LastName COLLATE NOCASE
should be this:
What I am getting is this (again, unless I remove one or both of the where clauses in the statement):
BTW, the statement works in the program ‘DB Browser for SQLite’
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”)
Dim rs As RecordSet = stmt.SQLSelect
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.
Curious: you had two ? in the statement… did you provide two parameters when calling it?
Jeff!!! I sooooo suck
That had to be it - I was only setting one.
I don’t deserve to code somethimes…
I am the master of overlooking the obvious
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.
Perhaps I wasn’t clear enough when I asked.