Startseite » Foren » Newsgroup Archiv » comp.databases.mysql

Using several tables in a single SELECT statement

13 replies [Letzter Beitrag]
22. October 2009 - 12:41
Brown Cat
Offline
Joined: 12.10.2009
Beiträge:

I have the following tables:

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?

Noch keine Bewertungen
Oben
  • 259 Aufrufe
22. October 2009 - 13:03
#1
Erick T. Barkhuis
Offline
Joined: 12.10.2009
Beiträge:

Brown Cat:
> 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

Oben
22. October 2009 - 13:37
#2
Brown Cat
Offline
Joined: 12.10.2009
Beiträge:

On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:

> Brown Cat:
>> 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

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.

Oben
23. October 2009 - 10:27
#3
Brian Cryer
Offline
Joined: 12.10.2009
Beiträge:

"Brown Cat" wrote in message
news:CmYDm.94760$5E.27400@newsfe29.ams2...
> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>
>> Brown Cat:
>>> 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
>
> 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.

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

Oben
23. October 2009 - 12:22
#4
Captain Paralytic
Offline
Joined: 12.10.2009
Beiträge:

On 23 Oct, 10:27, "Brian Cryer" wrote:
> "Brown Cat" wrote in message
>
> news:CmYDm.94760$5E.27400@newsfe29.ams2...
>
>
>
>
>
> > On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>
> >> Brown Cat:
> >>> I have the following tables:
>
> >>> =A0STUDENT
> >>> =A0CLASS
> >>> =A0STUDENT_CLASS
>
> >>> What I want to do is select a CLASS and see which students are studyi=
ng
> >>> that class. =A0[...] =A0But 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
>
> > 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.
>
> 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 b=
e
> something like:
>
> select Student.fullName
> from Student, Class, Student_Class
> where Student.studentID =3D Student_Class.studentID
> and Student_Class.classID =3D Student_Class.classID
> and Student_Class.className =3D 'Maths'
>
> You might find my SQL crib sheet useful:http://www.cryer.co.uk/brian/sql/=
sql_crib_sheet.htm
> --
> Brian Cryerwww.cryer.co.uk/brian

If you are going to introduce someone to SQL, you should at least use
the better explicit JOIN syntax!

Oben
26. October 2009 - 12:03
#5
Brian Cryer
Offline
Joined: 12.10.2009
Beiträge:

"Captain Paralytic"
wrote in message
news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
> On 23 Oct, 10:27, "Brian Cryer" wrote:
>> "Brown Cat" wrote in message
>>
>> news:CmYDm.94760$5E.27400@newsfe29.ams2...
>>
>>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>>
>>>> Brown Cat:
>>>>> 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
>>
>>> 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.
>>
>> 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
>
> If you are going to introduce someone to SQL, you should at least use
> the better explicit JOIN syntax!

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

Oben
26. October 2009 - 23:28
#6
Captain Paralytic
Offline
Joined: 12.10.2009
Beiträge:

On 26 Oct, 11:03, "Brian Cryer" wrote:
> "Captain Paralytic"
wrote in message
>
> news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
>
>
>
>
>
> > On 23 Oct, 10:27, "Brian Cryer" wrote:
> >> "Brown Cat" wrote in message
>
> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>
> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>
> >>>> Brown Cat:
> >>>>> 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
>
> >>> 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.
>
> >> 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 Cryerwww.cryer.co.uk/brian
>
> > If you are going to introduce someone to SQL, you should at least use
> > the better explicit JOIN syntax!
>
> 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.
BEST???
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.

Oben
27. October 2009 - 9:21
#7
Brian Cryer
Offline
Joined: 12.10.2009
Beiträge:

