SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Wizard
    Join Date
    Jul 2006
    Location
    New Zealand
    Posts
    1,300
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    selecting from multiple tables

    Hi all im working on a project and i have a question regarding select from multiple tables this is my select string

    SELECT patient.plname,patient.psex,doctor.Docid,operation.Result FROM patient,doctor,operation WHERE Docid='1'

    now

    below is my table structure.


    CREATE TABLE `doctor` (
    `Docid` int(255) unsigned NOT NULL auto_increment,
    `DocLname` varchar(255) collate latin1_general_ci NOT NULL,
    `DocFname` varchar(255) collate latin1_general_ci NOT NULL,
    `DocPAddy` varchar(255) collate latin1_general_ci NOT NULL,
    `CellPhone` varchar(255) collate latin1_general_ci NOT NULL,
    `Function` varchar(255) collate latin1_general_ci NOT NULL,
    PRIMARY KEY (`Docid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Table For Holding Doctor Informatin' AUTO_INCREMENT=1 ;

    --
    -- Dumping data for table `doctor`
    --


    -- --------------------------------------------------------

    --
    -- Table structure for table `operation`
    --

    CREATE TABLE `operation` (
    `opid` int(255) NOT NULL auto_increment,
    `pid` varchar(255) collate latin1_general_ci NOT NULL,
    `Docid` varchar(255) collate latin1_general_ci NOT NULL,
    `Date` varchar(255) collate latin1_general_ci NOT NULL,
    `Time` varchar(255) collate latin1_general_ci NOT NULL,
    `Result` varchar(255) collate latin1_general_ci NOT NULL,
    `Optype` varchar(255) collate latin1_general_ci NOT NULL,
    PRIMARY KEY (`opid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Table for Holding Operation Information' AUTO_INCREMENT=1 ;

    --
    -- Dumping data for table `operation`
    --


    -- --------------------------------------------------------

    --
    -- Table structure for table `patient`
    --

    CREATE TABLE `patient` (
    `pid` int(255) unsigned NOT NULL auto_increment,
    `plname` varchar(255) collate latin1_general_ci NOT NULL,
    `pfname` varchar(255) collate latin1_general_ci NOT NULL,
    `psex` varchar(255) collate latin1_general_ci NOT NULL,
    `pjob` varchar(255) collate latin1_general_ci NOT NULL,
    `pheight` varchar(255) collate latin1_general_ci NOT NULL,
    `pweight` varchar(255) collate latin1_general_ci NOT NULL,
    `paddy` varchar(255) collate latin1_general_ci NOT NULL,
    PRIMARY KEY (`pid`)
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci COMMENT='Table for Holding Patient Information' AUTO_INCREMENT=1 ;

    ALTER TABLE `patient` ADD `age` VARCHAR( 255 ) NOT NULL ;

    --
    -- Dumping data for table `patient`

    When i do that i get this error back from the select string above.

    Error

    SQL query: Documentation

    SELECT patient.plname, patient.psex, doctor.Docid, operation.Result
    FROM patient, doctor, operation
    WHERE Docid = '1'
    LIMIT 0 , 30

    MySQL said: Documentation
    #1052 - Column 'Docid' in where clause is ambiguous


    What am i doing wrong?

  2. #2
    SitePoint Wizard gold trophysilver trophybronze trophy dc dalton's Avatar
    Join Date
    Nov 2004
    Location
    Right behind you, watching, always watching.
    Posts
    5,431
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by william232 View Post
    Hi all im working on a project and i have a question regarding select from multiple tables this is my select string

    SELECT patient.plname,patient.psex,doctor.Docid,operation.Result FROM patient,doctor,operation WHERE Docid='1'
    Yup, you need to specify WHICH table to use the Docid in your where clause since you have it in both your doctor and operation tables ...

    either doctor.Docid OR operation.DocId ... that's the problem

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you have a serious problem right here --

    .. FROM patient,doctor,operation WHERE Docid='1'

    this will join document 1 to every doctor, and then join every doctor to every operation

    this is called a cartesian product, and is almost always a serious error

    is this problem part of a homework assignment?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Wizard
    Join Date
    Jul 2006
    Location
    New Zealand
    Posts
    1,300
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes this is for a homework assignment.

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you need to go back to your course material or book and do some research on how to write a join

    your query is missing all the join conditions
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


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
  •