有三张表,想用这三张表进行联合查询:Orders Table
CREATE TABLE `orders` (
`orderid` int(10) NOT NULL auto_increment,
`customerid` int(10) NOT NULL,
`orderdate` date NOT NULL,
`orderstatus` int(1) NOT NULL,
PRIMARY KEY (`orderid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=3 ;Orderdetails
CREATE TABLE `orderdetails` (
`orderid` int(10) NOT NULL,
`solutionid` int(5) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8;SolutionsCREATE TABLE `solutiondetails` (
`id` int(20) NOT NULL auto_increment,
`solutionid` int(5) NOT NULL,
`productid` int(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=22 ;我使用语句:
select orders.*,customers.contactperson,customers.company,solutions.title,solutions.pricefrom orders
left join (orderdetails,solutions,customers)
on (orderdetails.solutionid = solutions.solutionid and customers.customerid = orders.customerid)
得到下面的结果:orderid customerid orderdate orderstatus contactperson company title price
1 1 2008-02-26 1 Zhu Yuzhou Tenovar Ltd. Teamtouch Server and Client 1000.55
1 1 2008-02-26 1 Zhu Yuzhou Tenovar Ltd. This is a test solution 10.00
2 1 2008-02-11 2 Zhu Yuzhou Tenovar Ltd. Teamtouch Server and Client 1000.55
2 1 2008-02-11 2 Zhu Yuzhou Tenovar Ltd. This is a test solution 10.00结果重复,请问怎么去掉重复的内容??只要保持 orderid不重复就可以了。
`company` varchar(100) NOT NULL,
`contactperson` varchar(50) NOT NULL,
`customerid` int(10) NOT NULL auto_increment,
`email` varchar(100) NOT NULL,
`username` varchar(20) NOT NULL,
`password` varchar(20) NOT NULL,
`regdate` date NOT NULL,
PRIMARY KEY (`customerid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=9 ;
FROM orders
LEFT JOIN (
orderdetails, solutions, customers
) ON ( orderdetails.solutionid = solutions.solutionid
AND customers.customerid = orders.customerid
AND orders.orderid = orderdetails.orderid ) 这个句子,就对了,LEFT JOIN 少条件。
left join (orderdetails,solutions,customers)
on (orderdetails.solutionid = solutions.solutionid and customers.customerid = orders.customerid and orders.orderid = orderdetails.orderid)问题,已经 解决。