Using several tables in a single SELECT statement
Brown Cat
STUDENT
studentID fullName
1 John Smith
2 John Doe
3 Bob Marley
4 George Bush
CLASS
classID className
1 Maths
2 IT
3 Spanish
4 Science
5 Colouring in
STUDENT_CLASS
studentID classID
1 1
1 3
2 1
3 1
3 2
3 4
4 5
What I want to do is select a CLASS and see which students are studying
that class. So, if I wanted to search Maths, I want to see the results:
John Smith
John Doe
Bob Marley
Obviously, I can do this by writing a script to capture the studentIDs
and then fetch each name. But is it possible to do this in a single SQL
statement?
Erick T. Barkhuis
> I have the following tables:
> STUDENT
> CLASS
> STUDENT_CLASS
> What I want to do is select a CLASS and see which students are studying
> that class. [...] But is it possible to do this in a single SQL
> statement?
This appears a home work excercise to me.
Probably, you should read up on one of the most important (and basic) SQL
features: JOIN
--
Erick
Brown Cat
It's not homework, it's a personal project but I have used the very
commonly used Student/Class/Lecturer example to make the explanation a
bit more simple.
I think JOIN is what I need, thanks.
Brian Cryer
news:CmYDm.94760$5E.2...@newsfe29.ams2...
Your response indicates that Erick is right in that you need learn some
basic SQL (whether or not it is homework).
The answer you are looking for (and there are variations on this) would be
something like:
select Student.fullName
from Student, Class, Student_Class
where Student.studentID = Student_Class.studentID
and Student_Class.classID = Student_Class.classID
and Student_Class.className = 'Maths'
You might find my SQL crib sheet useful:
http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
If you are going to introduce someone to SQL, you should at least use
the better explicit JOIN syntax!
Brian Cryer
news:e2c78d23-994a-443d...@u13g2000vbb.googlegroups.com...
Whilst there are more obscure ways of doing the join, I find it generally
best to use the most straight forward and easiest to remember syntax. YMMV.
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
When join conditions and selection criteria are not clearly defined?
When the addition of a LEFT JOIN will cause terrible problems because
of the difference precedences?
Remind me not to employ you.
Brian Cryer
I find your reaction odd given that the join conditions and selection
criteria are clearly defined (although I accept that a change in syntax
would make it more explicit). Are you perhaps used to working on a database
which doesn't have an optimiser or an old version of MySQL?
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
I am used to working with many RDBMS systems including the latest
versions of MySQL.
Having an optimiser is no reason not to write clear queries.
Also, the precedence problem is still a problem no mater what version
of MySQL you are on.
I take as much care laying out my queries as I do laying out my
programming code. I use upper case for keywords and BiF (SELECT, FROM,
CONCAT(), ...), lower case for fields, careful indentation, new lines
where the subject changes. I am confident that if anyone looks at one
of my queries then, as with my code, the structure is obvious and easy
to follow.
I take pride in my trade and do not leave my clients with what is
essentially a rectangle of words, which some future programmer will
then have to carefully read through to find what is what.
YMMV
Jerry Stuckle
I agree with Paul here. Explicit JOINs are much better than implicit
ones for many reasons.
We used to use your method back in the 80's, because there was no JOIN
clause.
--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstu...@attglobal.net
==================
Brian Cryer
Agreed. I don't intentionally write unclear queries.
> Also, the precedence problem is still a problem no mater what version
> of MySQL you are on.
I don't see a problem in this case. Care to elaborate?
> I take as much care laying out my queries as I do laying out my
> programming code. I use upper case for keywords and BiF (SELECT, FROM,
> CONCAT(), ...), lower case for fields, careful indentation, new lines
> where the subject changes. I am confident that if anyone looks at one
> of my queries then, as with my code, the structure is obvious and easy
> to follow.
>
> I take pride in my trade and do not leave my clients with what is
> essentially a rectangle of words, which some future programmer will
> then have to carefully read through to find what is what.
Good.
> YMMV
Agreed.
--
Brian Cryer
www.cryer.co.uk/brian
Brian Cryer
news:hc6rml$8r3$2...@news.eternal-september.org...
Perhaps. I'm open to being shown why an explicit join is better, but I'm
still waiting to be shown anything tangible.
So, in this case, with a simple join between two tables, what tangible
benefits are there in doing an explicit join over an implicit one?
> We used to use your method back in the 80's, because there was no JOIN
> clause.
This doesn't make it in any way wrong. If the join where across more tables
then I might feel different - there comes a point where its easier to manage
the join expressions using an explicit join, but in a simple case like this
I'm still waiting to be convinced.
--
Brian Cryer
www.cryer.co.uk/brian
Jerry Stuckle
It separates the criteria for joining two tables from the criteria for
selecting rows from those two tables, making the code clearer and easier
to understand.
>> We used to use your method back in the 80's, because there was no JOIN
>> clause.
>
> This doesn't make it in any way wrong. If the join where across more
> tables then I might feel different - there comes a point where its
> easier to manage the join expressions using an explicit join, but in a
> simple case like this I'm still waiting to be convinced.
No, but it means it has been replaced by a much better way to do things.
For that matter, you can use COBOL I (circa 1959) for writing web pages,
so why not? Compilers still support most (of not all) of the original
language. It's not wrong to do it that way.
Erick T. Barkhuis
>Brian Cryer wrote:
>>So, in this case, with a simple join between two tables, what
>>tangible benefits are there in doing an explicit join over an
>>implicit one?
>>
>
>It separates the criteria for joining two tables from the criteria
>for selecting rows from those two tables, making the code clearer and
>easier to understand.
Although a simple join is generally simple to understand, even with
implicit joins, I agree with Jerry here.
I used to make use of implicit joins myself, until I noticed how much I
could benefit with the explicit form. It helped me structure the
queries better. Yes, even the simple ones.
>For that matter, you can use COBOL I (circa 1959) for writing web
>pages, so why not?
Because browsers don't interpret COBOL. However, if you meant to say
"as an application language, like Perl or PHP, then I agree.
> Compilers still support most (of not all) of the
> original language. It's not wrong to do it that way.
....and not bad, either. Remember, COBOL was intended to come close to
natural language. Its whole purpose was to make programming code easily
understandable. That's why BASIC statements like
b = a + 3
are written in COBOL like
ADD 3 TO a GIVING b.
So, while I understand what you're trying to say, I believe COBOL is
not the best of examples you could have given.
--
Erick
[COBOL/IMS application developer for 10+ years in the 80s/90s]
Jerry Stuckle
> Jerry Stuckle:
>
>> Brian Cryer wrote:
>
>>> So, in this case, with a simple join between two tables, what
>>> tangible benefits are there in doing an explicit join over an
>>> implicit one?
>>>
>> It separates the criteria for joining two tables from the criteria
>> for selecting rows from those two tables, making the code clearer and
>> easier to understand.
>
> Although a simple join is generally simple to understand, even with
> implicit joins, I agree with Jerry here.
> I used to make use of implicit joins myself, until I noticed how much I
> could benefit with the explicit form. It helped me structure the
> queries better. Yes, even the simple ones.
>
>
>> For that matter, you can use COBOL I (circa 1959) for writing web
>> pages, so why not?
>
> Because browsers don't interpret COBOL. However, if you meant to say
> "as an application language, like Perl or PHP, then I agree.
>
No, I meant COBOL I. Browsers don't interpret PHP, Perl or .NET,
either. Those are all handled on the server.
>> Compilers still support most (of not all) of the
>> original language. It's not wrong to do it that way.
>
> ....and not bad, either. Remember, COBOL was intended to come close to
> natural language. Its whole purpose was to make programming code easily
> understandable. That's why BASIC statements like
>
> b = a + 3
>
> are written in COBOL like
>
> ADD 3 TO a GIVING b.
>
> So, while I understand what you're trying to say, I believe COBOL is
> not the best of examples you could have given.
>
>
>
No, I think it's a rather good example.
William Gill
> ....and not bad, either. Remember, COBOL was intended to come close to
> natural language. Its whole purpose was to make programming code easily
> understandable. That's why BASIC statements like
>
> b = a + 3
>
> are written in COBOL like
>
> ADD 3 TO a GIVING b.
>
> So, while I understand what you're trying to say, I believe COBOL is
> not the best of examples you could have given.
My introduction to programming was kind of back door (I took a DPL
manual home to learn how to communicate with a programmer who worked for
me), but I learned to do serious damage in 4 or 5 assembly and 7 or 8
high level languages. Anyway, I always thought COBOL was what you would
expect from a government sponsored programming language; a bureaucratic,
bloated concoction.
--
Bill
Andrew C.
"Jerry Stuckle" <jstu...@attglobal.net> wrote in message
news:hc6rml$8r3$2...@news.eternal-september.org...
> for many reasons.
>
> We used to use your method back in the 80's, because there was no JOIN
> clause.
As a tiny squeak of support for implicit joins, and with my training hat on,
I have sometimes found them useful as a means of introducing the concept of
table joining to database newbies by starting off with a Cartesian Join and
actually showing where the tables correlate... then tossing a WHERE in to
filter out the rows that 'don't make sense'.
When training, I'm often inclined to give people a glimpse of what's going
on underneath before moving on to prettier (but perhaps slightly more
abstract) syntaxes.
A.
Captain Paralytic
> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
> > of MySQL you are on.
>
> I don't see a problem in this case. Care to elaborate?
>
In my second post in this thread I did elaborate. You say that you
don't see a problem in this case, but I have helped many people in
this forum who have started with a simple 2 table join and later
needed to add a LEFT JOIN and then had the precedence problem bite
them. I also mentioned in the same post about how much better a
explicit JOIN is from the PoV of understanding what parts of a query
are performing what task.
Note that the subject of this thread is "Using several tables in a
single SELECT statement". It says "several" not "two", implying that
more may be added.
However I note from the presentation of your sample query that clear
programming is not something that you are particularly concerned
about. Hence my comment that I would not wish to employ you.
Jerry Stuckle
Actually, when training, I don't even talk about implicit joins, except
as an afterthought. When we get into joining multiple tables, we go
straight to the explicit JOIN syntax. It is much clearer to the students.
Later on we make a comment they may see the implicit joins, but only as
a sidebar.
Peter H. Coffin
Remember the context: Once compiled, it's not particularly different
from any other language and it's WAY easier to read than assembly or
FORTRAN. I'd much rather have to maintain 30-year-old COBOL than
30-year-old anything else.
--
When you have a thermic lance, everything looks like hours of fun.
-- Christian Wagner in a.s.r
William Gill
> Remember the context: Once compiled, it's not particularly different
Never analyzed compiled efficiency, but no argument.
> ...and it's WAY easier to read than assembly or
> FORTRAN. I'd much rather have to maintain 30-year-old COBOL than
> 30-year-old anything else.
bothered with it outside of the academic.
--
Bill
Brian Cryer
> Brian Cryer wrote:
<snip>
>> So, in this case, with a simple join between two tables, what tangible
>> benefits are there in doing an explicit join over an implicit one?
>>
>
> It separates the criteria for joining two tables from the criteria for
> selecting rows from those two tables, making the code clearer and easier
> to understand.
Thank you. That makes sense. I still prefer an implicit join for simple
cases, but I can see the merit in migrating away from them.
--
Brian Cryer
www.cryer.co.uk/brian
Brian Cryer
> On 27 Oct, 15:44, "Brian Cryer" <not.here@localhost> wrote:
>> "Captain Paralytic" <paul_laut...@yahoo.com> wrote in message
>> > Also, the precedence problem is still a problem no mater what version
>> > of MySQL you are on.
>>
>> I don't see a problem in this case. Care to elaborate?
>
> In my second post in this thread I did elaborate. You say that you
> don't see a problem in this case, but I have helped many people in
> this forum who have started with a simple 2 table join and later
> needed to add a LEFT JOIN and then had the precedence problem bite
> them. I also mentioned in the same post about how much better a
> explicit JOIN is from the PoV of understanding what parts of a query
> are performing what task.
Your elaboration was the statement "will cause terrible problems because of
the difference precidences". That's a statement which I asked you to
elaborate on. A simple repitition of that assertion is not an elaboration.
So, please, in this case where is the precidence problem? Would we need to
start making assumptions about table sizes and indexes in order to introduce
a precidence problem? I'm looking to understand, are you able to explain?
> However I note from the presentation of your sample query that clear
> programming is not something that you are particularly concerned
> about. Hence my comment that I would not wish to employ you.
If you can't explain something when asked to then be assured that I wouldn't
wish to employ you either. I look for people who can explain what they are
doing and why, and can answer simple questions when challenged. If someone
can challenge my ideas in an interview then that gives them extra brownie
points, but simple assertions without explanation isn't helpful to anyone.
Jerry has provided a simple answer as to why an explicit join may be better
than an implicit one. If it were an interview he would have come out well.
You might like to learn from his example.
Fortunatly neither of us is looking for employment from the other.
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
Can you read? I gave the explanation of where the precedence problem
can bite. I will repeat it here again please try to read it!
You can read more about it in the manual, or do you want me to read
that for you too?
Brian Cryer
>
> Can you read? I gave the explanation of where the precedence problem
> can bite. I will repeat it here again please try to read it!
> You can read more about it in the manual, or do you want me to read
> that for you too?
>
> "I have helped many people in this forum who have started with a
> simple 2 table join and later needed to add a LEFT JOIN and then had
> the precedence problem bite them. "
This doesn't explain anything. You are simply repeating yourself. You have
yet to explain what a precedence problem is, nor to show when/how one might
be introduced. These are simple questions. I'm starting to wonder whether
this is a case of the emporer's new clothes.
Let me put this to you in a different way, in what situation would there be
a precidence problem? What would we need to do to the data or the design of
the tables or the design of the query to introduce a precidence problem?
Thinking about it if you have to fiddle with the design of the query then
you are introducing a problem which wasn't previously there, so I assume
that's not it - or is that it? You said "later needed to add a left join and
then had the precedence problem bite", so that does imply that you only get
the problem by changing the query, which in turn would imply that you are
talking about a problem which didn't exist in the original? Is that right?
That at least would explain why you have yet to show where a precidence
problem exists - because one doesn't exist, is that right?
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
So like I said, you want me to read the manual for you!
So since you appear incapable of this task, I will copy the relevant
portion:
"INNER JOIN and , (comma) are semantically equivalent in the absence
of a join condition: both produce a Cartesian product between the
specified tables (that is, each and every row in the first table is
joined to each and every row in the second table).
However, the precedence of the comma operator is less than of INNER
JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with
the other join types when there is a join condition, an error of the
form Unknown column 'col_name' in 'on clause' may occur. Information
about dealing with this problem is given later in this section."
Brian Cryer
That there can be a problem when you mix implicit and explicit joins is a
reasonable observation, but since there was no mixing of joins then a simple
"Yes, there was no precidence problem in the original" would have sufficed.
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
I did explain this fully in one of my other posts. Seems that you have
as much problem reading as you do writing.
Brian Cryer
Considering that I've been asking all along where the problem was, why then
didn't you simply say that there wasn't a problem? I'm glad that we have at
least reached agreement that you were talking about a problem that you had
introduced which did not exist. I don't recall a full explanation, but
perhaps that was in the different thread.
OT: You might like to consider reviewing your writing style, your posting
gives the impression that you might have an anger issue or have trolling
tendancies. It would be tempting, but childish to respond in the same
manner.
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
about 7 posts ago:
"You say that you don't see a problem in this case, but I have helped
many people in this forum who have started with a simple 2 table join
and later needed to add a LEFT JOIN and then had the precedence
problem bite them. I also mentioned in the same post about how much
better a explicit JOIN is from the PoV of understanding what parts of
a query are performing what task."
> OT: You might like to consider reviewing your writing style, your posting
> gives the impression that you might have an anger issue or have trolling
> tendancies. It would be tempting, but childish to respond in the same
> manner.
I have to admit that I do get a bit short when dealing with idiots.
Jerry Stuckle
Brian, you need to learn to stop arguing with those more knowledgeable
than you. It only makes you look like an idiot. Paul did post the
cause of the potential problem; you just either didn't read it or
couldn't understand it. I don't know which; neither do I care.
And your criticisms of him are completely unfounded. If there is anyone
here with trolling tendencies, it is you.
Brian Cryer
We are going round in circles here. Jump back 7 posts and re-read my reply.
Note, the first thing you quoted "You say that you don't see a problem in
this case". That's the issue that I was asking about. That's the only issue
I've been asking about - where the problem was in this case. You seem to be
focusing on where a problem might arise if someone made changes to the
query. In this case there is no problem. I am concerned with the here and
now, you are concerned with a hypothetical case. I'm not doubting that your
hypothetical case can arise, but it didn't exist here.
Summary: You introduced the problem. I asked where it was. We both agree it
isn't there.
>> OT: You might like to consider reviewing your writing style, your posting
>> gives the impression that you might have an anger issue or have trolling
>> tendancies. It would be tempting, but childish to respond in the same
>> manner.
> I have to admit that I do get a bit short when dealing with idiots.
I'm trying to give you the benefit of the doubt. It can be difficult given
that you seem to go out of your way to be insulting.
--
Brian Cryer
www.cryer.co.uk/brian
Brian Cryer
What I have been asking is where the problem was. It is only because I have
asked this that it has come to light that the problem is hypothetical - or
to use you term "potential". It is a problem which did not exist in the
original. I have just been trying to understand where the problem could come
from (which I do understand.)
Whether or not he is more knowledgeable with SQL than me is not the issue.
> And your criticisms of him are completely unfounded. If there is anyone
> here with trolling tendencies, it is you.
Other than pointing out his posting style, I don't think I have been
critical of him. I have only been trying to get to the bottom of what he was
on about, which is quite reasonable.
--
Brian Cryer
www.cryer.co.uk/brian
Jerry Stuckle
At the risk of repeating myself - Paul told you exactly the cause of the
potential problem. And it is POTENTIAL, not HYPOTHETICAL. It has
happened, and does happen regularly.
Rather than being critical of him, you should look at your ability to
read and understand.
I think you should drop this; you have your answer, and you're attempt
to defend yourself only makes you look more like an idiot.
Brian Cryer
> Brian Cryer wrote:
>>
>> "Jerry Stuckle" <jstu...@attglobal.net> wrote in message
>> news:hcmgnj$718$1...@news.eternal-september.org...
>>> Brian, you need to learn to stop arguing with those more knowledgeable
>>> than you. It only makes you look like an idiot. Paul did post the
>>> cause of the potential problem; you just either didn't read it or
>>> couldn't understand it. I don't know which; neither do I care.
>>
>> What I have been asking is where the problem was. It is only because I
>> have asked this that it has come to light that the problem is
>> hypothetical - or to use you term "potential". It is a problem which did
>> not exist in the original. I have just been trying to understand where
>> the problem could come from (which I do understand.)
>>
>> Whether or not he is more knowledgeable with SQL than me is not the
>> issue.
>>
>>> And your criticisms of him are completely unfounded. If there is anyone
>>> here with trolling tendencies, it is you.
>>
>> Other than pointing out his posting style, I don't think I have been
>> critical of him. I have only been trying to get to the bottom of what he
>> was on about, which is quite reasonable.
>
> At the risk of repeating myself - Paul told you exactly the cause of the
> potential problem. And it is POTENTIAL, not HYPOTHETICAL. It has
> happened, and does happen regularly.
I think you have misunderstood what I have been asking. I was asking where
it happened IN THIS CASE. It turns out it didn't exist IN THIS CASE. On my
side that is what the discussion has been about, not whether it can happen.
> Rather than being critical of him, you should look at your ability to read
> and understand.
Like I said, I have not intentionally been critical of him, I have only been
trying to get to the bottom of what he was on about. (Which I have.)
--
Brian Cryer
www.cryer.co.uk/brian
Captain Paralytic
> "Jerry Stuckle" <jstuck...@attglobal.net> wrote in message
>
> news:hcmj4a$r4u$1...@news.eternal-september.org...
>
>
>
>
>
> > Brian Cryer wrote:
>
Sheesh you don't give up do you! I haven't supplied any more
information, you have just realised what I've been saying all along.
Guess it just takes yo a bit of time.