SQL syntax--getting demoralized

[middletree]

I've seen posts here and elsewhere which read something along the lines of
"PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP!". Well, I know that kind of
subject line isn't descriptive, but I sure can relate right now. I've been
struggling for days--days!!-- on this one simple query. I really need to
get past this thing and move on. Please help.

I have a classic ASP page, and it gives you 4 dropdowns. You can select any
or none of them. Each of them is built by an ACCESS table. These 4 tables
are union tables; they have just 2 fields: the PK from the master table,
called Personal, and one of the 4 static tables that I call People, Area,
Ability, Gift.

Anyway, when someone selects a value from one or more of the dropdowns, the
next page grabs the values and puts them into variables (strGiftID,
strAbilityID, strAreaID, and strPeopleID). This is where I am running into
problems. If you see my previous threads on this, you'll see lots of
suggestions, and I have tried every one of them.

Here's my current code:

strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
Personal P "
strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
PG.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID "

If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID&" AND "
End if
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID&" AND "
End if
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
End if
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
End if

If Len(strWhere) > 0 Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If
----------------------------------------------

Here's the error:

'P.PersonalID = PG.PersonalID INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID'.
/grace/list.asp, line 64

(where line 64 is the line which executes the SQL statement)


Please, please let me know what you see that I am doing wrong.

TIA

 

[Brynn]

Have you tried putting single quotes around your where values?

I can't remember ... do you need to put the table aliases before the
column names in the where?

Have you tried this sql statement directly against the database?

Have you tried making a stored query in Access?

 

[Bob Barrows]

As always, the first step in debugging this type of problem is to
response.write your variable values (including the variable that contains
the SQL statement.). Here is how I would start debugging your code. Give it
a try and if it does not highlight your problem, post back with the results
of these response.writes.

strSQL = "SELECT P.Fname, P.LName, P.PreferredName, P.PersonalID FROM
Personal P "
strSQL = strSQL & "INNER JOIN PersonalGift PG ON P.PersonalID =
PG.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalArea PA ON P.PersonalID =
PA.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalAbility PAB ON P.PersonalID =
PAB.PersonalID "
strSQL = strSQL & "INNER JOIN PersonalPeople PP ON P.PersonalID =
PP.PersonalID "

Response.Write "strSQL contains: " & strSQL & "<BR>"
Response.Write "strGiftID contains: " & strGift & "<BR>"
If strGiftID <> "" Then
strWhere = "GiftID = "&strGiftID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"
Response.Write "strAreaID contains: " & strAreaID & "<BR>"
If strAreaID <> "" Then
strWhere = strWhere & "AreaID = "&strAreaID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"
Response.Write "strAbilityID contains: " & strAbilityID & "<BR>"
If strAbilityID <> "" Then
strWhere = strWhere & "AbilityID = "&strAbilityID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"
Response.Write "strPeopleID contains: " & strPeopleID & "<BR>"
If strPeopleID <> "" Then
strWhere = strWhere & "PeopleID = "&strPeopleID&" AND "
End if
Response.Write "strWhere contains: " & strWhere & "<BR>"

If Len(strWhere) > 0 Then
' Remove the last AND
strWhere = Left(strWhere, Len(strWhere) - 5)
strSQL = strSQL & "WHERE " & strWhere
End If

Response.Write "strSQL now contains: " & strSQL & "<BR>"

HTH,
Bob Barrows

 

[middletree]

Comments inline:

Have you tried putting single quotes around your where values?

Yes, but it didn't work, because these are integers. They have to be,
because they are the PK in their respective tables, where they are
autonumbers.

I can't remember ... do you need to put the table aliases before the
column names in the where?

Not sure what you mean

Have you tried this sql statement directly against the database?

Of course. Got the same error. Of course, I can't do the If part in the
database, so that kind of limits what I could test there.

Have you tried making a stored query in Access?

Not sure how, and not sure what difference it could make. What do you have
in mind?

 

[middletree]

Of course, I did try lots of response.writes. I guess I should have
mentioned that in my post, but if I would have mentioned everything I did,
the post would have been extremely long.

Let me see what I can do on this. Thanks.

 

[Bob Barrows]

Of course, I did try lots of response.writes. I guess I should have
mentioned that in my post, but if I would have mentioned everything I
did, the post would have been extremely long.

I realize that.

The thing is: we cannot help without seeing the results of the
response.writes. They are almost always the key to solving the problem.

The other key to solving these problems is telling us the data types of the
columns in your query. That lets us know where delimiters are required in
your sql statement.

Bob Barrows

 

[Bob Lehmann]

Since you prefixed your variable names with "str" - strAreaID - it gives the
impression that these are strings. If you are going to use this type of
notation, it should reflect the type of variable.

Bob Lehmann

 

[middletree]

