<i>MySQL 4.0x, PHP 4.3.x, DMX2004 & AQW</i>

<b>Background:</b> I have one table for Users, one for UserAddress, one for Orders. I have a <i>Recordset</i> (rsUserOrder), which has two foreign keys (<i>billingID</i>, <i>shippingID</i> that reference the same table (UserAddress) just different ID's since they may have more than one address in their account. The <i>billingID</i> is required, so that's fine, <i>shippingID</i> may be NULL if no shipping is needed.

<b>Problem:</b> When I pull down a record that has a <i>NULL</i> for <i>shippingID</i>, then I get no record/data. I've tried different types of JOINs (All records from Orders, all records from UserAddress), but no good so far.

~ ~ ~ ~ ~ ~ ~
Chris Charlton <i>- DMXzone Manager</i>
<font size=1>[ Studio MX/MX2004 | PHP/ASP | SQL | XHTML/CSS | XML | Actionscript | Web Accessibility | MX Extensibility ]</font id=size1>

outer join may not work as you are joining on two keys.....you could try to create a view for each and then use an outer join for each....that should do it.

Regards

Vince Baker
<strong>DMX Zone Manager</strong>

[VBScript | ASP | HTML | CSS | SQL | Oracle | AS400 | ERP Logic | Hosting]

<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>outer join may not work as you are joining on two keys.....you could try to create a view for each and then use an outer join for each....that should do it.<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
Thanks Vince, but MySQL/DW don't do VIEWs. MySQL 5 has VIEWs, but not my 4.0x . <img src=../images/dmxzone/forum/icon_smile_sad.gif border=0 align=middle> *cry*

~ ~ ~ ~ ~ ~ ~
Chris Charlton <i>- DMXzone Manager</i>
<font size=1>[ Studio MX/MX2004 | PHP/ASP | SQL | XHTML/CSS | XML | Actionscript | Web Accessibility | MX Extensibility ]</font id=size1>

two LEFT OUTER JOINs will work fine

(you could also make the first one INNER, since billingID is always required)

<pre id=code><font face=courier size=2 id=code>select O.foo
, O.bar
, B.address as billingaddress
, S.address as shippingaddress
from orders as O
left outer
join useraddress as B
on O.billingID = B.id
left outer
join useraddress as S
on O.shippingID = S.id</font id=code></pre id=code>

rudy
r937.com/

The <b>LEFT OUTER JOIN</b>(s) were the ticket!!! <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle> Thanx!

~ ~ ~ ~ ~ ~ ~
Chris Charlton <i>- DMXzone Manager</i>
<font size=1>[ Studio MX/MX2004 | PHP/ASP | SQL | XHTML/CSS | XML | Actionscript | Web Accessibility | MX Extensibility ]</font id=size1>