Bloody marvelous...!!!
Many thanks.. I'll set myself a few challenges, and see what happens...!
Cheers,
Tris...
"Gryffyn, Trevor" <TGryffyn@xxxxxxxxxxxxxxxxx>
01/12/2004 15:47
To
<php-db@xxxxxxxxxxxxx>
cc
<Tristan.Pretty@xxxxxxxxxxxxxxxx>
Subject
Basic JOIN tutorial - RE: [PHP-DB] Job interview test - I give
up...
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
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
