SitePoint Sponsor |
|
User Tag List
Results 1 to 5 of 5
Thread: selecting from multiple tables
-
Jan 1, 2009, 21:50 #1
- 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?
-
Jan 1, 2009, 22:04 #2
- Join Date
- Nov 2004
- Location
- Right behind you, watching, always watching.
- Posts
- 5,431
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
-
Jan 1, 2009, 22:47 #3
- 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?
-
Jan 7, 2009, 01:16 #4
- 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.
-
Jan 7, 2009, 01:25 #5
- 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
Bookmarks