can i INNER JOIN 3 tables

[botcha]

http://hirealimo.com.au/code1.php

 

WHY DOES:

SELECT price.*, vehicle.* FROM price INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1

WORK,

 

YET THIS DOESNT:

SELECT price.*, vehicle.*, town.* FROM price, town INNER JOIN vehicle USING (vehicleID) WHERE vehicle.passengers >= 1 AND price.townID = 1 AND price.eventID = 1

 

can i not inner join 3 tables?

i need to retrieve info from price, vehicle and town tables !

[botcha]

ok, im now getting: Unknown column 'vehicleID' in 'from clause'

 

there is no vehicleID column in the town table, so i guess thats the problem.

 

Still doesnt solve my problem though, as i need some info from each table ( price, vehicle info, etc)

 

[jcbones]

When you are working with joins, it is important that you know what each type of join does. It is equally important to know how each joining clause works (USING, ON).  While you have discovered the underlying problem (USING clause must contain a column that exists in all tables.  You should also be aware of mixing JOINing types.

 

INNER JOIN and , (comma) are semantically equivalent in the absence of a join condition: both produce a Cartesian product between the specified tables (that is, each and every row in the first table is joined to each and every row in the second table).

 

However, the precedence of the comma operator is less than of INNER JOIN, CROSS JOIN, LEFT JOIN, and so on. If you mix comma joins with the other join types when there is a join condition, an error of the form Unknown column 'col_name' in 'on clause' may occur. Information about dealing with this problem is given later in this section.

 

After we get that out of the way, there lies only one question.  How is the town table tied to the data you wish to retrieve from the other tables?

[botcha]

ok, i have 4 tables ( vehicle, town, event & price)

each table has a ID (vehicleID, townID, eventID & priceID) as a primary key

 

e.g vehicle table

vehicleID = 1

type = sedan

description = fairlane

 

e.g town table

townID = 1

name = albury

postcode = 2640

 

eg event table

eventID = 1

type = wedding

 

the vehicleID, townID & eventID are foreign keys in the price table

 

the price table is where i need it to all join up

by selecting the vehicle, town, and event, id like it to show a price from the price table, and have the priceID

 

I think the solution may be far more simpler?? I feel like im trying to find a complex answer to a simple problem !!

 

[jcbones]

So, lets dissect your problem, then find a good solution.  We must go through the database Normalization, as there should never be duplicate data stored.  It just causes confusion, and complicates the exchange of data.

 

Relationships =

A = 1 to 1 (ie 1 town to 1 vehicle)

B = 1 to many (ie 1 town to many vehicles)

C = many to many (ie many towns to many vehicles)

 

1. Towns to vehicle = B? 1 town to many vehicles

2. Towns to events = A? 1 town to 1 event

3. events to vehicles = C? many events to many vehicles

 

So,  Here is how I would do it.

 

Database Dump, (with some sample data)

--
-- Table structure for table `events`
--

CREATE TABLE IF NOT EXISTS `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `town_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `name`, `time`, `town_id`) VALUES
(1, 'Wedding ', '2012-01-23 02:24:57', 2),
(2, 'Car Show', '2012-01-23 02:24:57', 3),
(3, 'Party', '2012-01-23 02:24:57', 4),
(4, 'Tea Party', '2012-01-23 02:24:57', 1);

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

--
-- Table structure for table `price`
--

CREATE TABLE IF NOT EXISTS `price` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `base` float(10,2) NOT NULL,
  `modifier` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `price`
--

INSERT INTO `price` (`id`, `base`, `modifier`) VALUES
(1, 120.99, 2),
(2, 250.99, 2);

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

--
-- Table structure for table `towns`
--

CREATE TABLE IF NOT EXISTS `towns` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `postcode` int(5) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `towns`
--

INSERT INTO `towns` (`id`, `name`, `postcode`) VALUES
(1, 'New York', 0),
(2, 'Chicago', 0),
(3, 'Las Vegas', 0),
(4, 'Houston', 0);

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

--
-- Table structure for table `vehicles`
--

CREATE TABLE IF NOT EXISTS `vehicles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `type` int(11) NOT NULL,
  `price_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `vehicles`
--

