NYCPHP Meetup

[Tim Gales]

Okay let's try a simpler case:

 

You have some products :

 

CREATE TABLE prods (
  prod_id int(4) default NULL,
  name varchar(20) NOT NULL default '',
  desc varchar(20) NOT NULL default ''
) TYPE=MyISAM COMMENT='Products Table';
 
 
INSERT INTO prods (prod_id, name, desc) VALUES (1, 'one',
'first');
INSERT INTO prods (prod_id, name, desc) VALUES (2, 'two',
'second');
INSERT INTO prods (prod_id, name, desc) VALUES (3, 'three',
'third');
INSERT INTO prods (prod_id, name, desc) VALUES (4, 'four',
'fourth');
INSERT INTO prods (prod_id, name, desc) VALUES (5, 'five',
'fifth');
INSERT INTO prods (prod_id, name, desc) VALUES (6, 'six',
'sixth');
 
And the products are sold in districts called groups:
 
CREATE TABLE groups (
  group_id int(4) default NULL,
  name varchar(20) NOT NULL default '',
  desc varchar(20) NOT NULL default ''
) TYPE=MyISAM COMMENT='Products Table';
 
INSERT INTO groups (group_id, name, desc) VALUES (10,
'east', 'eastern district');
INSERT INTO groups (group_id, name, desc) VALUES (20,
'west', 'western district');
 

And you have an associative table which tells which products
are sold in which districts:

 
CREATE TABLE assoc (
  prod_id int(4) NOT NULL default '0',
  group_id int(4) NOT NULL default '0'
) TYPE=MyISAM COMMENT='Associates Prouduct with Groups';
 
INSERT INTO asoc (prod_id, group_id) VALUES (1, 10);
INSERT INTO asoc (prod_id, group_id) VALUES (2, 10);
INSERT INTO asoc (prod_id, group_id) VALUES (3, 10);
INSERT INTO asoc (prod_id, group_id) VALUES (4, 20);
INSERT INTO asoc (prod_id, group_id) VALUES (5, 20);
INSERT INTO asoc (prod_id, group_id) VALUES (6, 20);
 
In English you sell six products one, two and three are sold
in the eastern district and
Four, five and six are sold in the western district.
 
You were trying something like this:
 
SELECT p.prod_id


FROM prods p, assoc a, groups g


where p.prod_id = a.prod_id


and a.group_id = g.group_id


and a.group_id


and g.group_id in
(select group_id from groups where group_id = 10)
 
which roughly states:
 
find the products and districts that exist in a set of
groups that are equal to 10
 
or:
 
find product information from the eastern district 
 
or:
 
SELECT p.prod_id


FROM prods p, assoc a, groups g


where p.prod_id = a.prod_id


and a.group_id = g.group_id


and a.group_id


and g.group_id = 10
 
What do think?

 

T. Gales & Associates 
Helping People Connect with Technology 
http://www.tgaconnect.com 

-----Original Message-----
From: talk-bounces at lists.nyphp.org
[mailto:talk-bounces at lists.nyphp.org] On Behalf Of Phil
Powell
Sent: Sunday, September 21, 2003 2:17 PM
To: NYPHP Talk
Subject: Re: [nycphp-talk] Error involving inner SELECT using
mySQL - HELP

 

I did, it seems that mySQL is becoming more and more of a
waste of database!

 

Can't do subqueries, can't do foreign key constraints, even
the datetime field column datatype doesn't allow for the
now() function as an input parameter value in INSERT?.. so
tell me what GOOD is mySQL anyway????

 

I rewrote the entire database table schema relational
structure to include another association table and used
that, flatting out the query.  However, it's really BAD
coding because I have this query generating for every single
$row['nnet_produkt_id'] from the outer query results.  BAD
BAD BAD.. that means a while loop INSIDE another while loop!

 

Can you say performance murder!

 

Phil

 

 

----- Original Message ----- 

From: Jeff Siegel <mailto:jsiegel1 at optonline.net>  

To: 'NYPHP Talk' <mailto:talk at lists.nyphp.org>  

Sent: Sunday, September 21, 2003 1:46 PM

Subject: RE: [nycphp-talk] Error involving inner SELECT using
mySQL - HELP

 

You need at least ver. 4.1 of mySql. You may also be able to
rewrite it.

See: http://www.mysql.com/doc/en/ANSI_diff_Subqueries.html

 

Jeff Siegel

-----Original Message-----
From: talk-bounces at lists.nyphp.org
[mailto:talk-bounces at lists.nyphp.org] On Behalf Of Phil
Powell
Sent: Sunday, September 21, 2003 11:54 AM
To: NYPHP Talk
Subject: [nycphp-talk] Error involving inner SELECT using
mySQL - HELP

Ok guys, here we go again!

 

SELECT s.nnet_produkt_storrelse_navn 

FROM nnet_produkt_storrelse s, nnet_produkt_varegruppe v,
nnet_storrelse_varegruppe_assoc sv 

WHERE s.nnet_produkt_storrelse.id =
sv.nnet_produkt_storrelse id 

 AND sv.nnet_produkt_varegruppe_id =
v.nnet_produkt_varegruppe_id 

 AND sv.nnet_produkt_varegruppe_id IN (

   SELECT nnet_produkt_varegruppe_id 

   FROM nnet_produkt_varegruppe

   WHERE nnet_produkt_varegruppe_code =  'FLU17' 

 )

 AND s.nnet_produkt_storrelse_id > 1

 

This produces the following error:

 

You have an error in your SQL syntax near 'id AND
sv.nnet_produkt_varegruppe_id = v.nnet_produkt_varegruppe_id
AND sv.nnet_' at line 1

 

I don't see anything wrong with this query, it's standard
inner SELECT stuff!  I need help... again!

 

Phil


  _____  


_______________________________________________
talk mailing list
talk at lists.nyphp.org
http://lists.nyphp.org/mailman/listinfo/talk

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.nyphp.org/pipermail/talk/attachments/20030921/26850a78/attachment.html>