Converting a 'where' clause to inner join

Converting a 'where' clause to inner join (Full Version)

All Forums >> [Web Development] >> ASP and Database

Message

womble -> 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.

[image]local://upfiles/14943/790AF017F2AC4D3A926759A916F87A04.gif[/image]

rubyaim -> 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.

womble -> RE: Converting a 'where' clause to inner join (11/17/2006 16:47:55)
Thanks Sally, and thanks for the links. That W3Schools one explains it a lot more simply than the course stuff does. With some help from a fellow student I eventually came up with this, which seems to come up with the required stuff... mysql> SELECT sname -> FROM sailors, boats -> INNER JOIN reserves -> ON sailorid=sailoridfk -> AND boatid=boatidfk -> AND bcolour='red'; +---------+ \| sname \| +---------+ \| Dustin \| \| Lubber \| \| Horatio \| \| Dustin \| \| Lubber \| +---------+ 5 rows in set (0.00 sec) For the next one they wanted a list of sailors who'd reserved a red or green boat... mysql> SELECT sname -> FROM sailors, boats -> INNER JOIN reserves -> ON sailorid=sailoridfk -> AND boatid=boatidfk -> AND (bcolour='red' OR bcolour='green'); I think I'm sort of getting the hang of it - next week's study guide's onto transactions and aggregate functions - oh joy! [&:] /shuffles off to resume trying to bully the mysql monitor into playing nice...

womble -> RE: Converting a 'where' clause to inner join (11/17/2006 17:58:29)
[sm=yupi3ti.gif] Yay! I must be getting the hang of this - I just managed to do an inner join in two stages across 3 tables, and once I'd figured out what the question was asking for it only took me a couple of minutes to figure out the SQL syntax... ...this is quite remarkable as I have a very long history of b0rking MySQL databases, and that's only through phpMyAdmin - now I can b0rk 'em good and proper! [:D] /Wombly has silly head on as has been staring at a MySQL monitor window for the last 13 hours and brain is now well and truly fried [img]http://ganjataz.com/Forum/images/smiles/nod.gif[/img]

Page: [1]

6.445313E-02