List:MySQL and PHP« Previous MessageNext Message »
From:Christopher Ingram Date:February 7 2012 8:07pm
Subject:Re: Question about INNER JOIN
View as plain text  
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