No records to show

[Ctechnology24]

can anybody tell me what is wrong with my sql statement because it runs well if their is only one or but when I put another or it gives nothing to display

"SELECT * FROM tbldoctor_info i, tblmonday m, tbltuesday t, tblwednesday w, tblthursday h, tblfriday f, tblsaturday a, tblsunday u WHERE (i.ID_No = m.RelationDoc_ID OR i.ID_No = t.RelationDoc_ID OR i.ID_No = w.RelationDoc_ID OR i.ID_No = h.RelationDoc_ID OR i.ID_No = f.RelationDoc_ID OR i.ID_No = a.RelationDoc_ID OR i.ID_No = u.RelationDoc_ID)";

[smantscheff]

Show some test data.

[Ctechnology24]

this are my sample records in tbldoctor_info

ID_No	LastName	FirstName	MName	Age	Gender	Address	Specialist	License_No
			34	oi	oi	oi	43	oi	oi	oi	33
			100	Athena	god	olympus	89	female	unknown	healer	5560
			443	e	e	e	4	d	efg	fg	4
			546	tu	rh	erhg	57	gh	h	h	44
			777	try	try	try	9	try	aldsk	asdf	33
			789	juju	asdf	asdf	32	asdf	df	df	23
			798	Octavio	Angelo	Dj	33	Male	POCC	Opthalmologist	9988
			890	try	trey	try	8	male	PCC	we	22
			8900	j	j	j	8	j	j	j	99
			9987	Palarpalr	MarkEdward	Lieve	18	Male	St Lucy	DOta	1001
			32443	gaspar	carlo	alba	19	male	Sta.Lucia	Pedia	192168
			66674	ye	ye	ye	77	f	df	df	77
			66675	Reyes	Martin	Dela Cruz	12	male	Bagong Barrio Caloocan City	Dentistry	4294967295

this is a sample record from tblmonday

RelationDoc_ID	Timein_am	Timeout_am	Timein_pm	Timeout_pm
			798	1am	2am	1pm	4pm
			32443	2am	4am	3pm	5pm

the other tables like tbltuesday etc, have the same table structure with tblmonday, it only varies on the name and the records it contains,

[smantscheff]

Prepare and show a complete test case, with CREATE TABLE statements and INSERT INTO statements. Then apply your query to this test case and, if it does not work, show it to us.

[Ctechnology24]

this is my sample SQL codes

CREATE TABLE `tbldoctor_info` (
  `ID_No` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `LastName` varchar(45) NOT NULL,
  `FirstName` varchar(45) NOT NULL,
  `MName` varchar(45) NOT NULL,
  `Age` varchar(45) NOT NULL,
  `Gender` varchar(45) NOT NULL,
  `Address` varchar(45) NOT NULL,
  `Specialist` varchar(45) NOT NULL,
  `License_No` int(10) unsigned NOT NULL,
  PRIMARY KEY (`ID_No`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=66676 ;

INSERT INTO `bernardinodatabase`.`tbldoctor_info` (`ID_No`, `LastName`, `FirstName`, `MName`, `Age`, `Gender`, `Address`, `Specialist`, `License_No`) VALUES (NULL, 'Rogue', 'Sin', 'Foul', '13', 'Male', 'New York', 'Opthalmologist', '3445643222');

CREATE TABLE `tblmonday` (
  `RelationDoc_ID` int(11) unsigned NOT NULL,
  `Timein_am` varchar(50) NOT NULL,
  `Timeout_am` varchar(50) NOT NULL,
  `Timein_pm` varchar(50) NOT NULL,
  `Timeout_pm` varchar(50) NOT NULL,
  PRIMARY KEY (`RelationDoc_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `bernardinodatabase`.`tblmonday`
(`RelationDoc_ID`, `Timein_am`, `Timeout_am`, `Timein_pm`,
`Timeout_pm`) VALUES ('546', '1am', '2am', '3pm', '4pm');

[Ctechnology24]

it gives this error when I try it in phpmyadmin

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"SELECT * FROM tbldoctor_info i, tblmonday m, tbltuesday t, tblwednesday w, tblt' at line 1

[smantscheff]

If a SELECT statement names multiple tables in the FROM clause with the names separated by commas, MySQL performs a full join.

At least on of your weekday tables contains no data, therefore the result join contains no rows.

And the database design is horrendous - having one table for each weekday instead of one table with the weekday as a column is contrary to very basic design principles.

[Ctechnology24]

okay I will change it right away

[Ctechnology24]

Sorry, I know that already but I changed it because I thought it will be more easier to manipulate those records but I'm absolutely wrong, thanks for the advise, this enlightened me much. :)