I want to have two tables in mysql with a relation 1-n between them Do I have to build a table for cross referening the others and treat it by code, or there is any sql property to treat relations? Thanks
Advertising
Antonio Bernabei ----- Original Message ----- From: "Srinivasan Ramakrishnan" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, May 29, 2002 9:06 AM Subject: [PHP-DB] Nested SQL query problem... > Hi, > > I have a table that contains the prices of software products at different > quantity slab rates. > > I'm trying to get the Slab rate for a given number of licenses for a given > product_id. > > For example, what is the cost of 500 licenses of Product_ID 143 ? > > The slabs do not continue for an infinite number, hence I may only have slab > pricing for 1-10, 11-100, and 101-200 with the assumption that if there are > no higher slabs and I wanted say the rate for 500 licenses, I would use the > highest slab unit available, in this case the 101-200 slab. > > My table structure is like so: > > Slab_Master > +----------------+-------------+------+-----+---------+----------------+ > | Field | Type | Null | Key | Default | Extra | > +----------------+-------------+------+-----+---------+----------------+ > | TInt_SlabID | int(11) | | PRI | NULL | auto_increment | > | TInt_ProductID | int(11) | YES | | NULL | | > | TInt_StartSlab | int(11) | YES | | NULL | | > | TInt_EndSlab | int(11) | YES | | NULL | | > | TDbl_SlabRate | float(10,2) | YES | | NULL | | > +----------------+-------------+------+-----+---------+----------------+ > > I achieve the result I want with the use of 2 queries: > > Query 1: > > SELECT MAX(TInt_StartSlab) > FROM > Slab_Master > WHERE > TInt_ProductID = 143 > AND > TInt_StartSlab < 500; > > Which gives: > +---------------------+ > | MAX(TInt_StartSlab) | > +---------------------+ > | 101 | > +---------------------+ > > This I store in a variable, say $MAX, and execute Query 2; > > Query 2: > > SELECT TDbl_SlabRate > FROM > Slab_Master > WHERE > TInt_ProductID = 143 > AND > TInt_StartSlab = 101; /*$MAX = 101*/ > > Which gives: > +---------------+ > | TDbl_SlabRate | > +---------------+ > | 80.00 | > +---------------+ > > > I would ideally like to combine this into a single nested SQL query, however > all my attempts at that have failed, and I remember reading somewhere that > MySQL does not support nested SQL queries. Is a single query possible? > > > Cheers, > -Srini > -- > http://symonds.net/~sriniram > > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > > -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
