Re: [SQL] need help on a mysql query

SBmx wrote:
> hello

Hi,

Not excactly a PHP question, but here we go.

>
> I need help building a SELECT query... well, two queries actually
>
> i have three tables:
>
> products / fields id, name, type, country
> producttypes / fields: id, name
> countries / fields: id, name
>
> as you probably guessed, products.type stores a value from
> producttypes.id and products.country stores a value from countries.id

Nope, I wouldn't have guessed that.
I always give columns the same name if I create a Foreign Key constraint.
But clear now. :-)

>
> now, in my first query i need to select all producttypes that have
> more than 0 products belonging to that category.

Try using GROUP BY and use HAVING

>
> in a subsequent query i'll have to select all countries that have more
> than 0 products which belong to a given producttype.

Same: GROUP BY and HAVING.

>
> Thanks for taking the time to help me solve this one..

You'll have to solve it yourself, but you know what to look for now. ;-)

>
> somaBoy MX
>

Good luck.

Regards,
Erwin Moller

[SQL] need help on a mysql query

hello

I need help building a SELECT query... well, two queries actually

i have three tables:

products / fields id, name, type, country
producttypes / fields: id, name
countries / fields: id, name

as you probably guessed, products.type stores a value from
producttypes.id and products.country stores a value from countries.id

now, in my first query i need to select all producttypes that have
more than 0 products belonging to that category.

in a subsequent query i'll have to select all countries that have more
than 0 products which belong to a given producttype.

Thanks for taking the time to help me solve this one..

somaBoy MX

Re: need help on a mysql query

On Nov 21, 5:05 pm, Erwin Moller
wrote:

>
> Try using GROUP BY and use HAVING


actually, i looked into subqueries and came up with this:
SELECT name, id FROM countries WHERE id IN (SELECT country FROM
products)
SELECT name, id FROM producttypes WHERE id IN (SELECT type FROM
products WHERE country=$intCountry)

seems to work, but your solution would probably be more backwards
compatible with older mysql versions.

thanks a bunch!

somBoy MX

Re: need help on a mysql query

SBmx wrote:
> On Nov 21, 5:05 pm, Erwin Moller
> wrote:
>
>> Try using GROUP BY and use HAVING
>
>
> actually, i looked into subqueries and came up with this:
> SELECT name, id FROM countries WHERE id IN (SELECT country FROM
> products)
> SELECT name, id FROM producttypes WHERE id IN (SELECT type FROM
> products WHERE country=$intCountry)
>

Yes, that works in this situation.
But what will you do when you need all producttypes that have more than,
say, 2 or 3 products belonging to a certain category?

Have a look at GROUP BY and HAVING.
They are designed for this. ;-)

> seems to work, but your solution would probably be more backwards
> compatible with older mysql versions.

I don't know much about mySQL.
Last time I looked into it I ran away screaming (back to PostgreSQL).
I prefer PostgreSQL, as superior database compared to mySQL in my humble
opinion. (MySQL is a little faster, but that is all)

But I expect that mySQL can handle both approaches (subselect and GROUP BY).


>
> thanks a bunch!

You are welcome.
Good luck

Regards,
Erwin Moller

>
> somBoy MX

Re: [SQL] need help on a mysql query

SBmx wrote:
> hello
>
> I need help building a SELECT query... well, two queries actually
>
> i have three tables:
>
> products / fields id, name, type, country
> producttypes / fields: id, name
> countries / fields: id, name
>
> as you probably guessed, products.type stores a value from
> producttypes.id and products.country stores a value from countries.id
>
> now, in my first query i need to select all producttypes that have
> more than 0 products belonging to that category.
>
> in a subsequent query i'll have to select all countries that have more
> than 0 products which belong to a given producttype.
>
> Thanks for taking the time to help me solve this one..
>
> somaBoy MX
>
>

You can get more help for MySQL questions in a MySQL newsgroup such as
comp.databases.mysql.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================