Yeah, I guess since it's ASP, everything is a variant, so I tend to treat
almost everything like it's a string. You are correct that it implies the
wrong thing

 

[TomB]

I have a feeling all of those inner joins aren't nesting the way you want
them to.

I tried this query in Access and it returned a record if there was a
matching "join" in all of the "union" tables. That is, I created a Personal
item and an item for each of the four other tables, then a join in each of
the other 4 tables. If any one of the join tables did not contain my
Personal then it didn't show up. I assume that's what you want.

SELECT Personal.FName, Personal.LName, Personal.PreferredName,
Personal.PersonalID
FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID =
PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON Personal.PersonalID
= PersonalPeople.PersonalID) INNER JOIN PersonalArea ON Personal.PersonalID
= PersonalArea.PersonalID

You'll notice I took out the aliases (PersonalAbility PAB) I'm not sure if
they were causing the problem (they shouldn't have been) but I figured I'd
get them out of the way to be sure.

So I think the problem is with the parentheses. Adding the where clause
didn't cause a problem.

 

[middletree]

Ok, I will try it out. If I'm reading it right, I don't need to have my IF
statements. We'll see.

thanks

 

[Bob Barrows]

I have a feeling all of those inner joins aren't nesting the way you
want them to.

Of course you are right! Joins involving more than two tables need to be
nested by parentheses in JetSQL. I can't believe I missed that!

You'll notice I took out the aliases (PersonalAbility PAB) I'm not
sure if they were causing the problem (they shouldn't have been) but
I figured I'd get them out of the way to be sure.

No need. Table aliases help, not hinder.

Bob Barrows

 

[middletree]

Well, there is good news and bad news. I tried it out (you can, too, just go
to www.middletree.net/ then add to the URL: /list.asp
(Sorry to break it up like that, but don't want it ending up in Google
Groups)

Anyway, the good news is, it doesn't throw me an error. Bad news is, it
shows 19 rows, every time, no matter what I selected. Thing is, there are
only 6 rows in the Personal table!

 

[Brynn]

I would not normally email someone from the groups, but I have sent
you an email to your hotmail account listed.

If you no longer want me to email you, just tell me.

 

[middletree]

I have no problem with that. I'll take help where I can get it. As long as
you're not trying to sell me products to make body parts larger.

thanks

 

[Bob Barrows]

Well, there is good news and bad news. I tried it out (you can, too,
just go to www.middletree.net/ then add to the URL: /list.asp
(Sorry to break it up like that, but don't want it ending up in Google
Groups)

Anyway, the good news is, it doesn't throw me an error. Bad news is,
it shows 19 rows, every time, no matter what I selected. Thing is,
there are only 6 rows in the Personal table!

You must have multiple records for each person in at least one of the tables
involved in the join.

This is where you need to provide us with your table structures (relevant
fields only), sample data, intended results from that data, along with the
results your query is currently returning.It really helps us if you provide
the sample data for each table in the form of insert statements (insert
(<columns>) values (<data>) which we can copy, paste and run to insert the
data. The intended results should be provided in tabular format if possible.

Bob Barrows

 

[TomB]

Can you explain what it is you want returned?
I'm looking at list.asp right now, and I see a whole bunch of Joe Dirt's at
the bottom are your 4 combo boxes.

Are you hoping to limit the number of Joe Dirt's returned to those
personal's that match the contents of your combos?

In my database, I've only got 1 Personal and 1 for each of the 4 tables,
plus an entry for each combination in the join table. This returns one row.

However, if I add a second ability and add it to the join table as well.
Then I get two rows returned. I then add a second Area and the join, now I
have 4 rows returned. All of which makes sense.

You will still want your IF statements to limit the rows returned (see
example below).

Try throwing in a DISTINCT after SELECT ....

SELECT DISTINCT Personal.FName, Personal.LName, Personal.PreferredName,
Personal.PersonalID
FROM (((Personal INNER JOIN PersonalGift ON Personal.PersonalID =
PersonalGift.PersonalID) INNER JOIN PersonalAbility ON Personal.PersonalID =
PersonalAbility.PersonalID) INNER JOIN PersonalPeople ON Personal.PersonalID
= PersonalPeople.PersonalID) INNER JOIN PersonalArea ON Personal.PersonalID
= PersonalArea.PersonalID WHERE PersonalPeople=1;

 

[middletree]

For most of your questions, I just answered them in my post to Bob, right
above yours.

I added DISTINCT to see what happens, but it now shows 3 users, regardless
of what was selected.

 

[TomB]

Yes, but if you run that in Access, you'll see that your three records are
all DISTINCT.
1. Joe Dirt PersonalID=215
2. Joe Dirt PersonalID=217
3. joe Doe PersonalID=218

 

[middletree]

Yes, I understand that. That definitely solves part of the problem. I'm
glad you caught that.

 

[TomB]

Now you need to put your IFs back in there to limit the results.