Query for returning info from 2 columns

[Sun Zaza]

Hi,

I have some problem with a query.

I added a new table to the 4 images database (TEAMS_TABLE).

I have this information in my table:

Team home    Team away   Info home    Info away
Barcelona       Valencia               W               V
Sevilla           Barcelona              G                G
Barcelona       Madrid                  V                W


I need a query to return the info home & away of the club Barcelona.

Something like that:

Barcelona: W, G, V

I could get only the info home of Barcelona and also only the info away.

But the problem is how can I get both info home & away of the club Barcelona in one table or line.

I appreciate any help.

Many thanks in advance,
Cruxy

[V@nо]

maybe something like this:

Code: [Select]

SELECT *.h, team_home.a AS team_home_away, team_away.a AS team_away_away, info_home.a AS info_home_away, info_away.a AS info_away_away
FROM teams_table h
LEFT JOIN teams_table a ON team_away.a = team_home.h
WHERE team_home.h = 'barcelona'[\code]

[Sun Zaza]

Thanks V@no for your reply. I tried your query many times, but without success.

I will give you a working code, so maybe you get a better idea about what I am looking for.

To return the HOME information (uitslagen_thuis_streak = info home) of that team ($teams_id = Barcelona) I used this query:

Code: [Select]

$sql = "SELECT u.uitslagen_thuis_streak, u.uitslagen_uit_streak, u.uitslagen_thuis, u.uitslagen_uit, u.uitslagen_id, tt.teams_id, tu.teams_id
        FROM ".UITSLAGEN_TABLE." u
       
        LEFT JOIN ".TEAMS_TABLE." tt ON (u.uitslagen_thuis = tt.teams_name)
        LEFT JOIN ".TEAMS_TABLE." tu ON (u.uitslagen_uit = tu.teams_name) 
     
        WHERE $teams_id = tt.teams_id
        ORDER BY u.uitslagen_datum ASC       
        ";       
        $result = $site_db->query($sql);
        $num_rows = $site_db->get_numrows($result);       

        while ($uitslagen_row = $site_db->fetch_array($result)) {       
       
        $streak .= "".$uitslagen_row['uitslagen_thuis_streak']."<BR/>";           
        }
        $site_template->register_vars("streak", $streak);
The result is:

W
V


To return the AWAY information (uitslagen_uit_streak = Info away) of that team ($teams_id = Barcelona) I used this query:

Code: [Select]

$sql = "SELECT u.uitslagen_thuis_streak, u.uitslagen_uit_streak, u.uitslagen_thuis, u.uitslagen_uit, u.uitslagen_id, tt.teams_id, tu.teams_id
        FROM ".UITSLAGEN_TABLE." u
       
        LEFT JOIN ".TEAMS_TABLE." tt ON (u.uitslagen_thuis = tt.teams_name)
        LEFT JOIN ".TEAMS_TABLE." tu ON (u.uitslagen_uit = tu.teams_name) 
     
        WHERE $teams_id = tu.teams_id
        ORDER BY u.uitslagen_datum ASC       
        ";       
        $result = $site_db->query($sql);
        $num_rows = $site_db->get_numrows($result);       

        while ($uitslagen_row = $site_db->fetch_array($result)) {       
       
        $streak .= "".$uitslagen_row['uitslagen_uit_streak']."<BR/>";           
        }
        $site_template->register_vars("streak", $streak);
The result is:
G

Now I want to combine those two query in one query.

I need to return the result like this:

W
G
V


I already solved it with PHP, but I need the query table to use in other query (FROM (SQL 2) A ....)
Check the PHP solution:

Code: [Select]

$sql = "SELECT u.uitslagen_thuis_streak, u.uitslagen_uit_streak, u.uitslagen_thuis, u.uitslagen_uit, u.uitslagen_id, u.uitslagen_datum, uitslagen_speelronde,  tt.teams_id, tu.teams_id, tt.teams_name AS teamsTHUIS, tu.teams_name AS teamsUIT
        FROM ".UITSLAGEN_TABLE." u

LEFT JOIN ".TEAMS_TABLE." tt ON (u.uitslagen_thuis = tt.teams_name)
LEFT JOIN ".TEAMS_TABLE." tu ON (u.uitslagen_uit = tu.teams_name)

WHERE $teams_id = tt.teams_id OR $teams_id = tu.teams_id
ORDER BY u.uitslagen_datum ASC
";
$result = $site_db->query($sql);
$num_rows = $site_db->get_numrows($result);

while ($uitslagen_row = $site_db->fetch_array($result)) {


if ($uitslagen_row['uitslagen_thuis'] != $teamsNAME AND $uitslagen_row['uitslagen_thuis_streak'] == 'W')
{
$streak_teams = 'V';
}
elseif ($uitslagen_row['uitslagen_thuis'] != $ teamsNAME AND $uitslagen_row['uitslagen_thuis_streak'] == 'V')
{
$streak_teams = 'W';
}
else
{
$streak_teams = $uitslagen_row['uitslagen_thuis_streak'];
}

$streak .= "".$uitslagen_row['uitslagen_speelronde']." (".date("d-m-Y", $uitslagen_row['uitslagen_datum']).") ".$streak_teams." ".$uitslagen_row['uitslagen_thuis']."<BR/>";
}
$site_template->register_vars("streak", $streak);

NB: I already contacted PHPFREAKS.COM and also the biggest PHP forum in the Netherlands, but nobody could code this query yet.

Am I requesting something impossible from MySql?

[V@nо]

I won't be able look into it until late this evening, but I bet you would get more chanses get the answer if you ask it at the approriate site - mysql.com

[Sun Zaza]

No problem V@no. I am free tomorrow. I will be waiting for your answer.

but I bet you would get more chanses get the answer if you ask it at the approriate site - mysql.com

I will do it right now.

NB: Of course I will let you know if I solve it before you start looking for it.

[Sun Zaza]

Hi V@no,

Unfortunately I could not create a new topic on MySql.com. I believe they have a problem with the scripts of the forum.

Never mind! I hope you have time to give me the solution of this issue.


Thank you in advance,