CODEWITHSUNDEEP
sqljoin
Ziai
Ziai

Reputation: 39

Making a SQL query (cross table)

I am revising for my exam and I am a bit struggling with SQL queries..

Task for me is:

write a SQL query to list the full names of all the clients that attend Allen Moore‟s classes, in alphabetical order of surname (i.e. Lname column)

Client

Cid Fname Lname Fitness
C129 Julie Summer 1
C525 Max Hedrum 3
C628 John Long 3
C772 Warren Peace 2
C829 Anna Heart 2

Programme

Code Title Fitness
AR02 Aerobics 2
EN99 Endurance 3
TU10 Tune-Up 1
UB01 Upper-Body 2
YG02 Yoga 1

Staff

Sid Fname Lname Position Salary
S09 Jenny Sayer Psychologist 23500
S22 Allen Moore Instructor 21500
S28 Polly Purves Instructor 19000
S35 Jim Shoe Instructor 18000
S55 Mark Spencer Manager 25500

Class

Code Sid Cid
AR02 S35 C772
EN99 S22 C525
TU10 S35 C129
UB01 S28 C628
YG02 S22 C829
YG02 S22 C12

Is it something like:

SELECT Cliente.Fname, Cliente.Lname
FROM Staff, Class, Cliente
WHERE  Staff.Sid = Class.Sid AND Staff.Fname = "Allen" AND Staff.Lname = "Moore 
AND Class.Cid = Cliente.Cid
GROUP BY Cliente.lName ASC;

Thanks!

Upvotes: 1

Views: 297

Answers (5)

Matt
Matt

Reputation: 4775

Since none of the information from other tables is actually being used, why JOIN at all?

 SELECT FName, LName
 FROM Client
 WHERE
    Cid IN (
        SELECT Cid
        FROM Class
        WHERE Sid = (
            SELECT Sid FROM Staff WHERE FName = 'Allen' AND LName = 'Moore'
        )
    )
 ORDER BY LName ASC;

Upvotes: 1

Mithrandir
Mithrandir

Reputation: 25337

The GROUP BY in your query has to go, you don't have to aggregate anything. I would prefer the SQL92 syntax for a join:

SELECT 
    Client.Fname, Client.Lname
FROM 
   Staff
INNER JOIN
Class ON Staff.Sid = Class.Sid AND Staff.Fname = "Allen" AND Staff.Lname = "Moore"
INNER JOIN 
Client ON Class.Cid = Cliente.Cid
ORDER BY Cliente.lName ASC;

Upvotes: 1

Web User
Web User

Reputation: 7736

Using different aliases and a more popular convention for JOINs, but your query should also work. Also, ORDER BY ascending is default, so ASC is optional...

SELECT CLNT.Fname, CLNT.Lname
FROM Class AS CLS
     INNER JOIN Client AS CLNT ON CLS.Cid = CLNT.Cid
     INNER JOIN Staff AS S ON CLS.Sid = S.Sid
WHERE S.Fname = 'Allen'
      AND S.Lname = 'Moore'
ORDER BY CLNT.Lname;

Upvotes: 2

Nick Vaccaro
Nick Vaccaro

Reputation: 5504

Try brushing up on your use of the JOIN...ON clause.

http://www.w3schools.com/sql/sql_join_inner.asp

Upvotes: 1

user596075
user596075

Reputation: 

select c.fname + ' ' + c.lname
from Client c
inner join Class cl
on c.cid = cl.cid
inner join Staff s
on s.sid = cl.sid
where s.fname = 'allen'
and s.lname = 'moore'
order by c.lname

Upvotes: 2

Related Questions