"Captain Paralytic"
wrote in message
news:dd064c2b-8b3b-4945-b60a-a638353ad564@r5g2000yqb.googlegroups.com...
> On 26 Oct, 11:03, "Brian Cryer" wrote:
>> "Captain Paralytic"
wrote in message
>>
>> news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
>>
>> > On 23 Oct, 10:27, "Brian Cryer" wrote:
>> >> "Brown Cat" wrote in message
>>
>> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>>
>> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>>
>> >>>> Brown Cat:
>> >>>>> 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
>>
>> >>> 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.
>>
>> >> 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
>>
>> > If you are going to introduce someone to SQL, you should at least use
>> > the better explicit JOIN syntax!
>>
>> 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.
> BEST???
> 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?

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

Oben
27. October 2009 - 11:56
#8
Captain Paralytic
Offline
Joined: 12.10.2009
Beiträge:

On 27 Oct, 08:21, "Brian Cryer" wrote:
> "Captain Paralytic"
wrote in message
>
> news:dd064c2b-8b3b-4945-b60a-a638353ad564@r5g2000yqb.googlegroups.com...
>
>
>
>
>
> > On 26 Oct, 11:03, "Brian Cryer" wrote:
> >> "Captain Paralytic"
wrote in message
>
> >>news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com.=
..
>
> >> > On 23 Oct, 10:27, "Brian Cryer" wrote:
> >> >> "Brown Cat" wrote in message
>
> >> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>
> >> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>
> >> >>>> Brown Cat:
> >> >>>>> 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 re=
ad
> >> >>>> up
> >> >>>> on one of the most important (and basic) SQL features: JOIN
>
> >> >>> It's not homework, it's a personal project but I have used the ver=
y
> >> >>> commonly used Student/Class/Lecturer example to make the explanati=
on
> >> >>> a
> >> >>> bit more simple.
>
> >> >>> I think JOIN is what I need, thanks.
>
> >> >> 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 =3D Student_Class.studentID
> >> >> and Student_Class.classID =3D Student_Class.classID
> >> >> and Student_Class.className =3D 'Maths'
>
> >> >> You might find my SQL crib sheet useful:
> >> >>http://www.cryer.co.uk/brian/sql/sql_crib_sheet.htm
> >> >> --
> >> >> Brian Cryerwww.cryer.co.uk/brian
>
> >> > If you are going to introduce someone to SQL, you should at least us=
e
> >> > the better explicit JOIN syntax!
>
> >> Whilst there are more obscure ways of doing the join, I find it genera=
lly
> >> best to use the most straight forward and easiest to remember syntax.
> >> YMMV.
> > BEST???
> > 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?
>
> 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 databa=
se
> which doesn't have an optimiser or an old version of MySQL?
> =A0--
> Brian Cryerwww.cryer.co.uk/brian

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

Oben
27. October 2009 - 14:13
#9
Jerry Stuckle
Offline
Joined: 12.10.2009
Beiträge:

Brian Cryer wrote:
> "Captain Paralytic"
wrote in message
> news:dd064c2b-8b3b-4945-b60a-a638353ad564@r5g2000yqb.googlegroups.com...
>> On 26 Oct, 11:03, "Brian Cryer" wrote:
>>> "Captain Paralytic"
wrote in message
>>>
>>> news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
>>>
>>>
>>> > On 23 Oct, 10:27, "Brian Cryer" wrote:
>>> >> "Brown Cat" wrote in message
>>>
>>> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>>>
>>> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>>>
>>> >>>> Brown Cat:
>>> >>>>> 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
>>>
>>> >>> 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.
>>>
>>> >> 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
>>>
>>> > If you are going to introduce someone to SQL, you should at least use
>>> > the better explicit JOIN syntax!
>>>
>>> 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.
>> BEST???
>> 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?
>
> 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?

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.
jstucklex@attglobal.net
==================

Oben
27. October 2009 - 16:44
#10
Brian Cryer
Offline
Joined: 12.10.2009
Beiträge:

