Re: SQL syntax--getting demoralized
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.Prefer redName,
Personal.Person alID
FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON Personal.Person alID =
PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON Personal.Person alID
= PersonalPeople. PersonalID) INNER JOIN PersonalArea ON Personal.Person alID
= PersonalArea.Pe rsonalID WHERE PersonalPeople= 1;
"middletree " <middletree@hto mail.com> wrote in message
news:u3mrcjg1DH A.2060@TK2MSFTN GP10.phx.gbl...[color=blue]
> Well, there is good news and bad news. I tried it out (you can, too, just[/color]
go[color=blue]
> 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!
>
>
>
> "TomB" <shuckle@hotmai lXXX.com> wrote in message
> news:um1DrRg1DH A.1704@tk2msftn gp13.phx.gbl...[color=green]
> > I have a feeling all of those inner joins aren't nesting the way you[/color][/color]
want[color=blue][color=green]
> > 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[/color]
> Personal[color=green]
> > item and an item for each of the four other tables, then a join in each[/color][/color]
of[color=blue][color=green]
> > 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.Prefer redName,
> > Personal.Person alID
> > FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
> > PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON[/color][/color]
Personal.Person alID[color=blue]
> =[color=green]
> > PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON[/color]
> Personal.Person alID[color=green]
> > = PersonalPeople. PersonalID) INNER JOIN PersonalArea ON[/color]
> Personal.Person alID[color=green]
> > = PersonalArea.Pe rsonalID
> >
> > You'll notice I took out the aliases (PersonalAbilit y PAB) I'm not sure[/color][/color]
if[color=blue][color=green]
> > they were causing the problem (they shouldn't have been) but I figured[/color][/color]
I'd[color=blue][color=green]
> > 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 " <middletree@hto mail.com> wrote in message
> > news:utggLva1DH A.2388@TK2MSFTN GP09.phx.gbl...[color=darkred]
> > > I've seen posts here and elsewhere which read something along the[/color][/color][/color]
lines[color=blue]
> of[color=green][color=darkred]
> > > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP! ". Well, I know that[/color][/color][/color]
kind[color=blue]
> of[color=green][color=darkred]
> > > subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
> been[color=green][color=darkred]
> > > struggling for days--days!!-- on this one simple query. I really need[/color][/color]
> to[color=green][color=darkred]
> > > get past this thing and move on. Please help.
> > >
> > > I have a classic ASP page, and it gives you 4 dropdowns. You can[/color][/color][/color]
select[color=blue][color=green]
> > any[color=darkred]
> > > or none of them. Each of them is built by an ACCESS table. These 4[/color][/color]
> tables[color=green][color=darkred]
> > > are union tables; they have just 2 fields: the PK from the master[/color][/color][/color]
table,[color=blue][color=green][color=darkred]
> > > called Personal, and one of the 4 static tables that I call People,[/color][/color]
> Area,[color=green][color=darkred]
> > > Ability, Gift.
> > >
> > > Anyway, when someone selects a value from one or more of the[/color][/color][/color]
dropdowns,[color=blue][color=green]
> > the[color=darkred]
> > > next page grabs the values and puts them into variables (strGiftID,
> > > strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
> into[color=green][color=darkred]
> > > 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 = "&strPeople ID&" 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[/color][/color][/color]
P.PersonalID[color=blue]
> =[color=green][color=darkred]
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
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.Prefer redName,
Personal.Person alID
FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON Personal.Person alID =
PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON Personal.Person alID
= PersonalPeople. PersonalID) INNER JOIN PersonalArea ON Personal.Person alID
= PersonalArea.Pe rsonalID WHERE PersonalPeople= 1;
"middletree " <middletree@hto mail.com> wrote in message
news:u3mrcjg1DH A.2060@TK2MSFTN GP10.phx.gbl...[color=blue]
> Well, there is good news and bad news. I tried it out (you can, too, just[/color]
go[color=blue]
> 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!
>
>
>
> "TomB" <shuckle@hotmai lXXX.com> wrote in message
> news:um1DrRg1DH A.1704@tk2msftn gp13.phx.gbl...[color=green]
> > I have a feeling all of those inner joins aren't nesting the way you[/color][/color]
want[color=blue][color=green]
> > 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[/color]
> Personal[color=green]
> > item and an item for each of the four other tables, then a join in each[/color][/color]
of[color=blue][color=green]
> > 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.Prefer redName,
> > Personal.Person alID
> > FROM (((Personal INNER JOIN PersonalGift ON Personal.Person alID =
> > PersonalGift.Pe rsonalID) INNER JOIN PersonalAbility ON[/color][/color]
Personal.Person alID[color=blue]
> =[color=green]
> > PersonalAbility .PersonalID) INNER JOIN PersonalPeople ON[/color]
> Personal.Person alID[color=green]
> > = PersonalPeople. PersonalID) INNER JOIN PersonalArea ON[/color]
> Personal.Person alID[color=green]
> > = PersonalArea.Pe rsonalID
> >
> > You'll notice I took out the aliases (PersonalAbilit y PAB) I'm not sure[/color][/color]
if[color=blue][color=green]
> > they were causing the problem (they shouldn't have been) but I figured[/color][/color]
I'd[color=blue][color=green]
> > 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 " <middletree@hto mail.com> wrote in message
> > news:utggLva1DH A.2388@TK2MSFTN GP09.phx.gbl...[color=darkred]
> > > I've seen posts here and elsewhere which read something along the[/color][/color][/color]
lines[color=blue]
> of[color=green][color=darkred]
> > > "PULLNG MY HAIR OUT!!!!!" or "HELLLLPPP! ". Well, I know that[/color][/color][/color]
kind[color=blue]
> of[color=green][color=darkred]
> > > subject line isn't descriptive, but I sure can relate right now. I've[/color][/color]
> been[color=green][color=darkred]
> > > struggling for days--days!!-- on this one simple query. I really need[/color][/color]
> to[color=green][color=darkred]
> > > get past this thing and move on. Please help.
> > >
> > > I have a classic ASP page, and it gives you 4 dropdowns. You can[/color][/color][/color]
select[color=blue][color=green]
> > any[color=darkred]
> > > or none of them. Each of them is built by an ACCESS table. These 4[/color][/color]
> tables[color=green][color=darkred]
> > > are union tables; they have just 2 fields: the PK from the master[/color][/color][/color]
table,[color=blue][color=green][color=darkred]
> > > called Personal, and one of the 4 static tables that I call People,[/color][/color]
> Area,[color=green][color=darkred]
> > > Ability, Gift.
> > >
> > > Anyway, when someone selects a value from one or more of the[/color][/color][/color]
dropdowns,[color=blue][color=green]
> > the[color=darkred]
> > > next page grabs the values and puts them into variables (strGiftID,
> > > strAbilityID, strAreaID, and strPeopleID). This is where I am running[/color][/color]
> into[color=green][color=darkred]
> > > 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 = "&strPeople ID&" 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[/color][/color][/color]
P.PersonalID[color=blue]
> =[color=green][color=darkred]
> > > 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
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >[/color]
> >
> >[/color]
>
>[/color]
Comment