搜索时Mysql查询问题

Question

我在 MySQL 中遇到了一些问题

我想从三个表中搜索记录。我的查询是

SELECT *
from tbl_dogwalker
LEFT JOIN tbl_dogwalker_details on tbl_dogwalker_details.dogwalkerId=tbl_dogwalker.id
LEFT JOIN calendar_events on tbl_dogwalker_details.dogwalkerId= calendar_events.dogwalkerId
where tbl_dogwalker_details.addressSuburb LIKE '%ress%'
AND calendar_events.event_start='2011-04-02'
AND calendar_events.statusAfternoon=2
AND tbl_dogwalker_details.houseSittingShortTerm='1'
OR tbl_dogwalker_details.houseSittingMediumTerm='1'
OR tbl_dogwalker_details.houseSittingLongTerm='1'

我想搜索其中任何一个(tbl_dogwalker_details.houseSittingShortTerm、tbl_dogwalker_details.houseSittingMediumTerm、tbl_dogwalker_details.houseSittingLongTerm)是否等于1。我的表结构是

CREATE TABLE `calendar_events` (
  `id` int(11) NOT NULL auto_increment,
  `statusMorning` varchar(2) NOT NULL,
  `statusAfternoon` varchar(2) NOT NULL,
  `event_start` date default NULL,
  `dogwalkerId` int(11) NOT NULL,
  `userIdMorning` int(11) NOT NULL,
  `userIdAfternoon` int(11) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=64 ;



INSERT INTO `calendar_events` VALUES(55, '2', '2', '2011-03-02', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(54, '', '2', '2011-03-01', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(56, '2', '2', '2011-03-03', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(57, '', '2', '2011-03-15', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(58, '', '2', '2011-03-10', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(59, '', '2', '2011-03-16', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(60, '2', '2', '2011-03-25', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(61, '2', '', '2011-03-04', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(62, '2', '', '2011-03-05', 2, 0, 0);
INSERT INTO `calendar_events` VALUES(63, '2', '', '2011-03-12', 2, 0, 0);



CREATE TABLE `tbl_dogwalker` (
  `id` int(11) NOT NULL auto_increment,
  `firstName` varchar(50) NOT NULL,
  `lastName` varchar(50) NOT NULL,
  `email` varchar(50) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `areaOfInterest` varchar(500) NOT NULL,
  `region` varchar(100) NOT NULL,
  `suburb` varchar(100) NOT NULL,
  `userName` varchar(50) NOT NULL,
  `Password` varchar(20) NOT NULL,
  `comment` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `tbl_dogwalker`
--

INSERT INTO `tbl_dogwalker` VALUES(2, 'Rajanikant', 'Shukla', 'rajanikant.shukla@gmail.com', '9310970358', 'Delhi', '', '', 'rajanikant.shukla@gmail.com', '12345678', '');
INSERT INTO `tbl_dogwalker` VALUES(3, 'Aman', 'abc', 'test@test.com', '9898989898', 'Delhi', '', '', 'test@test.com', '35DFLPTX', '');


CREATE TABLE `tbl_dogwalker_details` (
  `id` int(11) NOT NULL auto_increment,
  `dogwalkerId` int(11) NOT NULL,
  `addressHouseNo` varchar(20) NOT NULL,
  `addressStreet` varchar(50) NOT NULL,
  `addressSuburb` varchar(50) NOT NULL,
  `addressCity` varchar(50) NOT NULL,
  `homePhone` varchar(20) NOT NULL,
  `mobilePhone` varchar(20) NOT NULL,
  `aboutYourSelf` varchar(20) NOT NULL,
  `aboutDogwalker` varchar(100) NOT NULL,
  `smallDogCare` varchar(5) NOT NULL,
  `mediumDogCare` varchar(5) NOT NULL,
  `largDogCare` varchar(5) NOT NULL,
  `feeding` varchar(5) NOT NULL,
  `exe_feed` varchar(25) NOT NULL,
  `vetVisit` varchar(5) NOT NULL,
  `grooming` varchar(5) NOT NULL,
  `houseSittingShortTerm` varchar(5) NOT NULL,
  `houseSittingMediumTerm` varchar(5) NOT NULL,
  `houseSittingLongTerm` varchar(5) NOT NULL,
  `homeBoarding` varchar(2) NOT NULL,
  `dayCare` varchar(2) NOT NULL,
  `additionalPetsCare` varchar(50) NOT NULL,
  `policeCheckStatus` varchar(4) NOT NULL,
  `testimonial1` varchar(50) NOT NULL,
  `testimonial2` varchar(50) NOT NULL,
  `image1` varchar(50) NOT NULL,
  `image2` varchar(50) NOT NULL,
  `profileImage` varchar(50) NOT NULL,
  `combo` varchar(2) NOT NULL,
  PRIMARY KEY  (`id`,`dogwalkerId`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `tbl_dogwalker_details`
--

INSERT INTO `tbl_dogwalker_details` VALUES(1, 2, 'addressHouseNo', 'addressStreet', 'addressSuburb', 'addressCity', '9310970358', '9310970358', 'I m a good dog walke', 'I can walk with so many dogs at a tim.', 'S', 'M', 'L', 'F', 'EXEFEED', 'V', 'G', '1', '1', '1', '1', '1', 'Mice', 'P', 'all people hire me for their god says that i m a b', 'asdfsdf', 'Thumb_11full.jpg', 'Thumb_13full.jpg', 'Thumb_15full.jpg', '');
INSERT INTO `tbl_dogwalker_details` VALUES(2, 3, '1111', '11cvbvbvbvbv', 'bvbv', 'bvbvbvbv', '9898989898', '987152107245', 'nbnbnb', 'nbbnnb', 'S', '', '', 'F', '', '', '', '1', '', '', '1', '1', 'Kitten/s', 'Y', 'nbnb', 'nbnbnb', '', '', '', '');

这次给出了 9 个相同的结果，但应该是 1

我们将提供任何帮助

谢谢

Answer 1

看起来你只需要正确放置括号即可

SELECT *
from tbl_dogwalker
LEFT JOIN tbl_dogwalker_details on tbl_dogwalker_details.dogwalkerId=tbl_dogwalker.id
LEFT JOIN calendar_events on tbl_dogwalker_details.dogwalkerId= calendar_events.dogwalkerId
where tbl_dogwalker_details.addressSuburb LIKE '%ress%'
AND calendar_events.event_start='2011-04-02'
AND calendar_events.statusAfternoon=2
AND 
(
   tbl_dogwalker_details.houseSittingShortTerm='1'
OR tbl_dogwalker_details.houseSittingMediumTerm='1'
OR tbl_dogwalker_details.houseSittingLongTerm='1'
)