select problem

[MisterT77]

Hi...
I want to do a query on different tables::
1. tip (spiele_id, user_id)
2. teams (team_id, team_name, flag)

I got this select query::

select s.*, m1.team_name as homename, 
m2.team_name as guestname, m3.flag as flaghome, m4.flag as flagguest, 
t1.tip_home as tiphome, t2.tip_guest as tipguest from wgc_spiele s
left join wgc_teams m1 on m1.team_id=s.team1
left join wgc_teams m2 on m2.team_id=s.team2
left join wgc_teams m3 on m3.team_id=s.team1
left join wgc_teams m4 on m4.team_id=s.team2 
left join wgc_tipp t1 on t1.spiele_id=s.spiele_id 
left join wgc_tipp t2 on t2.spiele_id=s.spiele_id  where  s.spieltag=$spieltag

but the problem is in those 2 lines:
left join wgc_tipp t1 on t1.spiele_id=s.spiele_id
left join wgc_tipp t2 on t2.spiele_id=s.spiele_id

I don't just need "spiele_id" from the table "tip" I do also need the "user_id" out of this table. both arguments have to fit!
U know how I can change the code?

[MisterT77]

can i change my code and use different selects in my query to solve the prob?

[the_Igel]

This query seems to be overcomplicated. I can't even understand, what it should return 🙂

First of all, it seems there's no need for LEFT JOIN, second - no need to have copies of m1 & m2 (m3 & m4 respectively). I'm too lazy to write down what the query would be like with these improvements. But maybe if you do this, you'll see the key for your problem.