At 11:57 -0400 5/9/02, Luc Foisy wrote: >It may not make any sense to you, but it makes perfect sense to me
Okay. Then it would seem that either: - I understand what you want to do, in which case you can't do it, because you're using SQL variables in a self-contradictory way. - I don't understand what you want to do, which is perfectly possible, since even the broken up queries below are pretty impenetrably formatted. > >Maybe it would look better to you if written as so: > >SELECT @AddressJoinID := IF (CUSTOMER.ID_ADDRESS_BILLTO > 0, >CUSTOMER.ID_ADDRESS_BILLTO, CUSTOMER.ID_ADDRESS) AS 'Junk' FROM >INVOICEHEADER LEFT JOIN CUSTOMER ON INVOICEHEADER.ID_CUSTOMER = CUSTOMER.ID >WHERE INVOICEHEADER.ID = [param:Invoice ID] > >SELECT INVOICEHEADER.ID AS 'ID_INVOICEHEADER', >DATE_FORMAT(INVOICEHEADER.Date,'%Y/%m/%d') AS 'Invoice_Date', >INVOICEHEADER.ID_CUSTOMER AS 'ID_CUSTOMER', INVOICEHEADER.SubTotal, >INVOICEHEADER.Tax1Total AS 'Tax1Total', INVOICEHEADER.GrandTotal AS >'GrandTotal', CUSTOMER.Company AS 'Cust_Company', CUSTOMER.*, COMPANY.*, >TRIM(CONCAT(IF(ADDRESS.UnitNumber IS NULL,'',ADDRESS.UnitNumber),' >',ADDRESS.CivicNumber,' ',STREET.Name,' ',IF(STREETTYPE.Abv IS >NULL,'',STREETTYPE.Abv))) AS 'Address_1', ADDRESS.Line2 AS 'Address_2', >CONCAT(CITY.Name,', ',PROVINCE.Name) AS 'Address_3', ADDRESS.PostalCode AS >'Cust_PostalCode' FROM INVOICEHEADER LEFT JOIN CUSTOMER ON >INVOICEHEADER.ID_CUSTOMER = CUSTOMER.ID LEFT JOIN ADDRESS ON ADDRESS.ID = >@AddressJoinID LEFT JOIN STREET ON ADDRESS.ID_STREET = STREET.ID LEFT JOIN >STREETTYPE ON ADDRESS.ID_STREETTYPE = STREETTYPE.ID LEFT JOIN CITY ON >ADDRESS.ID_CITY = CITY.ID LEFT JOIN PROVINCE ON ADDRESS.ID_PROVINCE = >PROVINCE.ID LEFT JOIN COUNTRY ON ADDRESS.ID_COUNTRY = COUNTRY.ID LEFT JOIN >COMPANY ON CUSTOMER.ID_COMPANY = COMPANY.ID WHERE INVOICEHEADER.ID = >[param:Invoice ID] > >Can't use an IF() on the join, but we want to join conditionally (customer >has two address fields, if one is blank, then use the other one) >( the query does not print out anything directly, values are grabbed from >the result set and formatted into a printable form ) > >Since all the joins in the first query exist in the second query, I should >be able to create and use the same variable in the second query ( or the >documentation needs to be revamped stating that you can't use variables in >the same select statement at all) You *can* use them in the same statement, but as the manual states, if you access a variable that is set in the same statement, the value you access for one row is the value that was assigned from the previous row. And this does not seem to fit what you're trying to accomplish. --------------------------------------------------------------------- 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
