471,319 Members | 1,587 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,319 software developers and data experts.

Mysql select question

Hi there,

I have a site with products on it.
The site has a mysql backend.

All products belong to certain series (table series).
There can be up to 4 different products (table products)
(categories 1-4) in 1 series.

Each product has a defined 'series ID' which tells us
what series the product belongs to.

If i'm viewing all products with cat=1, how do i know if
there is a cat=2 and/or cat=3 and/or cat=4 with the same
series id. Can this be done in 1 query?

Thanks!

Jul 17 '05 #1
23 2282
anyone?

Jul 17 '05 #2
*please* anyone?

Jul 17 '05 #3
frizzle wrote:
*please* anyone?


The answer is "probably".

I really can't say much more based on the information you provided. Perhaps
you should ask whoever wrote the mySQL backend.

--
phil [dot] ronan @ virgin [dot] net
http://vzone.virgin.net/phil.ronan/
Jul 17 '05 #4
ph********@hotmail.com wrote:
Hi there,

I have a site with products on it.
The site has a mysql backend.

All products belong to certain series (table series).
There can be up to 4 different products (table products)
(categories 1-4) in 1 series.

Each product has a defined 'series ID' which tells us
what series the product belongs to.

If i'm viewing all products with cat=1, how do i know if
there is a cat=2 and/or cat=3 and/or cat=4 with the same
series id. Can this be done in 1 query?

Thanks!


left join
Jul 17 '05 #5
I tried to write/am writing the mySQL backend....
So for now it''s still quite flexible..

Any suggestions?

Jul 17 '05 #6
By the way, also tha database is only loaded with test-data for sofar,
so if anyone out there could help me ....

Jul 17 '05 #7
Tim
By the way, also tha database is only loaded with test-data for sofar
so if anyone out there could help me ...


I would take a look at the left join and the inner join options.
Depending on the coding method, version of mysql, the desired workin
method, and the exact use will determine which one you want to use

--
Ti
-----------------------------------------------------------------------
Tim's Profile: http://www.hjyllc.com/forums/member.php?userid=
View this thread: http://www.hjyllc.com/forums/showthread.php?t=1669

Jul 17 '05 #8
Allrighty Tim, i'll have a look at that.
Thanks for the effort!

Jul 17 '05 #9
On 28 Feb 2005 13:36:40 -0800, ph********@hotmail.com wrote:
I have a site with products on it.
The site has a mysql backend.

All products belong to certain series (table series).
There can be up to 4 different products (table products)
(categories 1-4) in 1 series.

Each product has a defined 'series ID' which tells us
what series the product belongs to.

If i'm viewing all products with cat=1, how do i know if
there is a cat=2 and/or cat=3 and/or cat=4 with the same
series id. Can this be done in 1 query?


Yes, probably. If you post a minimal runnable set of SQL statements to create
your tables and set up a few rows of representative sample data, so that it's
just a copy&paste job to set it up locally, you'll have a much better chance of
getting a better answer.

Sounds like a 4-way (left) outer join.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #10
I'm sorry Andy,

i don't quite get what you mean.
As you have noticed i am not that skilled yet in mySQL/php.
Please explain a little bit more, that would be great..

Thanks!

Jul 17 '05 #11
On 3 Mar 2005 13:34:27 -0800, "frizzle" <ph********@hotmail.com> wrote:
I'm sorry Andy,

i don't quite get what you mean.
As you have noticed i am not that skilled yet in mySQL/php.
Please explain a little bit more, that would be great..


Post the definitions of your tables, and I can try and post SQL to do what you
want. What are your column names? What data do you have? If you post some
concrete details of your tables then I won't have to make up tables _and_ make
up the SQL - if you give a bit of help, it's easier to give help back, if you
see what I mean.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #12
Ok, now i understand:

My site is about furniture:
Products can be the following:
- footstools (cat=1),
- chairs (cat=2),
- sofas (cat=3),
- diningchairs (cat=4).

There are two tables involved:
1st: Series
Series-table contents:
id (unique id)
active (can be '1' or '0', only active series
can be viewed)
name (name of the series)

2nd: Models
Models-table contents:
id (unique id)
active (can be '1' or '0', only active items
in active series can be viewed)
series_id (what series does the item belong to)
cat_id (is it a footstool, a chair, etc. ?)
size (measurements of the item)

What i need is e.g. when i'm viewing e.g. the chair (cat=2), to know if

there is a footstool, sofa etc. in the same series (with the same
series_id) with
series set active, and products set active....

Hope this clears out a few things a little bit.

The query i have so far is the following:

