SQL joins

[chili5]

I'm making a web page that is supposed to display for any order what products have been ordered, how many products ordered, and what side the user has chosen.

This data is located across several tables. I have a table products_ordered which serves as a link to the products table, so I can get the name of the product. The products_ordered table also links to the the sides table, so I can find out what the side is.

The tables look like this:

products table:

CREATE TABLE IF NOT EXISTS `products` (
  `prd_id` int(11) NOT NULL AUTO_INCREMENT,
  `prd_name` varchar(100) NOT NULL,
  `prd_active` enum('1','0') NOT NULL DEFAULT '1',
  PRIMARY KEY (`prd_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=25 ;

Products ordered:

CREATE TABLE IF NOT EXISTS `products_ordered` (
  `po_ord_id` int(11) NOT NULL COMMENT 'a foreign key that links to the order table.',
  `po_prd_id` int(11) NOT NULL COMMENT 'the product id this links to the products table.',
  `po_qty` tinyint(4) NOT NULL COMMENT 'the number of entrees the user has selected.',
  `po_side` int(11) NOT NULL COMMENT 'A link to the side that the user has requested.'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Order table:

CREATE TABLE IF NOT EXISTS `orders` (
  `ord_id` int(11) NOT NULL AUTO_INCREMENT,
  `ord_date` datetime NOT NULL,
  `ord_size` varchar(15) NOT NULL COMMENT 'Size of the order, full, split or single.',
  `ord_qty` tinyint(4) NOT NULL COMMENT 'number of entrees the user wants. depends on size',
  `ord_custid` varchar(16) NOT NULL COMMENT 'foreign key linking to the customers table.',
  `ord_refid` varchar(16) NOT NULL COMMENT 'foreign key linking to the referrals and customer tables.',
  `ord_grpid` int(11) NOT NULL,
  `ord_delivery` enum('1','0') NOT NULL COMMENT '1 means the user wants delivery. 0 means no delivery!',
  `ord_credit` double NOT NULL COMMENT 'The amount of credit used with this order.',
  PRIMARY KEY (`ord_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=116 ;

Sides table:

CREATE TABLE IF NOT EXISTS `sides` (
  `side_id` int(11) NOT NULL AUTO_INCREMENT,
  `side_name` varchar(50) NOT NULL,
  `side_active` enum('1','0') NOT NULL DEFAULT '1' COMMENT '1 means this side is being offered. 0 means that this side is no longer avaiable. display ''none'' for side.',
  PRIMARY KEY (`side_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ;

Basically the Orders table links to the products_ordered table, so I can find out what products were ordered. The products_ordered table links to the sides table and the products table. I'm not sure how I can get all the information I need from the database though.

I know it uses joins, but SQL joins are really confusing me.

I tried this:

$sql = "SELECT p.prd_name, po.po_qty, s.side_name FROM products as p, products_ordered AS po, sides AS s INNER JOIN products ON po.po_ord_id=orders.ord_id;";

But it didn't work. Can someone help clear up my confusion?

[BlaineSch]

Try taking the ; out from between the ;

If your joining 3 tables - which I have only done once - I remember I did it one query where I joined the 2 tables, then put those in ()'s then I did another join - I actually used left join

$sql = "SELECT p.prd_name, po.po_qty, s.side_name FROM products as p, products_ordered AS po, sides AS s INNER JOIN products ON po.po_ord_id=orders.ord_id";

It looks like your not joining P based on anything either

SQL LEFT JOIN Keyword

Id try and help more but im not very good with mysql and I dont want to install a bunch of tables to play with lol but here is a rough shot:

SELECT *
FROM (products
LEFT JOIN products_ordered
ON products.po_prd_id=products.prd_id)
LEFT JOIN orders
ON products.po_ord_id=orders.ord_id 

[chili5]

Thanks man, you are very helpful. I'm almost done, I'm just not sure how I can join a sides table to this:

$sql = "SELECT prd_name, po_qty FROM (orders INNER JOIN products_ordered ON orders.ord_id=products_ordered.po_ord_id) INNER JOIN products ON products.prd_id=products_ordered.po_prd_id WHERE ord_id='$id';";

I want to join it on products_ordered.po_side=sides.side_id but I'm not sure where to add the next INNER join statement.

[BlaineSch]

Id assume:

$sql = "SELECT prd_name, po_qty FROM ((orders INNER JOIN products_ordered ON orders.ord_id=products_ordered.po_ord_id) INNER JOIN products ON products.prd_id=products_ordered.po_prd_id) --INNER JOIN HERE WHERE ord_id='$id';";

[chili5]

Thanks