selecting from multiple tables

[william232]

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?

[dc dalton]

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

[r937]

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?

[william232]

Yes this is for a homework assignment.

[r937]

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