"Captain Paralytic"
wrote in message
news:8d27ac18-e7e5-490f-a656-2ab2c3610bda@g27g2000yqn.googlegroups.com...
> On 27 Oct, 08:21, "Brian Cryer" wrote:
>> "Captain Paralytic"
wrote in message
>>
>> news:dd064c2b-8b3b-4945-b60a-a638353ad564@r5g2000yqb.googlegroups.com...
>>
>> > On 26 Oct, 11:03, "Brian Cryer" wrote:
>> >> "Captain Paralytic"
wrote in message
>>
>> >>news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
>>
>> >> > On 23 Oct, 10:27, "Brian Cryer" wrote:
>> >> >> "Brown Cat" wrote in message
>>
>> >> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>>
>> >> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>>
>> >> >>>> Brown Cat:
>> >> >>>>> 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
>>
>> >> >>> 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.
>>
>> >> >> 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
>>
>> >> > If you are going to introduce someone to SQL, you should at least
>> >> > use
>> >> > the better explicit JOIN syntax!
>>
>> >> 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.
>> > BEST???
>> > 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?
>>
>> 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
>
> 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.

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

Oben
27. October 2009 - 17:00
#11
Brian Cryer
Offline
Joined: 12.10.2009
Beiträge:

"Jerry Stuckle" wrote in message
news:hc6rml$8r3$2@news.eternal-september.org...
> Brian Cryer wrote:
>> "Captain Paralytic"
wrote in message
>> news:dd064c2b-8b3b-4945-b60a-a638353ad564@r5g2000yqb.googlegroups.com...
>>> On 26 Oct, 11:03, "Brian Cryer" wrote:
>>>> "Captain Paralytic"
wrote in message
>>>>
>>>> news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
>>>>
>>>> > On 23 Oct, 10:27, "Brian Cryer" wrote:
>>>> >> "Brown Cat" wrote in message
>>>>
>>>> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>>>>
>>>> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>>>>
>>>> >>>> Brown Cat:
>>>> >>>>> 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
>>>>
>>>> >>> 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.
>>>>
>>>> >> 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
>>>>
>>>> > If you are going to introduce someone to SQL, you should at least use
>>>> > the better explicit JOIN syntax!
>>>>
>>>> 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.
>>> BEST???
>>> 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?
>>
>> 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?
>
> I agree with Paul here. Explicit JOINs are much better than implicit ones
> for many reasons.

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

Oben
27. October 2009 - 18:32
#12
Jerry Stuckle
Offline
Joined: 12.10.2009
Beiträge:

Brian Cryer wrote:
> "Jerry Stuckle" wrote in message
> news:hc6rml$8r3$2@news.eternal-september.org...
>> Brian Cryer wrote:
>>> "Captain Paralytic"
wrote in message
>>> news:dd064c2b-8b3b-4945-b60a-a638353ad564@r5g2000yqb.googlegroups.com...
>>>> On 26 Oct, 11:03, "Brian Cryer" wrote:
>>>>> "Captain Paralytic"
wrote in message
>>>>>
>>>>> news:e2c78d23-994a-443d-bc13-c716e9ac25f8@u13g2000vbb.googlegroups.com...
>>>>>
>>>>>
>>>>> > On 23 Oct, 10:27, "Brian Cryer" wrote:
>>>>> >> "Brown Cat" wrote in message
>>>>>
>>>>> >>news:CmYDm.94760$5E.27400@newsfe29.ams2...
>>>>>
>>>>> >>> On Thu, 22 Oct 2009 14:03:49 ?, Erick T. Barkhuis wrote:
>>>>>
>>>>> >>>> Brown Cat:
>>>>> >>>>> 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
>>>>>
>>>>> >>> 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.
>>>>>
>>>>> >> 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
>>>>>
>>>>> > If you are going to introduce someone to SQL, you should at least
>>>>> use
>>>>> > the better explicit JOIN syntax!
>>>>>
>>>>> 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.
>>>> BEST???
>>>> 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?
>>>
>>> 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?
>>
>> I agree with Paul here. Explicit JOINs are much better than implicit
>> ones for many reasons.
>
> 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?
>

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.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Oben
27. October 2009 - 19:08
#13
Erick T. Barkhuis
Offline
Joined: 12.10.2009
Beiträge:

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.

> 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]

Oben