mysql/php query

I have a question about (i think) joining.

If I have a table in a database that has this info:

key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1

the favorite icecream table is this:
key - flavors
1 - vanilla
2 - chocolate
3 - strawberry

how do i do a query to display that judy's favorite is strawberry.

obviously this is a simple example. i am doing working on something
that is much more complex than this, but if anyone can give a hint i
can apply to the thing i am working on.

Thanks in advance!

On 17-Nov-2003, redhatlinux@msn.com (aaron) wrote:
[color=blue]
> I have a question about (i think) joining.
>
> If I have a table in a database that has this info:
>
> key - name - favorite
> 1 - john - 2
> 2 - judy - 3
> 3 - joe - 1
>
> the favorite icecream table is this:
> key - flavors
> 1 - vanilla
> 2 - chocolate
> 3 - strawberry
>
> how do i do a query to display that judy's favorite is strawberry.
>
> obviously this is a simple example. i am doing working on something
> that is much more complex than this, but if anyone can give a hint i
> can apply to the thing i am working on.[/color]

select flavors from nametable,icecreamtable where favorite=icecreamtable.key
and name='judy'

--
Tom Thackrey
www.creative-light.com
tom (at) creative (dash) light (dot) com
do NOT send email to jamesbutler@willglen.net (it's reserved for spammers)

aaron, doing a poor impression of Xerex, said:[color=blue]
>
> I have a question about (i think) joining.[/color]

This is really more of an SQL question than a PHP question.

[color=blue]
> If I have a table in a database that has this info:
> key - name - favorite
> the favorite icecream table is this:
> key - flavor[/color]

Here's TFM: http://www.mysql.com/doc/en/JOIN.html

Here's your simple answer, since I'm not a complete asshole (and also I
personally find it easier to learn from example):

$q = mysql_query("SELECT * FROM people
LEFT JOIN favorites
ON people.favorite = favorites.key
WHERE name = 'judy'");
if(!$q) die("query failed\n");
if(!mysql_num_rows($q)) die("no entry for judy\n");
$rec = mysql_fetch_assoc($q);
echo "judy's favorite is $rec[flavor]\n";

/joe
--
In git.talk.flame, Dr. Esque mentally reviles in Scott Hughes and a
processor, and then often links to the website of /home/pr0n and mcct and
hoovers, downloads, scans, and carefully emasculates Marilyn. The ninja
clan from icer will go to Irwin! In the Masquerade, Crai... [tape runs out]

*** aaron wrote/escribió (17 Nov 2003 08:49:53 -0800):[color=blue]
> I have a question about (i think) joining.
>
> If I have a table in a database that has this info:
>
> key - name - favorite
> 1 - john - 2
> 2 - judy - 3
> 3 - joe - 1
>
> the favorite icecream table is this:
> key - flavors
> 1 - vanilla
> 2 - chocolate
> 3 - strawberry
>
> how do i do a query to display that judy's favorite is strawberry.[/color]

Under MySQL you have two ways:

SELECT name, favourite
FROM people, flavours
WHERE favourite=flavours.key AND name='judy'

or

SELECT name, favourite
FROM people
INNER JOIN flavours ON favorite=flavours.key
WHERE name='judy'

Second one is more standard.

(Untested so typos expected)

--
--
-- Álvaro G. Vicario - Burgos, Spain
--

redhatlinux@msn.com (aaron) schrieb:
[color=blue]
> If I have a table in a database that has this info:
>
> key - name - favorite
> 1 - john - 2
> 2 - judy - 3
> 3 - joe - 1
>
> the favorite icecream table is this:
> key - flavors
> 1 - vanilla
> 2 - chocolate
> 3 - strawberry
>
> how do i do a query to display that judy's favorite is strawberry.[/color]

SELECT icecream.flavor
FROM person, icecream
WHERE person.name = 'judy'
AND person.favorite = icecream.key

Regards,
Matthias

I noticed that Message-ID:
<dd6e9aa5.0311170849.25a137@posting.google.com> from aaron contained the
following:
[color=blue]
>key - name - favorite
>1 - john - 2
>2 - judy - 3
>3 - joe - 1
>
>the favorite icecream table is this:
>key - flavors
>1 - vanilla
>2 - chocolate
>3 - strawberry
>
>how do i do a query to display that judy's favorite is strawberry.[/color]

SELECT flavors FROM people,favorite WHERE name ='judy' AND
name.favourite =favorite.key;

--
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/

On Mon, 17 Nov 2003 17:37:41 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:
[color=blue]
>aaron, doing a poor impression of Xerex, said:[color=green]
>>
>> I have a question about (i think) joining.[/color]
>
>This is really more of an SQL question than a PHP question.
>[color=green]
>> If I have a table in a database that has this info:
>> key - name - favorite
>> the favorite icecream table is this:
>> key - flavor[/color]
>
>Here's TFM: http://www.mysql.com/doc/en/JOIN.html
>
>Here's your simple answer, since I'm not a complete asshole (and also I
>personally find it easier to learn from example):
>
> $q = mysql_query("SELECT * FROM people
> LEFT JOIN favorites
> ON people.favorite = favorites.key
> WHERE name = 'judy'");
> if(!$q) die("query failed\n");
> if(!mysql_num_rows($q)) die("no entry for judy\n");
> $rec = mysql_fetch_assoc($q);
> echo "judy's favorite is $rec[flavor]\n";[/color]

I think you mean INNER JOIN, not LEFT JOIN; LEFT JOIN is the same as LEFT
OUTER JOIN, and so would only be applicable here if the database's referential
integrity was broken - i.e. judy's people.favourite field didn't match any of
the keys in the favourite icecream table, but you still wanted the people row.
Doing an outer join where an inner join is really wanted may have performance
implications as well.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Andy Hassall (79.740% quality rating):[color=blue]
>
> I think you mean INNER JOIN, not LEFT JOIN; LEFT JOIN is the same as LEFT
> OUTER JOIN, and so would only be applicable here if the database's
> referential integrity was broken - i.e. judy's people.favourite field
> didn't match any of the keys in the favourite icecream table, but you
> still wanted the people row.[/color]

I did mean the LEFT JOIN, but I usually opt for more information rather
than less and do extra error-checking in my PHP code.
[color=blue]
> Doing an outer join where an inner join is really wanted may have performance
> implications as well.[/color]

Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
be that much worse than an INNER JOIN with a WHERE clause equating to
roughly the same thing. Is there an order of magnitude difference?

/joe
--
In El Myr, some bastard from IS kisses David Maynor, and then powers up a
preprocessor from Ryan Chaves. A huggable sorority house from Steve
Simonsen will go to Stevie Strickland.

On Mon, 17 Nov 2003 19:30:11 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:
[color=blue]
>Andy Hassall (79.740% quality rating):[color=green]
>>
>> I think you mean INNER JOIN, not LEFT JOIN; LEFT JOIN is the same as LEFT
>> OUTER JOIN, and so would only be applicable here if the database's
>> referential integrity was broken - i.e. judy's people.favourite field
>> didn't match any of the keys in the favourite icecream table, but you
>> still wanted the people row.[/color]
>
>I did mean the LEFT JOIN, but I usually opt for more information rather
>than less and do extra error-checking in my PHP code.[/color]

OK, but I'd argue that if you needed an outer join in this specific case, then
the database is broken; referential integrity checks really belong in the
database (although the real world sometimes gets in the way of that). Even
MySQL 3.x has foreign key constraints now.
[color=blue][color=green]
>> Doing an outer join where an inner join is really wanted may have performance
>> implications as well.[/color]
>
>Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
>be that much worse than an INNER JOIN with a WHERE clause equating to
>roughly the same thing. Is there an order of magnitude difference?[/color]

Well, It Depends. But any time you fetch more data than you need, there's a
difference. And once you get past trivial queries, using outer joins where
they're not needed can certainly change for the worse and constrain the access
paths your database can use.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

Disco Plumber (74.510% quality rating):[color=blue]
>
> Well, I'm no DBA, but I wouldn't expect a LEFT JOIN with an ON clause to
> be that much worse than an INNER JOIN with a WHERE clause equating to
> roughly the same thing. Is there an order of magnitude difference?[/color]

The only information I found regarding this in MySQL's docs was:

http://www.mysql.com/doc/en/LEFT_JOIN_optimisation.html

which implies that LEFT JOINS have extra optimization done.

Regardless, the database implementation should not be my concern as a
PHP programmer. If I am doing valid SQL queries with fairly sound logic
(i.e., putting the processing in the right places, not making
unnecessary amounts of queries), the underlying implementation of one
JOIN versus another should be irrelevant to me. Of course, if I am
querying for information I'm not going to use (e.g., if I was going to
ignore those rows of the result where fields came back NULL), that is a
waste of processing.

But anyway I decided to write a script to do benchmarks for myself...

And the results are almost random (load dependent). There's no clear
winner. Of course, MySQL may be doing caching. But then, MySQL would be
doing caching for the actual service as well.

For reference:

$ mysql --version
mysql Ver 11.16 Distrib 3.23.49, for pc-linux-gnu (i686)

Check out a handful of the test results:

500 left joins (on): 1.480120 sec
500 inner joins: 0.505901 sec
500 left joins (using): 0.549247 sec

500 left joins (on): 1.678084 sec
500 inner joins: 0.723299 sec
500 left joins (using): 0.877342 sec

500 left joins (on): 0.488853 sec
500 inner joins: 0.696226 sec
500 left joins (using): 0.481876 sec

1000 left joins (on): 0.975070 sec
1000 inner joins: 1.654450 sec
1000 left joins (using): 0.969804 sec

1000 left joins (on): 0.993082 sec
1000 inner joins: 1.018203 sec
1000 left joins (using): 1.094612 sec

10000 left joins (on): 10.364384 sec
10000 inner joins: 11.173975 sec
10000 left joins (using): 13.425231 sec

10000 left joins (on): 11.104748 sec
10000 inner joins: 13.026637 sec
10000 left joins (using): 9.970058 sec

10000 left joins (on): 10.662058 sec
10000 inner joins: 10.493683 sec
10000 left joins (using): 16.690147 sec

Here's the script I used:

#!/usr/bin/php4 -q
<?php

include("common.php"); // database connection

define('ITERATIONS', 10000);

function diff($start, $end) {
list($stu, $sts) = explode(" ", $start);
$start = (float)$stu + (float)$sts;
list($etu, $ets) = explode(" ", $end);
$end = (float)$etu + (float)$ets;
return (float)($end - $start);
}

function bench($query, $desc) {
$start = microtime();
for($i=0;$i<ITERATIONS;$i++)
mysql_query($query);
$end = microtime();
$diff = diff($start, $end);
printf("%d %s: %f sec\n", ITERATIONS, $desc, $diff);
}

bench("SELECT * FROM users LEFT JOIN user_settings
ON users.uid = user_settings.uid
WHERE uname = 'phatjoe'",
"left joins (on)");

bench("SELECT * FROM users,user_settings
WHERE users.uid = user_settings.uid
AND uname = 'phatjoe'",
"inner joins");

bench("SELECT * FROM users LEFT JOIN user_settings
USING (uid)
WHERE uname = 'phatjoe'",
"left joins (using)");

?>

/joe
--
A dead relative's processor from the 118 will go to Myke.

Andy Hassall (4.980% quality rating):[color=blue]
>
> OK, but I'd argue that if you needed an outer join in this specific
> case, then the database is broken; referential integrity checks
> really belong in the database (although the real world sometimes gets
> in the way of that).[/color]

I'm not sure what you're saying here about referential integrity checks
belonging in the database. In the simple example given, if someone is
added to the people table, but no corresponding entry for them is ever
added to the favorites table, how does the database know whether or not
that is a problem (and what should it do if it is?).
[color=blue]
> Well, It Depends. But any time you fetch more data than you need, there's a
> difference. And once you get past trivial queries, using outer joins where
> they're not needed can certainly change for the worse[/color]

I frequently use them in more complex queries, even joining more than
two tables. However, in most cases where I want a LEFT JOIN, it's
because there may be no corresponding entries in the second (and
potentially third) table(s).

But still, there are cases where there should be no good reason for the
second table to be missing the corresponding entry, but I don't want to
omit output data if the entry is somehow missing. Like in the sample
code where I was checking "users LEFT JOIN user_settings". A user might
accidentally not have had his user_settings record created... but that
doesn't mean the user doesn't exist, and so if I am trying to show a
list of users with some piece of information from their user settings, I
will do a left join to make sure I get all the users. The alternative
(if I am not willing to trust the user_settings table to have all of the
users' entries) would seem to be to do a select on the users table and
then N selects on the user_settings table, but that's clearly less
efficient (N+1 queries vs. 1 query).
[color=blue]
> and constrain the access paths your database can use.[/color]

I don't know what this means at all.

/joe
--
The choad is wholesale. The 3LA is slimy and educational.

On Mon, 17 Nov 2003 21:27:35 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:
[color=blue]
>Andy Hassall (4.980% quality rating):[color=green]
>>
>> OK, but I'd argue that if you needed an outer join in this specific
>> case, then the database is broken; referential integrity checks
>> really belong in the database (although the real world sometimes gets
>> in the way of that).[/color]
>
>I'm not sure what you're saying here about referential integrity checks
>belonging in the database. In the simple example given, if someone is
>added to the people table, but no corresponding entry for them is ever
>added to the favorites table, how does the database know whether or not
>that is a problem (and what should it do if it is?).[/color]

But that can't happen in the data model given; favourite was part of the first
table.

The data given was:

key - name - favorite
1 - john - 2
2 - judy - 3
3 - joe - 1

the favorite icecream table is this:
key - flavors
1 - vanilla
2 - chocolate
3 - strawberry

Since 'favorite' was part of the 'person' row, then the only situations where
an outer join would apply would be:

(a) favorite was null (this could be a valid case for an outer join)
(b) favorite was set to a value that does not appear in the favorite table
(this was the case I was referring to as being broken, since I was assuming
'favorite' was declared as not null in the first table).

Case (b) should be caught by a foreign key constraint, and so on the
assumption that favorite is not null, there's no valid situation for an outer
join.
[color=blue][color=green]
>> Well, It Depends. But any time you fetch more data than you need, there's a
>> difference. And once you get past trivial queries, using outer joins where
>> they're not needed can certainly change for the worse[/color]
>
>I frequently use them in more complex queries, even joining more than
>two tables. However, in most cases where I want a LEFT JOIN, it's
>because there may be no corresponding entries in the second (and
>potentially third) table(s).[/color]

Yes, that's the purpose of an outer join.
[color=blue]
>But still, there are cases where there should be no good reason for the
>second table to be missing the corresponding entry, but I don't want to
>omit output data if the entry is somehow missing. Like in the sample
>code where I was checking "users LEFT JOIN user_settings". A user might
>accidentally not have had his user_settings record created... but that
>doesn't mean the user doesn't exist, and so if I am trying to show a
>list of users with some piece of information from their user settings, I
>will do a left join to make sure I get all the users. The alternative
>(if I am not willing to trust the user_settings table to have all of the
>users' entries) would seem to be to do a select on the users table and
>then N selects on the user_settings table, but that's clearly less
>efficient (N+1 queries vs. 1 query).[/color]

Sure, if it makes sense (or is convenient) to bring rows back where there is
no relation. On the other hand, if you're querying for a user's settings, it
doesn't necessarily make sense to bring back a row if there are no settings in
the table. That doesn't imply the user doesn't exist, just that that user has
no settings.

I see the point you're making (and even agree!), but what I'm trying (badly)
to get across was that the original question never called for an outer join,
just an ordinary join. There's no advantage to having it return one row with
the flavour field NULL, versus no rows indicating no favourites matched.

As you said on the other reply:
[color=blue]
>Of course, if I am
>querying for information I'm not going to use (e.g., if I was going to
>ignore those rows of the result where fields came back NULL), that is a
>waste of processing.[/color]

Exactly! :-)

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)

While thinking about how cool Tipper Gore was, Andy Hassall blurted:[color=blue]
>
> But that can't happen in the data model given; favourite was part of
> the first table.[/color]

good point, I should have checked the initial example again before
spouting.
[color=blue]
> Case (b) should be caught by a foreign key constraint, and so on the
> assumption that favorite is not null, there's no valid situation for an outer
> join.[/color]

ah, so in a foreign key constraint I can tell the db "don't allow me to
add this person record if there is no matching flavor record for his
favorite"?
[color=blue]
> doesn't necessarily make sense to bring back a row if there are no settings in
> the table. That doesn't imply the user doesn't exist, just that that user has
> no settings.[/color]

yes, the example I cited was actually a combined "get all users" and
"get their settings" simultaneously, so it made sense to do the left
join.

/joe
--
In the emo garage, the chair from Cuddles the Cat will go to Psi U. Cuddles
the Cat's delightful computer from Hojohoro Bekahamu will go to Sarah H..
Mike Doyle ignores the monitor from Faff.

On Mon, 17 Nov 2003 22:59:36 +0000 (UTC), Disco Plumber
<scag@moralminority.org> wrote:
[color=blue][color=green]
>> Case (b) should be caught by a foreign key constraint, and so on the
>> assumption that favorite is not null, there's no valid situation for an outer
>> join.[/color]
>
>ah, so in a foreign key constraint I can tell the db "don't allow me to
>add this person record if there is no matching flavor record for his
>favorite"?[/color]

Yes - it'll raise an error that there is no parent record.

Additionally it won't let you delete the flavour record leaving 'dangling'
rows in the people table that were referencing it; it'll complain there are
child records present. Typically you can set the constraint to either raise an
error, 'on delete cascade' (deleting the child rows too), or 'on delete set
null'.

--
Andy Hassall (andy@andyh.co.uk) icq(5747695) (http://www.andyh.co.uk)
Space: disk usage analysis tool (http://www.andyhsoftware.co.uk/space)