List:MySQL and PHP« Previous MessageNext Message »
From:<admin Date:February 7 2012 8:16pm
Subject:RE: Question about INNER JOIN
View as plain text  
HOLLY CRAP that is it.
That did exactly what I wanted to do.

THANK YOU SO MUCH I have been beating my head on this thing for a couple
hours now and I knew it was something stupid I was doing TY TY TY.
This even sped up the result return a good 1.5 seconds 






> -----Original Message-----
> From: Christopher Ingram [mailto:cbingram@stripped]
> Sent: Tuesday, February 07, 2012 3:07 PM
> To: admin@stripped
> Cc: php@stripped
> Subject: Re: Question about INNER JOIN
> 
> Seems like you could use a combination of LEFT JOIN and IFNULL.
> 
> The left join will bring back null for cost when the join fails and the
> IFNULL will let you replace it with your desired output.
> 
> Something like:
> 
> SELECT `items`.`*`,
> 
> IFNULL(`payscale_product`.`cost`, '0.00') AS cost
> 
> FROM
> 
> Items
> 
> LEFT JOIN payscale_products ON
> `payscale_products`.`id`=`items`.`product_id`
> 
> WHERE
> 
> `items`.`target`=$target AND customer_id=$customer
> 
> LIMIT 1
> 
> 
> Typed on my iPhone so please excuse any typos. Good luck!
> 
> Thanks,
> Chris
> 
> 
> 
> On Feb 7, 2012, at 2:52 PM, "admin@stripped" <
> admin@stripped> wrote:
> 
> Well let me explain the details a little better.
> 
> 
> 
> I am running through a table on criteria and then inner joining from
> another
> table where criteria from the first table matches.
> 
> I would like to be able to say if it doesn't match on the INNER JOIN
> then
> INNER JOIN on a default row.
> 
> Everything is being done on the MYSQL side nothing should come to PHP
> and
> there is no way to match a passed php variable because the information
> is
> determined in the matches row from the first table.
> 
> 
> 
> 
> 
> Example:
> 
> SELECT `items`.`*`,`payscale_product`.`cost`
> 
> FROM
> 
> Items
> 
> INNER JOIN payscale_products ON
> `payscale_products`.`id`=`items`.`product_id`
> 
> WHERE
> 
> `items`.`target`=$target AND customer_id=$customer
> 
> LIMIT 1
> 
> 
> 
> 
> 
> Some of the products have been set up for billing some have not so some
> of
> the products will not be in the payscale_products table until they have
> been
> setup.
> 
> I would return a payscale_products.cost as '0.00' if there was no match
> but
> If there is no match nothing returns so I can't set the value to 0
> 
> 
> 
> If there was a way to detect there was no return from the INNER join
> and
> then INNER JOIN on the default payscale by default.
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> 
> From: madavapeddi suman [mailto:suman.madavapeddi@stripped]
> Sent: Tuesday, February 07, 2012 2:34 PM
> To: admin@stripped
> Cc: php@stripped
> Subject: Re: Question about INNER JOIN
> 
> 
> 
> hi,
> 
> 
> 
>   You can out put the php variable that holds the SQL statement and
> take
> the SQL statement and execute it in the Database to see if the inner
> join
> works
> 
> 
> 
> /Suman
> 
> On Tue, Feb 7, 2012 at 12:27 PM, <admin@stripped> wrote:
> 
> I have a SQL statement that executes okay, but I noticed  the INNER
> JOIN
> fails because of a criteria match and it should.
> 
> 
> 
> Is there a way for me to detect the Failure of the inner join so I can
> change the criteria?
> 
> 
> 
> I was thinking of an IF statement but not sure how you detect if the
> INNER
> join has failed is there a result set?

Thread
Question about INNER JOINadmin7 Feb
  • Re: Question about INNER JOINmadavapeddi suman7 Feb
    • RE: Question about INNER JOINadmin7 Feb
      • Re: Question about INNER JOINChristopher Ingram7 Feb
        • RE: Question about INNER JOINadmin7 Feb