[SQL] need help on a mysql query

[SBmx]

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.countr y 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

[Erwin Moller]

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.countr y 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

[SBmx]

Re: need help on a mysql query

On Nov 21, 5:05 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:

>
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=$intCou ntry)

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

thanks a bunch!

somBoy MX

[Erwin Moller]

Re: need help on a mysql query

SBmx wrote:

On Nov 21, 5:05 pm, Erwin Moller
<Since_humans_r ead_this_I_am_s pammed_too_m... @spamyourself.c omwrote:
>

>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=$intCou ntry)
>

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

[Jerry Stuckle]

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.countr y 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@attgl obal.net
=============== ===