Doh! Join logic fails to stick in head....

Cinquecento

I don't know what it is about this, but I just can't seem to get my head around it - and I KNOW it's simple!!!!!

TABLE 1 (categories)
catName catGroup catCode

TABLE 2 (products)
prodName prodCode

If the user wants to see all the products in a particular catGroup, I need to get all the catCodes that have the same catGroup, and the select all the prodNames WHERE catCode = prodCode....

So why oh why can't I think about what the query should say for more than 10 seconds... maybe I've been working too hard lately?

Please, throw me a scrap of knowledge...

Ian

notionlogic

But you're almost there...😃

In it's simplest form....

select distinct prodName from products, categories
where catCode = prodCode

although you may not need the "distinct" bit if prodName is unique!

Hope this helps

Cinquecento

OK - but the catCode(s) are to have come from Table2 where catGroup matches a variable - so there is a join at work here?

I guess this is the query - but the syntax is all wrong...

SELECT prodName from Table1 WHERE ProdCode = (SELECT catCode from Table2 WHERE catGroup = '$selectedGroup')

fandelem

In another semi-related topic, let's say you had a 'price' field in the second table, how would you be able to total all the prices for a particular ..author, let's say (ie. total price for all books author has published).. in the same query?

notionlogic

Originally posted by Cinquecento
OK - but the catCode(s) are to have come from Table2 where catGroup matches a variable - so there is a join at work here?

I guess this is the query - but the syntax is all wrong...

SELECT prodName from Table1 WHERE ProdCode = (SELECT catCode from Table2 WHERE catGroup = '$selectedGroup')

I can see how you're thinking but you've taken it too far plus "sub-selects" don't work in MySQL!! Try this instead...

SELECT prodName from Table1, Table2
WHERE ProdCode = catCode
and catGroup = '$selectedGroup'

and for my next trick, the other question posted can be answered using the sum() function like this:

SELECT prodName, sum(price) from Table1, Table2
WHERE ProdCode = catCode
and catGroup = '$selectedGroup'
group by prodName

Note that the "group by" clause is all important! Does that help?

Cinquecento

Yup - that works a treat!

You are truly a codeAngel!

Ian

Cinquecento

Just so as I don't have to ask dumb questions again - could you expand on exactly HOW this query operates to deliver the result?

I've done this stuff before, but I guess those particular braincells have been recycled for Flash Actionscripting or something....

thanks