I have a SQL statement that I use to grab movie profiles. When I
constructed
the input form, I decided I didn't want to use blank entries for
tables that
didn't have a entry for that particular film e.g. a poster wasn't
available
for sale.
This SQL statement in PHP works fine when EVERY table has an entry for a
title:
$sql=SELECT allposters.allpostersurl, allposters.imageurl,
officialsites.url, profiles.photo, profiles.photoalt, profiles.profile,
soundtracks.title AS cdtitle, soundtracks.asin, soundtracks.coverart,
titles.titlesid, titles.releaseyear, titles.title FROM allposters,
officialsites, profiles, soundtracks, titles WHERE
allposters.titlesid=titles.titlesid AND
officialsites.titlesid=titles.titlesid AND
profiles.titlesid=titles.titlesid
AND soundtracks.titlesid=titles.titlesid AND
profiles.profilesid='$profileid';
Of course, if one table is missing an entry for that film it
turns up empty.
I understand that MySQL (our host is using 3.22.32) doesn't support full
outer joins which is what I think might be needed in this situation.
Are my choices:
a) code a separate SQL call for each table
b) use a blank entry for the title if no other info exists
or is there a third way to make that statement work? For those of you who
use PHP, is there any pressing performance reason to use or not to use
choice 'a'?
I think Bob Hall has the answer to your questions.
Here's a link to his excellent guide on MySQL SQL:
http://users.starpower.net/rjhalljr/MySQL/sql.html
/ Carsten
--
Carsten H. Pedersen
keeper and maintainer of the bitbybit.dk MySQL FAQ
http://www.bitbybit.dk/mysqlfaq
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
