SitePoint Sponsor

User Tag List

Results 1 to 5 of 5

Thread: JOIN 3 tables

  1. #1
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Unhappy JOIN 3 tables

    Hi guys I need to join my 3 tables

    I need to display the data from classlist, account, and student table from a specific class identified by the claID.

    For Example:
    If I want to display all students coming from class 77, their data from the three table will be joined even if the classlist doesnt match any record on the account and student table.

    I need your help ASAP. Thanks in advanced
    classList
    _______________________
    cllID claID accID
    77ses-jap 77 [->] ses-jap [->]
    77ses-1-2 77 [->] ses-1-2 [->]
    79ses-jap 79 [->] ses-jap [->]


    account
    ____________________________
    accID accFName accLName accMidName accEAdd
    ses-admin Jdfafypfe Tdf Gfads
    ses-stu Jap Jap Jap djflaj


    student
    _______________________________
    stuID accID stuCourse stuYearLevel stuSection
    12345 ses-12345 [->] BSIT 1 dfadsfd
    stu ses-stu [->] BSIT 1 a
    1 ses-1 [->] BSIT 1 A

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by comwwwdot View Post
    cllID claID accID
    77ses-jap 77 [->] ses-jap [->]
    77ses-1-2 77 [->] ses-1-2 [->]
    79ses-jap 79 [->] ses-jap [->]
    wha???

    could you do a SHOW CREATE TABLE for these three tables please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    wha???

    could you do a SHOW CREATE TABLE for these three tables please
    oops my bad, my apologies

    Table
    classlist

    CREATE TABLE `classlist` (
    `cllID` varchar(20) NOT NULL,
    `claID` varchar(15) NOT NULL,
    `accID` varchar(20) NOT NULL,
    PRIMARY KEY (`cllID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1


    Table
    account

    CREATE TABLE `account` (
    `accID` varchar(15) NOT NULL,
    `accFName` varchar(30) NOT NULL,
    `accLName` varchar(30) NOT NULL,
    `accMidName` varchar(30) NOT NULL,
    `accEAdd` varchar(30) NOT NULL DEFAULT 'notspecified@ses.com',
    `accPic` varchar(50) NOT NULL,
    `accType` varchar(10) NOT NULL,
    `accPassword` varchar(32) NOT NULL,
    `accStatus` varchar(8) NOT NULL,
    PRIMARY KEY (`accID`),
    KEY `accID` (`accID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1


    Table
    student

    CREATE TABLE `student` (
    `stuID` varchar(15) NOT NULL,
    `accID` varchar(15) NOT NULL,
    `stuCourse` varchar(60) NOT NULL,
    `stuYearLevel` varchar(15) NOT NULL,
    `stuSection` varchar(10) NOT NULL,
    PRIMARY KEY (`stuID`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    SELECT s.stuID
         , s.stuCourse
         , s.stuYearLevel
         , s.stuSection
      FROM classlist AS c
    INNER
      JOIN student AS s
        ON s.accID = c.accID
     WHERE c.claID = '77'
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Member
    Join Date
    Sep 2008
    Posts
    15
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    SELECT s.stuID
         , s.stuCourse
         , s.stuYearLevel
         , s.stuSection
      FROM classlist AS c
    INNER
      JOIN student AS s
        ON s.accID = c.accID
     WHERE c.claID = '77'

    Thanks thanks


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •