locked
return records from related tables when theres not related record in some tables RRS feed

  • Question

  • User-1693623980 posted

    Hi,

    I fetch data from this three table like this

    http://upload7.ir/imgs/2014-04/51545623395537387418.jpg

    select tbl_Student.*,tbl_StudentCountry.*, tbl_StudentFavouriteColor.* from tbl_Student,tbl_StudentCountry,tbl_StudentFavouriteColor     where tbl_Student.StudentID=tbl_StudentCountry.StudentID and tbl_StudentCountry.StudentID=tbl_StudentFavouriteColor.StudentID

    above query is equal to this

    SELECT        tbl_Student.StudentID, tbl_Student.FirstName, tbl_Student.Age, tbl_StudentCountry.ID, tbl_StudentCountry.StudentID AS Expr1, tbl_StudentCountry.Country,
                             tbl_StudentCountry.City, tbl_StudentFavouriteColor.ID AS Expr2, tbl_StudentFavouriteColor.StudentID AS Expr3, tbl_StudentFavouriteColor.FavouriteColor
    FROM            ((tbl_Student INNER JOIN
                             tbl_StudentCountry ON tbl_Student.StudentID = tbl_StudentCountry.StudentID) INNER JOIN
                             tbl_StudentFavouriteColor ON tbl_StudentCountry.StudentID = tbl_StudentFavouriteColor.StudentID)

    but with this data

    the query  returns only one record becase only id=3 is exist in all three table

    but I need this result
    http://upload7.ir/imgs/2014-04/85192297904709039865.jpg
    is it possible?

    Monday, April 14, 2014 1:45 PM

Answers

  • User-1199946673 posted

    SELECT        tbl_Student.StudentID, tbl_Student.FirstName, tbl_Student.Age, tbl_StudentCountry.ID, tbl_StudentCountry.StudentID AS Expr1, tbl_StudentCountry.Country,
                             tbl_StudentCountry.City, tbl_StudentFavouriteColor.ID AS Expr2, tbl_StudentFavouriteColor.StudentID AS Expr3, tbl_StudentFavouriteColor.FavouriteColor
    FROM            ((tbl_Student INNER JOIN
                             tbl_StudentCountry ON tbl_Student.StudentID = tbl_StudentCountry.StudentID) LEFT JOIN
                             tbl_StudentFavouriteColor ON tbl_StudentCountry.StudentID = tbl_StudentFavouriteColor.StudentID)

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 14, 2014 6:42 PM