Ok, looked closer...
Really cool stuff here..
I've changed al my ID fields to INDEX and not Primary
Anyhoo...
The resutls I'm getting from the new query (see below) are not
returning items that ARe in stock and the required gender...
I'm investigating.. but if you can spot it, you oculd give me ahint? ;-p
===================
SELECT distinct
products.id
, products.title
, brands.title as brandTitle
, products.thumb2
, products.price
, products.salePrice
, products.saleStatus
FROM products
inner
join stock
on stock.productId = products.id
and stock.count > 0
inner
join prodcatmatch
on prodcatmatch.product = products.id
inner
join categories as parent
on parent.id = stock.size
and parent.gender = '1'
and parent.type = '2'
inner
join categories as child
on child.id = prodcatmatch.category
and child.type = '5'
inner
join brands
on brands.id = products.brand
order
by products.id
===================
On 20/10/06, Tris <[EMAIL PROTECTED]> wrote:
this is awesome..
I've always wanted to learn JOIN(s)
I'll take a look
cheers!!!
On 20/10/06, r937 <[EMAIL PROTECTED]> wrote:
>
> three things:
>
> 1. your query is incomplete, some join conditions are missing
> 2. your tables are missing indexes on the join columns
> 3. LIMIT makes no sense without ORDER BY
>
> 1 is most serious, as you will get extraneous and incorrect results
> (and i suspect this is why you plunked DISTINCT into the query)
>
> 2 means your query will run for hours instead of seconds
>
> 3 is a logic problem
>
>
> here's your query, written with JOIN syntax (recommended)
>
> notice where the join conditions are missing
>
>
> SELECT distinct
> products.id
> , products.title
> , brands.title as brandTitle
> , products.thumb2
> , products.price
> , products.salePrice
> , products.saleStatus
> FROM products
> inner
> join stock
> on stock.productId = products.id
> and stock.count > 0
> inner
> join prodcatmatch
> on prodcatmatch.????? = ???????.?????
> inner
> join categories as parent
> on parent.id = stock.size
> and parent.gender = '1'
> and parent.type = '2'
> inner
> join categories as child
> on child.????? = ???????.?????
> inner
> join brands
> on brands.id = products.brand
> order
> by ?????? LIMIT 0,24
>
>
> rudy
> http://r937.com/
>
> ____ � The WDVL Discussion List from WDVL.COM � ____
> To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
> use the web interface http://e-newsletters.internet.com/discussionlists.html/
> Send Your Posts To: [email protected]
> To change subscription settings, add a password or view the web interface:
> http://intm-dl.sparklist.com/read/?forum=wdvltalk
>
> ________________ http://www.wdvl.com _______________________
>
> You are currently subscribed to wdvltalk as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to [EMAIL PROTECTED]
> To unsubscribe via postal mail, please contact us at:
> Jupitermedia Corp.
> Attn: Discussion List Management
> 475 Park Avenue South
> New York, NY 10016
>
> Please include the email address which you have been contacted with.
>
____ � The WDVL Discussion List from WDVL.COM � ____
To Join wdvltalk, Send An Email To: mailto:[EMAIL PROTECTED] or
use the web interface http://e-newsletters.internet.com/discussionlists.html/
Send Your Posts To: [email protected]
To change subscription settings, add a password or view the web interface:
http://intm-dl.sparklist.com/read/?forum=wdvltalk
________________ http://www.wdvl.com _______________________
You are currently subscribed to $subst('List.Name') as:
$subst('Recip.EmailAddr')
To unsubscribe send a blank email to $subst('Email.UnSub')
To unsubscribe via postal mail, please contact us at:
Jupitermedia Corp.
Attn: Discussion List Management
475 Park Avenue South
New York, NY 10016
Please include the email address which you have been contacted with.
