hello,
I am using mysql 3.23.49 on debian stable.
I have two tables:
One with teams - fields are team_no (int) and team_name (varchar).
The other table contains matches. i.e. two teams play agst each other
- fields are home_team (int), away_team (int), home_goal (int) and
away_goal (int).
I want to run a query that will show the matches, but replace the team
numbers from the home_team and away_team fields with the team names
from the team table.
I thought i could use a nested query, such as
select (select team_name from teams where
teams.team_no=matches.home_team), (select team_name from teams where
teams.team_no=matches.away_team) from matches;
but i believe mysql cannot do this with 3.23. Not sure if joins will
help?
any idea how to help? thanks,
KGee
4  2051 
KGee wrote: hello,
I am using mysql 3.23.49 on debian stable.
I have two tables:
One with teams - fields are team_no (int) and team_name (varchar).
The other table contains matches. i.e. two teams play agst each other
- fields are home_team (int), away_team (int), home_goal (int) and
away_goal (int).
I want to run a query that will show the matches, but replace the team
numbers from the home_team and away_team fields with the team names
from the team table.
I thought i could use a nested query, such as
select (select team_name from teams where
teams.team_no=matches.home_team), (select team_name from teams where
teams.team_no=matches.away_team) from matches;
but i believe mysql cannot do this with 3.23. Not sure if joins will
help?
any idea how to help? thanks,
KGee
something like
SELECT t1.team_name, t2.team_name FROM matches m LEFT JOIN teams t1 ON
m.home_team = t1.team_no LEFT JOIN teams t2 ON m.away_team = t2.team_no;
KGee wrote: hello,
I am using mysql 3.23.49 on debian stable.
I have two tables:
One with teams - fields are team_no (int) and team_name (varchar).
The other table contains matches. i.e. two teams play agst each other
- fields are home_team (int), away_team (int), home_goal (int) and
away_goal (int).
I want to run a query that will show the matches, but replace the team
numbers from the home_team and away_team fields with the team names
from the team table.
I thought i could use a nested query, such as
select (select team_name from teams where
teams.team_no=matches.home_team), (select team_name from teams where
teams.team_no=matches.away_team) from matches;
but i believe mysql cannot do this with 3.23. Not sure if joins will
help?
any idea how to help? thanks,
KGee
something like
SELECT t1.team_name, t2.team_name FROM matches m LEFT JOIN teams t1 ON
m.home_team = t1.team_no LEFT JOIN teams t2 ON m.away_team = t2.team_no;
Bruce Wolk <fa**@not-a-real-address.net> wrote in message news:<H8****************@newssvr25.news.prodigy.co m>... KGee wrote: hello,
I am using mysql 3.23.49 on debian stable.
I have two tables:
One with teams - fields are team_no (int) and team_name (varchar).
The other table contains matches. i.e. two teams play agst each other
- fields are home_team (int), away_team (int), home_goal (int) and
away_goal (int).
I want to run a query that will show the matches, but replace the team
numbers from the home_team and away_team fields with the team names
from the team table.
I thought i could use a nested query, such as
select (select team_name from teams where
teams.team_no=matches.home_team), (select team_name from teams where
teams.team_no=matches.away_team) from matches;
but i believe mysql cannot do this with 3.23. Not sure if joins will
help?
any idea how to help? thanks,
KGee
something like
SELECT t1.team_name, t2.team_name FROM matches m LEFT JOIN teams t1 ON
m.home_team = t1.team_no LEFT JOIN teams t2 ON m.away_team = t2.team_no;
Thanks Bruce!
I just figured it out a few hours ago. used exactly the same code as
you. it is encouraging for me as i know i am doing something right.
Bruce Wolk <fa**@not-a-real-address.net> wrote in message news:<H8****************@newssvr25.news.prodigy.co m>... KGee wrote: hello,
I am using mysql 3.23.49 on debian stable.
I have two tables:
One with teams - fields are team_no (int) and team_name (varchar).
The other table contains matches. i.e. two teams play agst each other
- fields are home_team (int), away_team (int), home_goal (int) and
away_goal (int).
I want to run a query that will show the matches, but replace the team
numbers from the home_team and away_team fields with the team names
from the team table.
I thought i could use a nested query, such as
select (select team_name from teams where
teams.team_no=matches.home_team), (select team_name from teams where
teams.team_no=matches.away_team) from matches;
but i believe mysql cannot do this with 3.23. Not sure if joins will
help?
any idea how to help? thanks,
KGee
something like
SELECT t1.team_name, t2.team_name FROM matches m LEFT JOIN teams t1 ON
m.home_team = t1.team_no LEFT JOIN teams t2 ON m.away_team = t2.team_no;
Thanks Bruce!
I just figured it out a few hours ago. used exactly the same code as
you. it is encouraging for me as i know i am doing something right. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Prem |
last post by:
Hi,
I am having many problems with inner join. my first problem is :
1) I want to know the precedance while evaluating query with multiple
joins.
eg.
select Employees.FirstName,...
|
by: Keith |
last post by:
I am having a problem creating a many-to-many-to-many type relationship. It
works fine, but when I create a view to query it and test it, it does not
generate the results I expected.
Below...
|
by: Preston Landers |
last post by:
Hello all.
I am trying to write a query that "just" switches some data around so
it is shown in a slightly different format. I am already able to do
what I want in Oracle 8i, but I am having...
|
by: Ciar?n |
last post by:
I have about 7 tables I need to join, but am having a lot of
difficulty with the joins, that I need some help on.
I'll provide the details of four tables to illustrate the scenario.
I have one...
|
by: michaelnewport |
last post by:
Greetings,
I like to write my inner joins as below,
but someone at work tells me its not as 'performant'
as using the 'inner join' statement.
Is this true ?
Is there a better way to write it...
|
by: Steve |
last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three
"Left Outer Joins" in order to return every transaction for a specific
set of criteria.
Using three "Left Outer Joins"...
|
by: Phil W |
last post by:
Hello,
I have set up my database in access using many to many relationships (it's
the good ol' books and authors one again). I've actually extended it to
include other people who contribute in...
|
by: khan |
last post by:
i have 2 queries totaldues and totalPayments
totaldues has 2 fileds(custID (Group By)
amountDue(sum of all invoices dues)
and totalPayments consist of CustID(Group By) and Amount_Paid(sum of
all...
|
by: gkellymail |
last post by:
the following query works fine:
select link.idx, link.x_table, link.x_id_a, link.x_id_z, a.strandid,
b.strandid
from link_detail, link, strand A, strand B
where link_detail.x_table =...
|
by: TC |
last post by:
I've used Access for many years. Several times, I've encountered a bug
which I refer to as the "Vanishing Joins" bug. When it happens, joins
vanish randomly from queries. More specifically, all...
|
by: taylorcarr |
last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |
