I have a database defined with the following tables:
CREATE TABLE pix (
id int(11) NOT NULL auto_increment,
propertyid int(11) NOT NULL default '0',
filename varchar(25) default NULL,
caption varchar(50) default NULL,
width smallint(4) unsigned NOT NULL default '0',
height smallint(4) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE pix_big (
id int(11) unsigned NOT NULL auto_increment,
pixid int(11) unsigned default '0',
bigpixid int(11) unsigned default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE pix_section (
id int(11) unsigned NOT NULL auto_increment,
name varchar(15) default NULL,
code varchar(9) default NULL,
count tinyint(3) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE pix_type (
id int(11) unsigned NOT NULL auto_increment,
name varchar(10) NOT NULL default '',
code varchar(4) NOT NULL default '0',
width smallint(4) unsigned NOT NULL default '0',
height smallint(4) unsigned NOT NULL default '0',
sizelimit tinyint(1) unsigned NOT NULL default '0',
PRIMARY KEY (id)
) TYPE=MyISAM;
CREATE TABLE pix_use (
id int(1) unsigned NOT NULL auto_increment,
pixid int(11) unsigned NOT NULL default '0',
pixsectionid int(11) unsigned NOT NULL default '0',
typeseq smallint(4) unsigned default NULL,
PRIMARY KEY (id)
) TYPE=MyISAM;
When I run the following query:
select
pix_section.code,pix.filename,pix.id,pix_use.id,pix_use.typeseq,pix.width,pix.height
from pix_use
left join pix on pix_use.pixid=pix.id
left join pix_section on pix_use.pixsectionid=pix_section.id
where pix.propertyid=202
order by pix_section.code,pix_use.typeseq
I get the appropriate results. But if I add a couple of fields, and a left
join clause that joins the pix table as bigpix, I loose the pix.filename of
any record that doesn't have an entry in the bigpix alias of pix. In other
words, any pix that has a bigpix defined is ok. But on any pix that does not
have a bigpix defined, I "loose" the filename info from pix.
And I don't understand why!
Here is the second query:
select
pix_section.code,pix.filename,pix.id,pix_use.id,pix_use.typeseq,pix.width,pix.height,pix_big.bigpixid,bigpix.filename
from pix_use
left join pix on pix_use.pixid=pix.id
left join pix_section on pix_use.pixsectionid=pix_section.id
left join pix_big on pix.id=pix_big.pixid left join pix as bigpix on
pix_big.bigpixid=bigpix.id
where pix.propertyid=202
order by pix_section.code,pix_use.typeseq
Bruce Hodo - Webmaster, GetAwayNetwork, Inc.
==Providing unique vacation information on the World Wide Web==
For Villas, Resorts, Hotels, Air/Hotel Packages, Charter Airfares
=============== Visit us at http://getawaynet.com ===============
---------------------------------------------------------------------
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
