[RESOLVED] VB6 Access SQL string returning dups

[longwolf]

I have an Access DB with a LOT of tables in it.
One of them is tblSessionLog

tblSessionLog holds, SessionID, CaID, PLID and sevaral other things

The tblCasino links to tblCity which links to tblState, etc
tblPlayer links to several more tables.

All IDs ar unque primary keys.

At this piont there are almost 200 businesses and a couple of dozen Players in the tables
But there are only 2 log entries.

I'm trying to pull those 2 log entries, along with all the other info they chain to.

Here's the string I'm using;

    sSQL = "SELECT * FROM tblSessionLog tS, tblHeat tH, tblPlayer tPl, tblAlias tA, " & _
           "tblPersona tP, tblBodyType tB, tblHairColor tHC, tblHairLength tHL, " & _
           "tblFacialHair tFH, tblMustache tM, tblEyeColor tE, tblGlasses tG, tblClothing tCl, " & _
           "tblTable tT, tblCasino tCa, tblCity tCi, tblState tSt, tblCountry tCnt " & _
           "WHERE tS.UserID = " & CStr(g_lUserID) & " AND tS.CaID = tCa.CaID AND tH.HID = tS.HID " & _
           "AND tS.PLID = tPl.PLID AND tA.AID = tPl.AID AND tP.PID = tPl.PID " & _
           "AND tB.BID = tPl.BID AND tHC.HCID = tPl.HCID AND tHL.HLID = tPl.HLID " & _
           "AND tFH.FID = tPl.FID AND tM.MID = tPl.MID AND tE.EID = tPl.EID AND " & _
           "tG.GID = tPl.GID"

It pulls the info, but when I check the RecordCount it holds 638820!

What do I do here?

[si_the_geek]

You've probably missed a join condition or two, but due to the syntax you have used it is very hard to tell - it is much clearer if you specify the join conditions in the From clause rather than the Where clause, for example rather than this:

...
FROM tableA, tableB, tableC

WHERE tableA.field1 = tableB.field2
AND tableB.field4 = tableC.field4
...

try arranging it like this:

...
FROM tableA
INNER JOIN tableB ON (tableA.field1 = tableB.field2)
INNER JOIN tableC ON (tableB.field4 = tableC.field4)

WHERE ...

Just doing that may be enough for you to spot the problem, but if not it will at least make it easier for us to check.

[longwolf]

All of the fields that are tested in the WHERE section already have the Relationships set/linked in the data base.

So would the JOIN make it work differently?

[GaryMazzone]

The use of Inner Join in the From Clasue rather then the = in a Where clause is the current ANSI standard of SQL

[longwolf]

So was I using implied 'Inner Join's in my WHERE section?

I've never written a SQL string the way being suggested so I'm trying to understand.

[si_the_geek]

The relationships you have set in the database mean nothing to SQL statements, you need to specify which joins to use - either in the Where clause as you did (which is implied Inner Joins), or in the From clause as I did (easier to read/check, and allows you to specify the type of join).

While needing to specify the joins in your SQL statements means a bit more typing, it gives you much more flexibility - such as being able to use a Left Join in some circumstances but not in others.

[longwolf]

Thx Si.

I just tried this, but it's getting a 'Missing Operator" error.

    sSQL = "SELECT * FROM tblSessionLog " & _
           "INNER JOIN tblCasino ON (tblSessionLog.CaID = tblCasino.CaID) " & _
           "INNER JOIN tblCity ON (tblCity.CityID = tblCasino.CityID) " & _
           "INNER JOIN tblState ON (tblState.StateID = tblCity.StateID) " & _
           "INNER JOIN tblCountry ON (tblCountry.CntID = tblState.CntID) " & _
           "INNER JOIN tblCasino ON (tblSessionLog.HID = tblHeat.HID) " & _
           "INNER JOIN tblCasino ON (tblSessionLog.PLID = tblPlayer.PLID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.AID = tblAlias.AID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.PID = tblPersona.PID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.BID = tblBodyType.BID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.HCID = tblHairColor.HCID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.HLID = tblHairLength.HLID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.FID = tblFacialHair.FID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.MID = tblMustache.MID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.EID = tblEyeColor.EID) " & _
           "INNER JOIN tblPlayer ON (tblPlayer.GID = tblGlasses.GID) " & _
           "WHERE tblSessionLog.UserID = " & CStr(g_lUserID)

[si_the_geek]

It might not be working at the moment, but at least it's much easier to read!

I suspect the problem is caused by a little mistake you made, in each Join clause the table name should be a different table to the previous Joins (and the ON part says how to join it to the previous tables).

You had it right up to the first tblState line, but then seemed to lose track - the second tblState should be tblCountry instead, etc. The ON conditions you specified seem to be fine, it's just the table names to correct.


edit: ah, I just noticed Access in the thread title.. in that case you need to remove the brackets (and perhaps re-add them in a different place).

[longwolf]

Ok
This new one does make more sense.
I've tried it with and without the the brackets.

But I'm still getting the 'Missing Operator" error.

I have no idea of how else to place the brackets.

    sSQL = "SELECT * FROM tblSessionLog " & _
           "INNER JOIN tblCasino ON tblSessionLog.CaID = tblCasino.CaID " & _
           "INNER JOIN tblCity ON tblCity.CityID = tblCasino.CityID " & _
           "INNER JOIN tblState ON tblState.StateID = tblCity.StateID " & _
           "INNER JOIN tblCountry ON tblCountry.CntID = tblState.CntID " & _
           "INNER JOIN tblHeat ON tblSessionLog.HID = tblHeat.HID " & _
           "INNER JOIN tblPlayer ON tblSessionLog.PLID = tblPlayer.PLID " & _
           "INNER JOIN tblAlias ON tblPlayer.AID = tblAlias.AID " & _
           "INNER JOIN tblPersona ON tblPlayer.PID = tblPersona.PID " & _
           "INNER JOIN tblBodyType ON tblPlayer.BID = tblBodyType.BID " & _
           "INNER JOIN tblHairColor ON tblPlayer.HCID = tblHairColor.HCID " & _
           "INNER JOIN tblHairLength ON tblPlayer.HLID = tblHairLength.HLID " & _
           "INNER JOIN tblFacialHair ON tblPlayer.FID = tblFacialHair.FID " & _
           "INNER JOIN tblMustache ON tblPlayer.MID = tblMustache.MID " & _
           "INNER JOIN tblEyeColor ON tblPlayer.EID = tblEyeColor.EID " & _
           "INNER JOIN tblGlasses ON tblPlayer.GID = tblGlasses.GID " & _
           "WHERE tblSessionLog.UserID = " & CStr(g_lUserID)

[si_the_geek]

I don't often use Access, but from what I remember you need to use brackets to nest each of the joins.

Here's a shortened version to try out:

sSQL = "SELECT * FROM ((tblSessionLog " & _
           "INNER JOIN tblCasino ON tblSessionLog.CaID = tblCasino.CaID) " & _
           "INNER JOIN tblCity ON tblCity.CityID = tblCasino.CityID) " & _
           "INNER JOIN tblState ON tblState.StateID = tblCity.StateID " & _
           "WHERE tblSessionLog.UserID = " & CStr(g_lUserID)

If it doesn't work, try it without the Where clause.

[longwolf]

Hot dog, That did it.

Thx Si, I'd have never guessed this one

looks kinda strange:

    sSQL = "SELECT * FROM ((((((((((((((tblSessionLog " & _
               'etc

But it's only pulling the two records!

[si_the_geek]

It's a weird one - I've got no idea why they decided it should be like that!