SEO

#1 (**permalink**) 02-28-2008, 10:25 AM

Hi,

I have the following query that runs and returns the correct results.

SELECT c.title AS categoryName, p.title, p.id
FROM dex_product_to_category ptc
JOIN dex_product p ON ( ptc.product_id = p.id )
JOIN dex_product_category c ON ( ptc.category_id = c.id )

categoryName title id
Chocolate Bars Snickers 1
Candy O Henry 2
Chocolate Bars O Henry 2

However, when I try to right join to a new table
"dex_product_sku_override" where there may or may not be a override I
always get a zero result set. I was under the impression that using a
right join would bring back a column even if it did not exists...
Where have I gone wrong?

Ideally I would like to return the results that DO NOT have a sku
override.

SELECT c.title AS categoryName, p.title, p.id, o.sku
FROM dex_product_to_category ptc
JOIN dex_product p ON ( ptc.product_id = p.id )
JOIN dex_product_category c ON ( ptc.category_id = c.id )

RIGHT JOIN dex_product_sku_override o (p.id = o.product_id)

Thanks for the help

#2 (**permalink**) 02-28-2008, 10:25 AM

blaine@worldweb.com wrote:
> Hi,
>
> I have the following query that runs and returns the correct results.
>
>
> SELECT c.title AS categoryName, p.title, p.id
> FROM dex_product_to_category ptc
> JOIN dex_product p ON ( ptc.product_id = p.id )
> JOIN dex_product_category c ON ( ptc.category_id = c.id )
>
>
> categoryName title id
> Chocolate Bars Snickers 1
> Candy O Henry 2
> Chocolate Bars O Henry 2
>
>
> However, when I try to right join to a new table
> "dex_product_sku_override" where there may or may not be a override I
> always get a zero result set. I was under the impression that using a
> right join would bring back a column even if it did not exists...
> Where have I gone wrong?
>
> Ideally I would like to return the results that DO NOT have a sku
> override.
>
> SELECT c.title AS categoryName, p.title, p.id, o.sku
> FROM dex_product_to_category ptc
> JOIN dex_product p ON ( ptc.product_id = p.id )
> JOIN dex_product_category c ON ( ptc.category_id = c.id )
>
> RIGHT JOIN dex_product_sku_override o (p.id = o.product_id)
>
> Thanks for the help

Try a LEFT JOIN

#3 (**permalink**) 02-28-2008, 10:25 AM

On Aug 10, 12:00 pm, "Paul Lautman" <paul.laut...@btinternet.com>
wrote:
> bla...@worldweb.com wrote:
> > Hi,
>
> > I have the following query that runs and returns the correct results.
>
> > SELECT c.title AS categoryName, p.title, p.id
> > FROM dex_product_to_category ptc
> > JOIN dex_product p ON ( ptc.product_id = p.id )
> > JOIN dex_product_category c ON ( ptc.category_id = c.id )
>
> > categoryName title id
> > Chocolate Bars Snickers 1
> > Candy O Henry 2
> > Chocolate Bars O Henry 2
>
> > However, when I try to right join to a new table
> > "dex_product_sku_override" where there may or may not be a override I
> > always get a zero result set. I was under the impression that using a
> > right join would bring back a column even if it did not exists...
> > Where have I gone wrong?
>
> > Ideally I would like to return the results that DO NOT have a sku
> > override.
>
> > SELECT c.title AS categoryName, p.title, p.id, o.sku
> > FROM dex_product_to_category ptc
> > JOIN dex_product p ON ( ptc.product_id = p.id )
> > JOIN dex_product_category c ON ( ptc.category_id = c.id )
>
> > RIGHT JOIN dex_product_sku_override o (p.id = o.product_id)
>
> > Thanks for the help
>
> Try a LEFT JOIN

Sorry I just noticed this as well.

This will now work.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

SEO

Right Join Help Please