INSERT INTO `vehicles` (`id`, `name`, `type`, `price_id`) VALUES
(1, '2012 Crown Victoria', 3, 1),
(2, '2011 Chevy Box Truck', 2, 1),
(3, 'Lamborghini Gallardo', 4, 2);

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

--
-- Table structure for table `vehicle_event`
--

CREATE TABLE IF NOT EXISTS `vehicle_event` (
  `vehicle_id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `vehicle_event`
--

INSERT INTO `vehicle_event` (`vehicle_id`, `event_id`) VALUES
(2, 3),
(2, 4),
(1, 2),
(1, 4),
(4, 2),
(4, 3),
(3, 1),
(3, 3);

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

--
-- Table structure for table `vehicle_type`
--

CREATE TABLE IF NOT EXISTS `vehicle_type` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `style` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `vehicle_type`
--

INSERT INTO `vehicle_type` (`id`, `name`, `style`) VALUES
(1, 'Sedan', 'Convertible'),
(2, 'Van', 'Styleside'),
(3, 'Sedan', 'Coupe'),
(4, 'Sports', 'Exotic');

 

Here is the query

SELECT e.name AS event, t.name AS town, v.name AS vehicle, vt.name AS vehicleType, vt.style AS vehicleStyle, p.base AS price
FROM EVENTS AS e
JOIN (
towns AS t, vehicles AS v, vehicle_type AS vt, price AS p
) ON ( e.town_id = t.id )
WHERE v.id
IN (

SELECT vehicle_id
FROM vehicle_event
WHERE event_id = e.id
)
AND v.type = vt.id
AND v.price_id = p.id

 

There is really to much to explain, and not enough space to do it, read that as, I can't explain it well enough for you to comprehend it.  I would suggest you watching

.

[fenway]

It's a really bad idea to mix commas and JOINs.

[botcha]

what is a better way fenway?

[jcbones]

He wants you to write the word JOIN instead of using comma's, sometimes you run into a "unknown column name" with the comma syntax.

 

Like:

SELECT e.name AS event, t.name AS town, v.name AS vehicle, vt.name AS vehicleType, vt.style AS vehicleStyle, p.base AS price
FROM EVENTS AS e
JOIN (
towns AS t
JOIN vehicles AS v
JOIN vehicle_type AS vt
JOIN price AS p
) ON ( e.town_id = t.id )
WHERE v.id
IN (

SELECT vehicle_id
FROM vehicle_event
WHERE event_id = e.id
)
AND v.type = vt.id
AND v.price_id = p.id

[fenway]

Also, I want an ON clause for each and every table being JOIN'ed.

[botcha]

thanks to you both, i did watch all 9 videos that jcbones posted, very helpful.

 

this does pose another question:

 

if I have a table for PRICE only and specify each price (as per your example), wouldnt I have a table with values all over the place?

 

eg.

 

price.id = 1, value = $66

price.id = 2, value = $838

price.id = 3, value = $664

price.id = 4, value = $545

price.id = 5, value = $635

price.id = 6, value = $8538

 

is this ok to do? i suppose that many orders will have the same price, so thats probably a good thing?

 

 

[botcha]

also fenway could you please repost the code with all the ON in the right place (or do i just have to do it after each JOIN word?)

[kickstart]

Hi

 

Something like this:-

 

SELECT e.name AS event, t.name AS town, v.name AS vehicle, vt.name AS vehicleType, vt.style AS vehicleStyle, p.base AS price
FROM EVENTS AS e
JOIN towns AS t ON e.town_id = t.id 
JOIN vehicles AS v ON e.id = v.id
JOIN vehicle_type AS vt ON v.type = vt.id
JOIN price AS p ON v.price_id = p.id
WHERE v.passengers >= 1 
AND p.townID = 1 
AND p.eventID = 1

 

All the best

 

Keith

[botcha]

also do i need another table for orders to bring it all together?

 

how do i set the prices

 

e.g

 

car = ford, event = wedding, town = melboure, price = $300

 

do i need one big table to do this?

[kickstart]

Hi

 

Probably yes, especially as I presume you need some way of looking up which cars already have an order for which day.

 

But that table of orders would contain the id fields of the records on other tables that it refers to, not the values themselves.

 

All the best

 

Keith