syntax join

[thunderwap]

hello

i need help with join syntax here.anybody can plz do join syntax of

Return the First Name, Last Name, Product Name, and Sale Price for all products sold in
the month of October 2005.

i want join syntax. inner join is same as join.so i want join only.not outer or cross.


i did but i am facing error. i did

select customers.firstname,lastname, products.pname,sales.sprice from sales join products on sales.pid=products.pid where sdate between '2005-10-01' and '2005-10-31'; 

--
-- Table structure for table `customers`
--

CREATE TABLE IF NOT EXISTS `customers` (
  `cid` int(20) NOT NULL AUTO_INCREMENT,
  `firstname` varchar(50) DEFAULT NULL,
  `lastname` varchar(50) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state` varchar(2) DEFAULT NULL,
  `zip` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=21 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`cid`, `firstname`, `lastname`, `city`, `state`, `zip`) VALUES
(1, 'chintan', 'patel', 'anand', 'gj', '388001'),
(2, 'paresh', 'prajapati', 'nadiad', 'gj', '387001'),
(3, 'pragnesh', 'patel', 'surat', 'gj', '395008'),
(4, 'nilesh', 'dharsandia', 'mumbai', 'mh', '400002'),
(5, 'sonal', 'patel', 'mumbai', 'mh', '400002'),
(6, 'harshal', 'patel', 'mogri', 'gj', '388345'),
(7, 'prakash', 'rathod', 'mogri', 'gj', '388345'),
(8, 'aarzoo', 'dodhiya', 'rajkot', 'gj', '360003'),
(9, 'heta', 'dave', 'varanasi', 'up', '221002'),
(10, 'nikita', 'dave', 'varanasi', 'up', '221002'),
(11, 'vaibhav', 'dave', 'varanasi', 'up', '221002'),
(12, 'paresh', 'patel', 'pune', 'mh', '411001'),
(13, 'prakash', 'patel', 'pune', 'mh', '411001'),
(14, 'sandhya', 'patel', 'hyderabad', 'ap', '500031'),
(15, 'divesh', 'patel', 'bangalore', 'ka', '560002'),
(16, 'payal', 'shah', 'bangalore', 'ka', '560002'),
(17, 'priyanka', 'rana', 'anand', 'gj', '388001'),
(18, 'sanket', 'dhebar', 'v.v.nagar', 'gj', '388121'),
(19, 'puja', 'shah', 'varanasi', 'up', '221002'),
(20, 'priya', 'shah', 'varanasi', 'up', '221002');

-- --------------------------------------------------------

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `pid` int(20) NOT NULL AUTO_INCREMENT,
  `productname` varchar(20) DEFAULT NULL,
  `recommendedprice` varchar(20) DEFAULT NULL,
  `category` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`pid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`pid`, `productname`, `recommendedprice`, `category`) VALUES
(1, 'dvd', '105', 'livingroom'),
(2, 'microwave', '98', 'kitchen'),
(3, 'monitor', '200', 'office'),
(4, 'speakers', '85', 'office'),
(5, 'refrigerator', '900', 'kitchen'),
(6, 'vcr', '165', 'livingroom'),
(7, 'coffee pot', '35', 'kitchen');

-- --------------------------------------------------------

--
-- Table structure for table `sales`
--

CREATE TABLE IF NOT EXISTS `sales` (
  `sid` int(20) NOT NULL AUTO_INCREMENT,
  `pid` int(20) DEFAULT NULL,
  `cid` int(20) DEFAULT NULL,
  `sprice` varchar(20) DEFAULT NULL,
  `sdate` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`sid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=32 ;

--
-- Dumping data for table `sales`
--

INSERT INTO `sales` (`sid`, `pid`, `cid`, `sprice`, `sdate`) VALUES
(1, 1, 1, '130', '2005-06-2014'),
(2, 2, 2, '97', '2005-06-19'),
(3, 3, 3, '200', '2005-09-20'),
(4, 4, 4, '80', '2005-03-2022'),
(5, 5, 5, '899', '2005-01-23'),
(6, 6, 6, '150', '2005-03-24'),
(7, 3, 7, '209', '2005-03-10'),
(8, 4, 8, '90', '2005-08-11'),
(9, 6, 9, '130', '2005-08-12'),
(10, 2, 14, '85', '2005-12-13'),
(11, 3, 15, '240', '2005-05-14'),
(12, 1, 17, '87', '2005-07-19'),
(13, 2, 18, '99', '2005-09-20'),
(14, 6, 19, '150', '2005-07-22'),
(15, 5, 5, '900', '2005-03-06'),
(16, 4, 6, '86', '2005-04-07'),
(17, 2, 7, '88', '2005-11-08'),
(18, 3, 8, '198', '2005-05-09'),
(19, 1, 9, '150', '2005-10-10'),
(20, 6, 14, '99', '2005-05-09'),
(21, 6, 15, '104', '2005-09-20'),
(22, 4, 14, '90', '2005-07-22'),
(23, 1, 1, '130', '2005-03-06'),
(24, 2, 2, '102', '2005-04-07'),
(25, 1, 3, '114', '2005-11-08'),
(26, 5, 4, '1000', '2005-05-09'),
(27, 5, 5, '1100', '2005-10-10'),
(28, 3, 6, '285', '2005-06-11'),
(29, 2, 7, '87', '2005-10-12'),
(30, 3, 8, '300', '2005-07-13'),
(31, 3, 20, '205', '2005-12-31'); 

[something else]

select c.firstname,
    c.lastname,
    p.pname,
    s.sprice 
from sales s
    join products p
    on s.pid=p.pid 
    join customers c
    on p.pid = s.pid

where s.sdate between '2005-10-01' and '2005-10-31'; //between needs to be an integer not a string 

[thunderwap]

select c.firstname,
c.lastname,
p.pname,
s.sprice
from sales s
join products p
on s.pid=p.pid
join customers c
on p.pid = s.pid

where s.sdate between '2005-10-01' and '2005-10-31'; //between needs to be an integer not a string 

my problem got solved.thanks.

it should be "from sales join products on sales.pid=products.pid"

i.e

select customers.firstname,lastname, products.productname,sales.sprice
from sales
join products on sales.pid=products.pid
join customers on sales.cid=customers.cid
where date(sales.sdate) between '2005-10-01' and '2005-10-31';

and there should be date(sales.sdate) between....... instead s.sdate between.......

if sales is not defined before sdate then it will not take sales date and shows error.i forgot this join syntax and so i refered normalization and join syntax again.

thank you for help mate.