Member Avatar for borat

Hi people.
This is my first thread, i hope you can help me.
I'm a newbie and my english is so bad, but, i hope you can understand me. this is my question:

I've a table TURN with this field
IDTURN (int)
SEASON (string)
SORT (string)

another table GROUP
IDGROUP (int)
IDTURN (int)

a table MATCH
IDMATCH (int)
IDGROUP (int)
IDHOMETEAM (int)
GOLHOMETEAM (int)

and i've another table TRAINER_TEAM
IDTRAINER (int)
IDTEAM (int)
IDTURN_FROM (int)
IDTURN_TO (int)

in this table i've an ID for the trainer, the id of team and the duration of the experience of this trainer with this team. The duration is set up by an IDTURN_FROM and an IDTURN_TO.

i'll want to

SELECT SUM OF GOLHOMETEAM FROM MATCH TABLE WHERE TRAINER_TEAM.IDTEAM = MATCH.IDHOMETEAM AND MATCH.IDGROUP.IDTURN.SORT >= TRAINER_TEAM.IDTURN_FROM.SORT AND MATCH.IDGROUP.IDTURN.SORT <= TRAINER_TEAM.IDTURN_TO.SORT
AND TRAINER_TEAM.IDTRAINER=5

i hope is understandable.
please help, is important!!

thank u danyweb!

  • 2 Contributors
  • 1 Reply
  • 113 Views
  • 11 Hours Discussion Span
  • Latest Post Latest Post by Traicey
Member Avatar for Traicey

i'll want to SELECT SUM OF GOLHOMETEAM FROM MATCH TABLE WHERE TRAINER_TEAM.IDTEAM = MATCH.IDHOMETEAM AND MATCH.IDGROUP.IDTURN.SORT >= TRAINER_TEAM.IDTURN_FROM.SORT AND MATCH.IDGROUP.IDTURN.SORT <= TRAINER_TEAM.IDTURN_TO.SORT
AND TRAINER_TEAM.IDTRAINER=5

1. You dont have IDTURN on Match Table so you cannot join that table with IDTURN, u have IDTURN on TURN, TRAINER_TEAM and Group table, so those are the only tables you can use to join or complare the IDTURN attributes so I have used the IDTURN on TURN table coz I suppose that IDTURN is original coming from that table

2. I dont understand what you mean about the .SORT coz as far as I understand your tables, SORT is a TURN table attribute,

3. I dont know what u wanted exactly I just guessed that U needed help with the quiry, the following is more like the code u posted above, I have used abbriviation for the tables that we need to join the tables in order to accomplish the requirements of the quiry so M short for Match table is there to tell the system that evrywhere it sees M it has to go beck and read from Match Table, same goes to the rest of tables

Select                   Sum(GOLHOMETEAM)
From                     Match M,  Trainer_Team T, Turn U
Where                   T.IDTEAM = M.IDHOMETEAM
AND                      U.IDTURN  >=  T.IDTURN_FROM 
AND                      U.IDTURN <= T.IDTURN_TO
AND                      T.IDTRAINER = 5
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.