Union query help - multiple unions

  • Thread starter Thread starter j_gold
  • Start date Start date
J

j_gold

Hi,

The following query gives me a Run-time error '3146' ODBC call failed. I've
tested each select statement individually, and the queires work.

If I run two of the queries together e.g. 1st and 2nd select statements or 1
and 3 or 2 and 3, as unions, each of those work. However, when I try and run
the whole lot, it blows up.

What am I doing wrong :-), thanks.

'Select all Persons who are Faculty & Researchers
sqlQuery = "SELECT *, 'faculty' as [personType], 'y' as [Researcher] "
sqlQuery = sqlQuery & "FROM Person P "
sqlQuery = sqlQuery & "WHERE P.personID IN (SELECT F.personID FROM Faculty
F) "
sqlQuery = sqlQuery & "AND P.personID IN (SELECT R.personID FROM Researcher
R) "
'Select all Persons who are Faculty & NOT Researchers
sqlQuery = sqlQuery & "UNION "
sqlQuery = sqlQuery & "SELECT *, 'faculty' as [personType], 'n' as
[Researcher] "
sqlQuery = sqlQuery & "FROM Person P "
sqlQuery = sqlQuery & "WHERE P.personID "
sqlQuery = sqlQuery & "IN (SELECT F.personID FROM Faculty F) "
sqlQuery = sqlQuery & "AND P.personID NOT IN (SELECT R.personID FROM
Researcher R) "
'Select all Persons who are Grads and Researchers
sqlQuery = sqlQuery & "UNION "
sqlQuery = sqlQuery & "SELECT *, 'grad' as [personType], 'y' as [Researcher] "
sqlQuery = sqlQuery & "FROM Person P "
sqlQuery = sqlQuery & "WHERE P.personID "
sqlQuery = sqlQuery & "IN (SELECT G.personID FROM GradStudent G) "
sqlQuery = sqlQuery & "AND P.personID IN (SELECT R.personID FROM Researcher
R) ;"
 
Hi,

I have enountered that type of situation. Sometimes reordering the
queries makes a difference. Sometimes doing the queries in a different
fashion is the only solution I have found. In your case, you can probably do
that all in a single query that uses left joins to the Faculty, GradStudent
and Researcher tables. Untested:

select F.*, IIf(IsNull(F.personID), IIf(IsNull(G.personID), "???", "grad"),
"faculty") as personType, IIf(IsNull(R.personID), "n", "y") as Researcher
from ((Person as P left join Faculty as F on F.personID = P.personID) left
join GradStudent as G on G.personID = P.personID) left join Researcher as R
on R.personID = P.personID;

Clifford Bass
 
Hi Clifford, thanks for the help, but I could not get your suggestion to work.

I should add that I am using linked tables that are linked to a MySQL db.

What I am trying to do is to get a list of people broken down by type.

I have a master list of people who can be either faculty, staff or grad
student. To determine which type they are I compare the master table (Person)
against three other tables (Faculty, Staff and Grad). If the personID matches
the personID in one of the three tables, they belong to that group.
Additionally, if they are either Faculty or Grad, I need to determine if that
person is a Researcher (also determined by personID). The columns personType
and Researcher are filled in accordingly.

I am able to get the query to work if I use MySQL Query Browser.

Clifford Bass said:
Hi,

I have enountered that type of situation. Sometimes reordering the
queries makes a difference. Sometimes doing the queries in a different
fashion is the only solution I have found. In your case, you can probably do
that all in a single query that uses left joins to the Faculty, GradStudent
and Researcher tables. Untested:

select F.*, IIf(IsNull(F.personID), IIf(IsNull(G.personID), "???", "grad"),
"faculty") as personType, IIf(IsNull(R.personID), "n", "y") as Researcher
from ((Person as P left join Faculty as F on F.personID = P.personID) left
join GradStudent as G on G.personID = P.personID) left join Researcher as R
on R.personID = P.personID;

Clifford Bass

j_gold said:
Hi,

The following query gives me a Run-time error '3146' ODBC call failed. I've
tested each select statement individually, and the queires work.

If I run two of the queries together e.g. 1st and 2nd select statements or 1
and 3 or 2 and 3, as unions, each of those work. However, when I try and run
the whole lot, it blows up.

What am I doing wrong :-), thanks.

'Select all Persons who are Faculty & Researchers
sqlQuery = "SELECT *, 'faculty' as [personType], 'y' as [Researcher] "
sqlQuery = sqlQuery & "FROM Person P "
sqlQuery = sqlQuery & "WHERE P.personID IN (SELECT F.personID FROM Faculty
F) "
sqlQuery = sqlQuery & "AND P.personID IN (SELECT R.personID FROM Researcher
R) "
'Select all Persons who are Faculty & NOT Researchers
sqlQuery = sqlQuery & "UNION "
sqlQuery = sqlQuery & "SELECT *, 'faculty' as [personType], 'n' as
[Researcher] "
sqlQuery = sqlQuery & "FROM Person P "
sqlQuery = sqlQuery & "WHERE P.personID "
sqlQuery = sqlQuery & "IN (SELECT F.personID FROM Faculty F) "
sqlQuery = sqlQuery & "AND P.personID NOT IN (SELECT R.personID FROM
Researcher R) "
'Select all Persons who are Grads and Researchers
sqlQuery = sqlQuery & "UNION "
sqlQuery = sqlQuery & "SELECT *, 'grad' as [personType], 'y' as [Researcher] "
sqlQuery = sqlQuery & "FROM Person P "
sqlQuery = sqlQuery & "WHERE P.personID "
sqlQuery = sqlQuery & "IN (SELECT G.personID FROM GradStudent G) "
sqlQuery = sqlQuery & "AND P.personID IN (SELECT R.personID FROM Researcher
R) ;"
Click to expand...
Click to expand...
 
Hi,

Yeah, I pretty much figured that was what you were doing in regard to
what you wanted. When you say mine would not work; was that because it
failed also? Or that it did not return what you are expecting? My
assumption, perhaps wrong, was that any one person could only have at most
one row in each of the other tables. I also was assuming that graduate
students cannot be faculty, at least at the same time.

Clifford Bass
 
Hi Clifford,

My apologies. Yes, after rerunning yours, it mostly works. It helps to copy
and paste the code exactly as typed ;-)

However, I am not getting the results I want. I will rework what you have
provided to see if I can get the results I'm looking for. I may have to post
back. :-)

Thanks for the help.

Cheers,

J
 
Back
Top