|
| |
|
|
womble
Posts: 5510
Joined: 3/14/2005
From: Living on the edge
Status: offline
 |
Converting a 'where' clause to inner join - 11/17/2006 8:08:59
/wanders into db forum looking nervously around
I'm now officially bewildered (I was never confused). I'm doing a MySQL/Coldfusion course, and one of this week's exercises is to convert a query with a 'where' clause to an inner join. I obviously don't understand joins as well as I thought I did because I've no idea what I'm doing here - whatever I try the MySQL monitor just throws errors back at me.
The attached pic is the set-up, and this is the query for converting to an inner join:
SELECT sname FROM sailors s, reserves r, boats b
WHERE sailorid=sailoridfk AND boatid=boatidfk AND bcolour='red';
And this is my efforts so far:
mysql> SELECT sname FROM sailors, reserves, boats
-> INNER JOIN bcolour ON boatID = 'red';
ERROR 1146 (42S02): Table 'ret26.bcolour' doesn't exist
mysql> SELECT sname FROM sailors, reserves, boats
-> INNER JOIN boatid.bcolour ON boatID = 'red';
ERROR 1142 (42000): SELECT command denied to user 'ret26'@'localhost' for table 'bcolour'
mysql> SELECT sname FROM sailors, reserves, boats
-> INNER JOIN sailors.sailorid ON boats.boatid =bcolour='red';
ERROR 1142 (42000): SELECT command denied to user 'ret26'@'localhost' for table 'sailorid'
mysql> SELECT sname
-> FROM sailors
-> INNER JOIN sailors, boats
-> USING (boatID)
-> WHERE (boats.boatID = 'red');
ERROR 1066 (42000): Not unique table/alias: 'sailors'
mysql> SELECT sname
-> FROM sailors
-> INNER JOIN sailors.sailorid, boats.boatid
-> USING (boatID)
-> WHERE (boats.boatID = 'red');
ERROR 1142 (42000): SELECT command denied to user 'ret26'@'localhost' for table 'sailorid'
mysql> SELECT sname
-> FROM sailors
-> INNER JOIN sailors, boats, reserves ON (boats.boatID = 'red');
ERROR 1066 (42000): Not unique table/alias: 'sailors'
mysql> SELECT sname
-> FROM sailors
-> INNER JOIN sailors.sailorid, boats.boatid ON boats.boatid = 'red';
ERROR 1142 (42000): SELECT command denied to user 'ret26'@'localhost' for table 'sailorid'
mysql> SELECT sname FROM sailors AS s, boats AS b
-> INNER JOIN sailors ON s.sailorID=boatID ('red');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('red')' at line 2
mysql> SELECT sname FROM sailors s, reserves r, boats b
-> INNER JOIN sailors.sailorID ON boats.boatID AND b.bcolour='red';
ERROR 1142 (42000): SELECT command denied to user 'ret26'@'localhost' for table 'sailorID'
mysql>
mysql> select sname
-> FROM sailors.sailorID INNER JOIN reserves.sailoridfk=boats.boatID(bcolour='red');
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '.boatID(bcolour='red')' at line 2
mysql>
I sort of get the idea of the 'INNER JOIN' 'ON' thing, but I can't work out how to get the bcolour='red' bit into it.
The course forum's got about as much life as a cemetary, and I've been banging my head on the desk trying to figure this out since last night. Any advice appreciated.
Thumbnail Image
 Attachment (1)
_____________________________
~~ "A cruel god ain't no god at all" ~~
|
|
|
|
rubyaim
Posts: 757
Joined: 6/22/2005
Status: offline
|
RE: Converting a 'where' clause to inner join - 11/17/2006 16:17:48
Womble, this is a good quick reference for simple joins:
http://www.w3schools.com/sql/sql_join.asp
INNER JOIN returns all rows from tables where there is a match.
A simple INNER JOIN on 2 tables would look like
SELECT Field1, Field2, Field3, Table2.Field1
FROM Table1 INNER JOIN Table2 ON Table1.IDField = Table2.IDField
Another INNER JOIN on 3 tables using WHERE would look something like
SELECT Table1.Field1 AS Table1_Field1, Table1.Field2, Table1.Field3, Table2.Field1 AS Table2_Field1, Table2.Field2, Table2.Field3, Table3.Field1, Table3.Field2
FROM (Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1) INNER JOIN Table3 ON
(Table3.Field1 = Table2.Field1) AND (Table1.Field1 = Table3.Field1)
WHERE (((WhichTABLE.WhichFIELD)="whatever"));
I'm not a mySQL user but this should help with the correct syntax:
http://dev.mysql.com/doc/refman/5.0/en/join.html
Clear as mud? 
Start with a simple two table INNER JOIN and see how you go, when that works gradually build it up.
< Message edited by rubyaim -- 11/17/2006 16:26:54 >
_____________________________
Sally
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
