SEO

hi to all!

This is query I used before we moved to mysql4 based dedicated server:

SELECT ".$tn.".reg_id, b.*, sh.*
FROM ".$tn.", bill_info as b
LEFT JOIN shipp_info as sh ON (b.bill_id=sh.bill_id)
WHERE b.reg_id = ".$tn.".reg_id
AND b.table_name = '".$tn."'
AND ".$tn.".registration_status = '".$_SESSION['order_status']."'
ORDER BY ".$tn.".reg_id DESC

($tn is name for selected table)

Now, after I moved to mysql5 based server I'm getting error:
ERROR: Unknown column 'b.bill_id' in on clause
?!?!?

CREATE TABLE `bill_info` (
`bill_id` int(10) unsigned NOT NULL auto_increment,
`ref_no` varchar(14) default NULL,
`reg_id` int(5) unsigned default NULL,
`cc_id` int(5) unsigned default NULL,
`table_name` varchar(50) NOT NULL default '',
`bill_name` varchar(100) NOT NULL default '',
`bill_address_1` varchar(100) NOT NULL default '',
`bill_address_2` varchar(50) default NULL,
`bill_city` varchar(50) NOT NULL default '',
`bill_state` char(2) NOT NULL default '',
`bill_zip` int(5) unsigned NOT NULL default '0',
`bill_phone` varchar(20) default NULL,
`bill_email` varchar(50) NOT NULL default '',
`shipg_eq_bill` int(1) unsigned default NULL,
`bill_status` varchar(25) default NULL,
`CC_alerted` int(1) default '0',
UNIQUE KEY `bill_id` (`bill_id`),
UNIQUE KEY `ref_no` (`ref_no`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=132 ;


CREATE TABLE `shipp_info` (
`ship_id` int(10) unsigned NOT NULL auto_increment,
`bill_id` int(5) unsigned default NULL,
`ship_name` varchar(100) NOT NULL default '',
`ship_address_1` varchar(100) NOT NULL default '',
`ship_address_2` varchar(50) default NULL,
`ship_city` varchar(50) NOT NULL default '',
`ship_state` char(2) NOT NULL default '',
`ship_zip` int(5) unsigned NOT NULL default '0',
`ship_phone` varchar(20) default NULL,
`ship_email` varchar(50) NOT NULL default '',
UNIQUE KEY `ship_id` (`ship_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;


Thanks for any help!

-afan

afan@afan.net wrote:
> FROM ".$tn.", bill_info as b
> LEFT JOIN shipp_info as sh ON (b.bill_id=sh.bill_id

That would be the usage of multiple from's combined with a left join.
Unfortunately with mysql 5 you can't do this. You're going to have to
do bill_info as a left/right/inner join instead.

> afan@afan.net wrote:
>> FROM ".$tn.", bill_info as b
>> LEFT JOIN shipp_info as sh ON (b.bill_id=sh.bill_id
>
> That would be the usage of multiple from's combined with a left join.
> Unfortunately with mysql 5 you can't do this. You're going to have to
> do bill_info as a left/right/inner join instead.
>
Could you please elaborate a little bit more, please?

Thanks.

View the documentation here:
http://dev.mysql.com/doc/refman/5.0/en/join.html

You could write your statement as

SELECT
Field1, field2
FROM
Table1
LEFT JOIN Table2
LEFT JOIN Table3

OR

SELECT
Field1, field2
FROM
(Table1, Table2)
LEFT JOIN Table3

Etc etc...

But you cannot do:

SELECT
Field1, field2
FROM
Table1, Table2
LEFT JOIN Table3

-----Original Message-----
From: afan@afan.net [mailto:afan@afan.net]
Sent: Tuesday, January 16, 2007 11:06 AM
To: Chris White
Cc: mysql@lists.mysql.com
Subject: Re: query taht works on mysql4 but doesn't on mysql5?

> afan@afan.net wrote:
>> FROM ".$tn.", bill_info as b
>> LEFT JOIN shipp_info as sh ON (b.bill_id=sh.bill_id
>
> That would be the usage of multiple from's combined with a left join.
> Unfortunately with mysql 5 you can't do this. You're going to have to
> do bill_info as a left/right/inner join instead.
>
Could you please elaborate a little bit more, please?

Thanks.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/mysql?unsub=j...edventures.com

I have not tried, but i think this is what he meant:
FROM
tn t
INNER JOIN bill_info b ON (b.id_b = t.id_t)
LEFT JOIN shipp_info sh ON (b.bill_id=sh.bill_id)

HTH,

On 1/16/07, Chris White <chriswhite@interfuel.com> wrote:
>
>
>
> afan@afan.net wrote:
> > FROM ".$tn.", bill_info as b
> > LEFT JOIN shipp_info as sh ON (b.bill_id=sh.bill_id
>
> That would be the usage of multiple from's combined with a left join.
> Unfortunately with mysql 5 you can't do this. You're going to have to
> do bill_info as a left/right/inner join instead.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=ran256@gmail.com
>
>

Thanks Johnatan.
After I put tables in brackets - everything worked fine!


And thank to everybody else.

-afan

> View the documentation here:
> http://dev.mysql.com/doc/refman/5.0/en/join.html
>
> You could write your statement as
>
> SELECT
> Field1, field2
> FROM
> Table1
> LEFT JOIN Table2
> LEFT JOIN Table3
>
> OR
>
> SELECT
> Field1, field2
> FROM
> (Table1, Table2)
> LEFT JOIN Table3
>
> Etc etc...
>
> But you cannot do:
>
> SELECT
> Field1, field2
> FROM
> Table1, Table2
> LEFT JOIN Table3
>
> -----Original Message-----
> From: afan@afan.net [mailto:afan@afan.net]
> Sent: Tuesday, January 16, 2007 11:06 AM
> To: Chris White
> Cc: mysql@lists.mysql.com
> Subject: Re: query taht works on mysql4 but doesn't on mysql5?
>
>> afan@afan.net wrote:
>>> FROM ".$tn.", bill_info as b
>>> LEFT JOIN shipp_info as sh ON (b.bill_id=sh.bill_id
>>
>> That would be the usage of multiple from's combined with a left join.
>> Unfortunately with mysql 5 you can't do this. You're going to have to
>> do bill_info as a left/right/inner join instead.
>>
> Could you please elaborate a little bit more, please?
>
> Thanks.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=j...edventures.com
>
>