Union query help - multiple unions

[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) ;"

 

[Clifford Bass]

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]

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.

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,

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) ;"

 

[Clifford Bass]

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

 

[j_gold]

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

 

[j_gold]

I reworked the code and it works beautifully - many, many thanks!

 

[Clifford Bass]

Hi,

Great! And you are quite welcome.

Clifford Bass