[RESOLVED] Inner Join

jarg

Hi All,

im trying to join to tables together have managed to get the correct responses with the following mysql select statement:

SELECT regatta_results_08.raceid, regatta_results_08.winningcrewid, regatta_results_08.loosingcrewid, regatta_crews_08.crewid, regatta_crews_08.club FROM regatta_crews_08
INNER JOIN regatta_results_08 ON regatta_crews_08.crewid = regatta_results_08.winningcrewid

The problem that i am having is that i want the join to also select from another column, so idealy it would be :
"INNER JOIN regatta_results_08 ON regatta_crews_08.crewid = regatta_results_08.winningcrewid OR regatta_results_08.loosingcrewid"

This however does not work.

Anybody got any ideas as to how to solve this issue?

Thanks,
James

Lars_Berg

I suppose you want the club name of both the winning and loosing crew

SELECT regatta_results_08.raceid, 
       regatta_results_08.winningcrewid, 
       regatta_results_08.loosingcrewid, 
       won.club as winner,
       lost.club as looser 
  FROM regatta_crews_08 as won
 INNER JOIN regatta_results_08 
    ON won.crewid = regatta_results_08.winningcrewid 
 INNER JOIN regatta_crews_08 as lost
    ON lost.crewid = regatta_results_08.loosingcrewid

jarg

That works a treat many thanks,

i just need to filter the clubs now so for example if i want to filter for a specific club it will return clubs that have both won and lost.

I can't seem to get it to filter when i put an OR statement in, can filter for individual winners or losers;

SELECT regatta_results_08.raceid, regatta_results_08.winningcrewid, regatta_results_08.loosingcrewid, won.club as winner, lost.club as looser
FROM regatta_crews_08 as won 
 INNER JOIN regatta_results_08 ON won.crewid =  regatta_results_08.winningcrewid 
INNER JOIN regatta_crews_08 as lost ON lost.crewid = regatta_results_08.loosingcrewid 
WHERE won.club OR lost.club = 'Warwick BC'

Any ideas?
Thanks

Lars_Berg

When you write

WHERE won.club OR lost.club = 'Warwick BC'

this is interpreted as

where (won.club) or (lost.club = 'Warwick BC')

i.e. the DBMS sees won.club as a boolean value, i.e. something that is either true or false.
Since club is presumably defined as varchar this does not make sense but some DBMS allows this.

What you need is to specify separate conditions

WHERE won.club = 'Warwick BC' OR lost.club = 'Warwick BC'

this can be simplified to

WHERE  'Warwick BC' in (won.club,lost.club)

jarg

Thanks, thats got the SQL working well.

Having one more problem though,
am using jquery and ajax to run and pass through query's.

the problem that i am having is that i am passing query's with spaces in such as 'Warwick BC' when i then post this with jquery to my PHP page, it posts it as 'Warwick+BC' whereas the PHP normally accepts %20.

Thus i think it is causing me some problems.
Anyone got any ideas how to solve this?

Thanks,
James