•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MySQL section within the Web Development category of DaniWeb, a massive community of 428,577 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,665 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MySQL advertiser: Programming Forums
Views: 877 | Replies: 3
 |
•
•
Join Date: Apr 2007
Posts: 6
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Sql Joins Hi,
I am currently trying to write some sql that includes a where clause to limit responses but will leave blanks where there is no match. I will include the code so its easier to understand:
SELECT DISTINCT teacher.teachername, student.studentname, results.grade FROM teacher INNER JOIN student ON teacher.teachername = student.teachername INNER JOIN results ON student.studentname = results.studentname WHERE results.grade = 'A';
I basically want all teachers names to be displayed, but only students that have got A's to be displayed next to them, and any that do not have any A Grade students to have a blank box next to them! I have tried using left joins, right joins, and outer joins and nothing has worked...all I end up with is only teachers names listed that have A Grade students. Any ideas? Any help will be greatly appreciated!
I am currently trying to write some sql that includes a where clause to limit responses but will leave blanks where there is no match. I will include the code so its easier to understand:
SELECT DISTINCT teacher.teachername, student.studentname, results.grade FROM teacher INNER JOIN student ON teacher.teachername = student.teachername INNER JOIN results ON student.studentname = results.studentname WHERE results.grade = 'A';
I basically want all teachers names to be displayed, but only students that have got A's to be displayed next to them, and any that do not have any A Grade students to have a blank box next to them! I have tried using left joins, right joins, and outer joins and nothing has worked...all I end up with is only teachers names listed that have A Grade students. Any ideas? Any help will be greatly appreciated!
•
•
Join Date: Mar 2007
Posts: 28
Reputation:
Rep Power: 0
Solved Threads: 0
Light Poster
SELECT DISTINCT teacher.teachername, student.studentname, results.grade
FROM teacher LEFT JOIN
(
student INNER JOIN results
ON student.studentname = results.studentname
AND results.grade = 'A'
)
ON teacher.teachername = student.teachername 
|
•
•
•
•
•
•
•
•
DaniWeb MySQL Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- MS SQL Joins - newb assistance (MS SQL)
- Wysiwyg Sql (MS Access and FileMaker Pro)
- Query conversion from Sybase to MS SQL Server 2000 (MS SQL)
- Problem with Rewriting Subqueries as Joins (MS SQL)
Other Threads in the MySQL Forum
- Previous Thread: I need help with this kind of report mySQL
- Next Thread: Static Count vs Dynamic Count (Very advanced, gurus take your shot)