$getmodel = mysql_query("
SELECT s.name, m.id, m.size
FROM series s, models m
WHERE m.cat=$cat
AND m.id=$id
AND m.series_id=s.series_id
AND s.active=1
AND m.active=1
LIMIT 1")or die("bad query");

Thanks man!

Jul 17 '05 #13
On 3 Mar 2005 14:13:54 -0800, "frizzle" <ph********@hotmail.com> wrote:
Ok, now i understand:

My site is about furniture:
Products can be the following:
- footstools (cat=1),
- chairs (cat=2),
- sofas (cat=3),
- diningchairs (cat=4).

There are two tables involved:
1st: Series
Series-table contents:
id (unique id)
active (can be '1' or '0', only active series
can be viewed)
name (name of the series)

2nd: Models
Models-table contents:
id (unique id)
active (can be '1' or '0', only active items
in active series can be viewed)
series_id (what series does the item belong to)
cat_id (is it a footstool, a chair, etc. ?)
size (measurements of the item)

What i need is e.g. when i'm viewing e.g. the chair (cat=2), to know if

there is a footstool, sofa etc. in the same series (with the same
series_id) with
series set active, and products set active....

Hope this clears out a few things a little bit.

The query i have so far is the following:

$getmodel = mysql_query("
SELECT s.name, m.id, m.size
FROM series s, models m
WHERE m.cat=$cat
AND m.id=$id
AND m.series_id=s.series_id
AND s.active=1
AND m.active=1
LIMIT 1")or die("bad query");


Hm - you may be nearly there, but need another join - i.e. first query for the
chair, then join to all the other products in the same series. Untested SQL:

SELECT s.name,
m2.id,
m2.size
FROM models m -- one row for the first product (see WHERE clause at end)
JOIN series s -- find the associated series
ON (m.series_id = s.series_id AND s.active = 1)
JOIN models m2 -- multiple rows for the other products in the series
ON (m2.series_id = s.series_id
AND m2.id != m.id
AND m2.active = 1)
WHERE m.cat = $cat
AND m.id = $id
AND m.active = 1

Is that anywhere close?

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #14
Wow, :|

First of all wow! Thanks for helping, and wow, what a query...
I'm quite new at this, and this really looks complicated.

But it was too good to be true... I cannot get it to work, and
i don't know where to look.. I tried a few things which could possibly
somehow make a little bit sense, but they didn't :(

I get the following error notice:
You have an error in your SQL syntax near 'FROM models m JOIN series s
ON (m.series_id = s.series_id AND s.'

Please help me, i really don't know where to fix this.

(btw, I found it hard to find some good join tuts also)

Jul 17 '05 #15
On 5 Mar 2005 01:44:11 -0800, "frizzle" <ph********@hotmail.com> wrote:
But it was too good to be true... I cannot get it to work, and
i don't know where to look.. I tried a few things which could possibly
somehow make a little bit sense, but they didn't :(

I get the following error notice:
You have an error in your SQL syntax near 'FROM models m JOIN series s
ON (m.series_id = s.series_id AND s.'

Please help me, i really don't know where to fix this.

(btw, I found it hard to find some good join tuts also)


Well, I put together some tables based on what you said and ran it, and with a
couple of tweaks it at least runs. I'm still not 100% clear on your data as you
haven't posted any actual examples so it might not do the right thing.

mysql> desc series;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | | PRI | 0 | |
| active | int(10) unsigned | | | 0 | |
| name | varchar(24) | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

mysql> desc models;
+-----------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+------------------+------+-----+---------+-------+
| id | int(10) unsigned | | PRI | 0 | |
| active | int(10) unsigned | | | 0 | |
| series_id | int(10) unsigned | | | 0 | |
| cat_id | int(10) unsigned | | | 0 | |
| size | varchar(24) | YES | | NULL | |
+-----------+------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql> insert into series values (1, 1, 'series 1');
Query OK, 1 row affected (0.03 sec)

mysql> insert into models values (1, 1, 1, 1, 'big');
Query OK, 1 row affected (0.02 sec)

mysql> insert into models values (2, 1, 1, 2, 'small');
Query OK, 1 row affected (0.01 sec)

mysql> insert into models values (3, 1, 1, 3, 'medium');
Query OK, 1 row affected (0.00 sec)

mysql> select * from series;
+----+--------+----------+
| id | active | name |
+----+--------+----------+
| 1 | 1 | series 1 |
+----+--------+----------+
1 row in set (0.00 sec)

mysql> select * from models;
+----+--------+-----------+--------+--------+
| id | active | series_id | cat_id | size |
+----+--------+-----------+--------+--------+
| 1 | 1 | 1 | 1 | big |
| 2 | 1 | 1 | 2 | small |
| 3 | 1 | 1 | 3 | medium |
+----+--------+-----------+--------+--------+
3 rows in set (0.00 sec)

mysql> SELECT s.name,
-> m2.id,
-> m2.size
-> FROM models m -- one row for the first product (see WHERE clause at
end)
-> JOIN series s -- find the associated series
-> ON (m.series_id = s.id AND s.active = 1)
-> JOIN models m2 -- multiple rows for the other products in the series
-> ON (m2.series_id = s.id
-> AND m2.id != m.id
-> AND m2.active = 1)
-> WHERE m.cat_id = 1
-> AND m.id = 1
-> AND m.active = 1;
+----------+----+--------+
| name | id | size |
+----------+----+--------+
| series 1 | 2 | small |
| series 1 | 3 | medium |
+----------+----+--------+
2 rows in set (0.00 sec)

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #16
Wow, first of all thousands of thanks for all that work!

But, weird enough, i tried it with my current tables, and it didn't
work, then i built
your examples, but that also didn't work.. :(

The following query:

SELECT s.name, m2.id, m2.size
FROM models m
JOIN series s ON ( m.series_id = s.id
AND s.active =1 )
JOIN models m2 ON ( m2.series_id = s.id
AND m2.id != m.id
AND m2.active =1 )
WHERE m.cat_id =1
AND m.id =1
AND m.active =1

Gives me the following error:
#1064 - You have an error in your SQL syntax near 'ON ( m.series_id =
s.id
AND s.active =1 )
JOIN models m2 ON ( m2.series_id = ' at line 3

I know that you've helped me a lot already, but please hgelp me through
this problem...

Thanks man!! (If there's anything i can help with, let me know..)

Thanks!

Frizzle

Jul 17 '05 #17
?

Jul 17 '05 #18
On 8 Mar 2005 02:09:11 -0800, "frizzle" <ph********@hotmail.com> wrote:
?


!

Don't remove all the context when you reply (just because that's what the
Google Groups Abomination encourages doesn't make it the right way to post to
Usenet).

Also have some patience, yes I've not replied in the last 24 hours, but that's
not surprising since I'm in another timezone and I've been at work. Sheesh.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #19
On 7 Mar 2005 02:54:38 -0800, "frizzle" <ph********@hotmail.com> wrote:
But, weird enough, i tried it with my current tables, and it didn't
work, then i built
your examples, but that also didn't work.. :(

The following query:

SELECT s.name, m2.id, m2.size
FROM models m
JOIN series s ON ( m.series_id = s.id
AND s.active =1 )
JOIN models m2 ON ( m2.series_id = s.id
AND m2.id != m.id
AND m2.active =1 )
WHERE m.cat_id =1
AND m.id =1
AND m.active =1

Gives me the following error:
Since it works on the version of MySQL I use (4.1), you are clearly on a
different version. Which version?

Did you check the manual? It's at http://dev.mysql.com/doc/mysql/en/index.html
and is searchable.
#1064 - You have an error in your SQL syntax near 'ON ( m.series_id =
s.id
AND s.active =1 )
JOIN models m2 ON ( m2.series_id = ' at line 3


OK, so from the error it's complaining about the "ON" clause. This is
immediately after a JOIN, so perhaps it's that. So put "JOIN" in the search box
in the manual. The first hit is section 13.1.7.1. JOIN Syntax:

http://dev.mysql.com/doc/mysql/en/join.html

Read through that. It points out:

"Note that INNER JOIN syntax allows a join_condition only from MySQL 3.23.17
on. The same is true for JOIN and CROSS JOIN only as of MySQL 4.0.11."

So, you're using either MySQL 3.23 or a version of 4.0 earlier than 4.0.11 if
it doesn't accept a join condition for JOINs.

Replace "JOIN" with "INNER JOIN" in the SQL above. If that doesn't work you're
on some extremely old version of MySQL and should probably either upgrade or
abandon all hope.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #20
My sincere apologies Andy,

I didn't mean to look impatient or anything else/worse..

I really appreciate your help. I'm quite new to PHP, and especially
to mySQL, so all the help i get is welcome, and i wouldn't want to ruin
it by appearing impatient....

Anyway, i've checked my phpinfo() and i read that my php-version is
PHP Version 4.3.2, so that should be no problem...

Thanks again for helping me out. I'll try these new things tomorrow.
First i have to get some sleep... zzzzzz

Frizzle

Jul 17 '05 #21
Too bad, tried the inner join thing, raed the mySQL docs,
but can't figure it out, it still gives me the same error.... :( :

You have an error in your SQL syntax near 'ON (m.series_id=s.id AND
s.active=1) JOIN models m2 ON ('

I even tried to add quotes to the '1' etc. but nothing seems to work...

Frizzle

Jul 17 '05 #22
On 9 Mar 2005 04:47:36 -0800, "frizzle" <ph********@hotmail.com> wrote:
Too bad, tried the inner join thing, raed the mySQL docs,
but can't figure it out, it still gives me the same error.... :( :

You have an error in your SQL syntax near 'ON (m.series_id=s.id AND
s.active=1) JOIN models m2 ON ('


The fragment of SQL from the error message shows that you haven't actually
changed the JOINs to INNER JOINs.

--
Andy Hassall / <an**@andyh.co.uk> / <http://www.andyh.co.uk>
<http://www.andyhsoftware.co.uk/space> Space: disk usage analysis tool
Jul 17 '05 #23
Indeed, not in the error i pasted, but i've tried it actually, and it
gave me exactly the same error...

Jul 17 '05 #24

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Xenophobe | last post: by
19 posts views Thread by Westcoast Sheri | last post: by
8 posts views Thread by Tony Clarke | last post: by
1 post views Thread by Marcus | last post: by
51 posts views Thread by w_curtis | last post: by
reply views Thread by jy2003 | last post: by
reply views Thread by Mike Chirico | last post: by
6 posts views Thread by ojorus | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.