using DISTINCT with 2 tables INNER JOIN'ed (SQL syntax) 2

[ADoozer]

SQLStr = "SELECT PlayerName FROM PlayersInHandData AS PIHD INNER JOIN ActionsData AS AD ON PIHD.HandID=AD.HandID WHERE AD.ActionType='DealCards' AND PIHD.SeatNumber=AD.SeatNumber"

now that SQL command works, but returns multiple instances of the same PlayerName.

i tried adding a DISTINCT into the statement to stop duplicates

SQLStr = "SELECT DISTINCT PlayerName FROM PlayersInHandData AS PIHD INNER JOIN ActionsData AS AD ON PIHD.HandID=AD.HandID WHERE AD.ActionType='DealCards' AND PIHD.SeatNumber=AD.SeatNumber"

which does stop the duplicates.. but also returns the wrong results

(it seems as if it is ignoring the AND PIHD.SeatNumber=AD.SeatNumber bit)

i can easily enough just filter out all duplicate names in code.. however if its possible to do it within the SQL statement id rather do that

Is it possible to use DISTINCT on tables INNER JOIN'ed? and if so how do i group my WHERE's so that both the criteria are met.

thanks in advance

If somethings hard to do, its not worth doing - Homer Simpson

 

[unclerico]

try it this way:

SQLStr = "SELECT DISTINCT PlayerName FROM PlayersInHandData AS PIHD INNER JOIN ActionsData AS AD ON PIHD.HandID=AD.HandID AND PIHD.SeatNumber=AD.SeatNumber WHERE AD.ActionType='DealCards'"

 

[ADoozer]

thnx...

unfortunately that doesnt work either



If somethings hard to do, its not worth doing - Homer Simpson

 

[Jasen]

Have a sample of what your tables look like, and what result you are looking for? What kind of DB?
You should have no problem using 'distinct' in queries with joins. I'm thinking the problem is more likely with the join or where clauses.

 

[ADoozer]

Table: PlayersInHand
Field1:HandID
Field2:SeatNumber
Field3:PlayerName

Table: ActionsData
Field1:HandID
Field2:ActionType
Field3:SeatNumber

in ActionsData there will only ever be 1 ActionType='DealCards' for each HandID

some sample data

Table: PlayersInHand
HandID      1
SeatNumber  1
PlayerName  Bob
HandID      1
SeatNumber  2
PlayerName  Fred
HandID      1
SeatNumber  3
PlayerName  Percy
HandID      1
SeatNumber  4
PlayerName  Josephine
HandID      2
SeatNumber  1
PlayerName  Bob
HandID      2
SeatNumber  4
PlayerName  Percy
HandID      1
SeatNumber  6
PlayerName  Josephine

Table: ActionsData
HandID      1
ActionType  'SmallBlind'
SeatNumber  2
HandID      1
ActionType  'BigBlind'
SeatNumber  3
HandID      1
ActionType  'DealCards'
SeatNumber  1
HandID      2
ActionType  'SmallBlind'
SeatNumber  3
HandID      2
ActionType  'BigBlind'
SeatNumber  1
HandID      2
ActionType  'DealCards'
SeatNumber  1

with my first example (not using DISTINCT) the query would (in this case) return 2 records both with PlayerName=Bob

wheras if i use DISTINCT it would return 3 records Bob,Percy,Josephine

obviously the first scenario is what i want (minus the duplicates)

its an Access2000 MDB accessed using Jet 4.0 and ADO

If somethings hard to do, its not worth doing - Homer Simpson

 

[petermeachem]

I get 2 bobs without the distinct and 1 bob with distinct. I suppose you are quite sure you are doing this as you say you are? I can't get Josephine at all because seats 4 and 6 are not the first table.

http://www.accuflight.co.uk

http://www.lettersdirect.co.uk

http://www.trelliscoaching.com

http://www.fengshuimarketingcenter.com

http://www.ourhouseinportugal.co.uk

 

[ADoozer]

the data i presented was simplified to try and explain the problem.

the original dataset had 16000+ HandIDs of info (44Mb total) so its not practical to upload but, ive created another dataset that has the problem (2500ish HandIDs [approx 1meg zipped]) if you want to use actual data, its available here

http://homepage.ntlworld.com/drigby01/Tracker.zip

the EXACT sql string is

SQLStr = "SELECT DISTINCT PlayerName FROM PlayersInHandData AS PIHD INNER JOIN ActionsData AS AD ON PIHD.HandID=AD.HandID WHERE PIHD.SeatNumber=AD.SeatNumber AND AD.ActionType='DealCards'"

thanx in advance

If somethings hard to do, its not worth doing - Homer Simpson

 

[gmmastros]

When I run your query directly in Access, I get 3 records. When I remove the distinct (and add an Order By PIHD.PlayerName), I get 1771 records where all but 2 records are ADoozer.

What is the expected outcome from the data, with your query?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ADoozer]

scratch that. seems to be a problem with the data
sorry
its been a long day [coffee]

If somethings hard to do, its not worth doing - Homer Simpson

 

[ADoozer]

thankfully its not my code thats squiffy.

the XML code (where i create all my data from) in the original SQLite database is corrupt (unless you can have 20 players at a 10 seat table)

so this leads to me and MadMan28 being sat in the same seat (number 7), im assuming this is also the case where Ste7 is credited as being DealtCards (something i am about to check now)

thnx again.. stars for your patience!

If somethings hard to do, its not worth doing - Homer Simpson