473,216 Members | 1,289 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,216 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 2515
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
19
by: Westcoast Sheri | last post by:
To keep track of how many fruits my visitors buy, I use a mySQL database (2 columns: "fruit" and "quantity")....so can we make these following mySQL queries work somehow? (visitor buys 5...
8
by: Tony Clarke | last post by:
Hi, Just a quick question about performance with MySQL & PHP. If I had a table in a MySQL database with about 100,000 records in it and I need to find the last record is there a quick way to do...
1
by: Marcus | last post by:
Hello, quick question about MySQL storing NULL values... Say I have a textbox called $_POST and a variable $var. if(empty($_POST)) $var = NULL; else $var = $_POST; Disregarding...
51
by: w_curtis | last post by:
I'm an Access user, and I'm trying to learn MySQL and then PHP so I can make some web databases. But it just isn't clicking. I've followed some tutorials, and picked up a book, but just getting...
0
by: jy2003 | last post by:
Below is what I got by using MySQL's --log startup option. The original Java(with JDBC driver) program that creates the queries at runtime was coded by other people, and unfortunately, they are not...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
1
by: jlee | last post by:
I'm pretty much a newbie on mysql, and I need some help. I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4 (i386) on a server hosting an active website. The site's developer...
6
by: ojorus | last post by:
Hi! My company make several flash-based games, and I use php to communicate with mysql to provide highscore-lists. My problem is this: When I save a player's score in the mysql-table, I want to...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.