MySQL ODBC Connector - Multi condition/table select

Author

Multi condition/table select

Andy Pieters

2005-04-24, 1:23 pm

--nextPart9697534.pJFBvX6PDz
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

Hi all

I am trying to figure out how to do this in one query.

Using MySQL 2.3.58

Tables (only relevant data shown)

Categories: id=3Dint 11 primary key
title =3D varchar
Products: id=3Dint 11 primary key
name=3Dvarchar
Lnk: catid=3Dint11 primary key
lnk=3Dint 11 (key: unique combo catid+lnk)

Id's for categories are between 20001 and 25000
id's for products are <20000

I want to select all products that do not have a link to category x in the=
=20
table Lnk.

Example

Categories:
id title
20001 Network
20002 Switches

Products
id name
1 10/100 Switch 5 port
2 10/100 Switch 8 port
3 10/100/1000 Switch 5 port

Lnk
catid lnk
20001 20002
20002 1

With this data, when using the category 20002, the query should return=20
products with id 2, and 3. If used with category 20001, it should return=20
products with id 1, 2, and 3

I was thinking on using three left joins but have been unable to make worki=
ng=20
code.

Can anybody make sense to this?

With kind regards

Andy

=2D-=20
Registered Linux User Number 379093

=2D-
Check out these few php utilities that I released
under the GPL2 and that are meant for use with a=20
php cli binary:
=20
http://www.vlaamse-kern.com/sas/
=2D-

=2D-

--nextPart9697534.pJFBvX6PDz
Content-Type: application/pgp-signature

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.6 (GNU/Linux)

iD8DBQBCa9jgP6e1Rq5I
UAkRAqwMAJ9LuS6bq/ FotjzQubBfikWxRXdXuA
CdGRnE
rYCUsmcw2u0VTM7Af8ZT
iZQ=
=fz0+
-----END PGP SIGNATURE-----

--nextPart9697534.pJFBvX6PDz--

mathias fatene

2005-04-24, 1:23 pm

Hi,
Don't you miss some relationnal definition in your table.
Personnaly, I suggest in lnk :

Lnk: catid=int11 primary key
prodid=int 11
The key being (caid,prodid).

And simplier (Normal form) :

Categories: id=int 11 primary key
title = varchar

Products: id=int 11 primary key
name=varchar
catid int 11

The query will then be evident.

Mathias

-----Original Message-----
From: Andy Pieters [mailto:andy@vlaamse
-kern.com]
Sent: dimanche 24 avril 2005 19:35
To: mysql@lists.mysql.com
Subject: Multi condition/table select

Hi all

I am trying to figure out how to do this in one query.

Using MySQL 2.3.58

Tables (only relevant data shown)

Categories: id=int 11 primary key
title = varchar
Products: id=int 11 primary key
name=varchar
Lnk: catid=int11 primary key
lnk=int 11 (key: unique combo catid+lnk)

Id's for categories are between 20001 and 25000
id's for products are <20000

I want to select all products that do not have a link to category x in
the
table Lnk.

Example

Categories:
id title
20001 Network
20002 Switches

Products
id name
1 10/100 Switch 5 port
2 10/100 Switch 8 port
3 10/100/1000 Switch 5 port

Lnk
catid lnk
20001 20002
20002 1

With this data, when using the category 20002, the query should return
products with id 2, and 3. If used with category 20001, it should
return
products with id 1, 2, and 3

I was thinking on using three left joins but have been unable to make
working
code.

Can anybody make sense to this?

With kind regards

Andy

--
Registered Linux User Number 379093

--
Check out these few php utilities that I released
under the GPL2 and that are meant for use with a
php cli binary:

http://www.vlaamse-kern.com/sas/
--

--

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links