Member Avatar for pink_zippy_123

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!

  • 2 Contributors
  • 3 Replies
  • 83 Views
  • 6 Days Discussion Span
  • Latest Post Latest Post by cfAllie
Member Avatar for cfAllie 
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
Member Avatar for pink_zippy_123

Thats great it now works...thanks a lot!! :)

Member Avatar for cfAllie

Welcome !

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.