Looks like you got a lot of good responses. I downloaded the zip file but then got slammed with some priority stuff and havn't had a chance to take a look at this. So in the interest of not forgetting and of closing this reply window :) just a quicky response. I had a lot of trouble initially wrapping my brain around JOINs conceptually but they're really rather easy. Here's my 2 cent tour (because most of the documentation I read didn't really put it in "plain english"). Someone please correct me or add to what I've said if it's not accurate: Inner Join - This is when you have two tables and the items in the ON clause need to exist in both tables for you to get a row returned. Doing a " a INNER JOIN b ON somecriteria " is functionally the same as doing "from a, b where somecriteria" Maybe there's a performance difference, maybe only on certain database systems, not sure. They should return the same output: SELECT trucks.truckid, crates.crateid FROM trucks INNER JOIN crates ON trucks.truckid = crates.truckid Should be the same as... SELECT trucks.truckid, crates.crateid FROM trucks, crates WHERE trucks.truckid = crates.truckid This will return all trucks that have crates on them. If a truck is empty, it doesn't show up on the list. Outer Joins - This is where you know you have items in one table, but may or may not have items in another table. SELECT trucks.truckid, crates.crateid FROM trucks LEFT JOIN creates ON trucks.truckid = crates.truckid This should give you a list of all trucks, even empty ones, and their associated crates (if there are any.. If no crates are on the trucks, then an empty cell is returned for 'crateid'. SELECT trucks.truckid, crates.crateid FROM creates RIGHT JOIN trucks ON trucks.truckid = crates.truckid This should return the same thing. The LEFT or RIGHT "points" to the table that definitely has rows, the other table will show empty (NULL) cells if there are no matches. I believe there's a join that will show NULLs in either column if there's no match, but I don't know what it is off the top of my head. Anyone know this one? Also, some systems like Oracle, let you do shorthand like: SELECT trucks.truckid, crates.crateid FROM creates, trucks WHERE trucks.truckid = crates.truckid(+) (this syntax is probably wrong). It uses a (+) in the WHERE clause to indicate the join. Microsoft SQL-Server has some kind of shorthand way to do this as well, but it's documented as "never having worked properly" so I'd recommend not using it. To me, the Oracle shorthand is much more intuitive, but once you get the hang of the LEFT JOIN type syntax, it's not too bad. One last note, you can JOIN on multiple tables, you just need to wrap everything in parentheses. It gets a little sticky, but work from the inside out and keep an image in your head of what's going to be returned by each layer and join accordingly. If you have Microsoft Access or something else that graphically lets you create queries, you might try building a query that you know works and returns what you need, then looking at the SQL VIEW to see how the syntax goes. It can help you understand how to do the raw SQL yourself. Good luck! -TG > -----Original Message----- > From: Tristan.Pretty@xxxxxxxxxxxxxxxx > [mailto:Tristan.Pretty@xxxxxxxxxxxxxxxx] > Sent: Wednesday, December 01, 2004 8:14 AM > To: php-db@xxxxxxxxxxxxx > Subject: Job interview test - I give up... > > > I recently saw a job that I thought I'd be good for, so they > sent me a > test... > All good.. but I realised that I couldn't do it.. > I got sooo damned close, but could not finish... :-( > > Anyhoo, here's the test: > http://www.beertastic.co.uk/test_problem.zip > > And the test parameters are below > If anyone can tell me the answer, I'd appreciate it, then I can > de-engineer it, and hopefully learn something. > I've already mailed them and said thanks, but I give up ;-) > > I my prob was in gettin ga distict list of all DVD > categories, from within > the final results page... > > Hmmm, any takers? > > TASK:- > > Modify the test.php file (attached) so a list of categories and the > number of DVDs in each category are displayed when the script > is called > in a browser. (See resultspage.html for the EXACT output required) > > You must complete the getNumDvdsInCategories() function to query the > database and return a multidimensional array in the required format. > > You may use the PHP and MySQL manuals available online. > > > RESTRICTIONS:- > > You may only make ONE select query to the database. > Do not edit code outside the getNumDvdsInCategories() function. The > results in the table must be in alphabetical order (category > name). The > Drama category MUST appear even though there are no DVDs in the > category. Categories beginning with the letter "T" must not be > displayed. Values in the database cannot be hardcoded anywhere in the > script. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
