I am trying to do move a (hard to maintain) spreadsheet task
over to the Mysql database so I can more easily add to the
data and harvest result information.
I am using Mysql 3.23.49
The following table parts have been created and will be
referenced below:
CREATE TABLE TestSuite (
TSid int(11) NOT NULL auto_increment,
TSname varchar(16) NOT NULL default '',
TSstatus enum('Included','Excluded') NOT NULL default 'Excluded',
PRIMARY KEY (TSid),
UNIQUE KEY TSid (TSid,TSname),
KEY TSid_2 (TSid,TSname)
) TYPE=MyISAM;
CREATE TABLE Command (
Cid int(11) NOT NULL auto_increment,
Cname varchar(32) binary NOT NULL default '',
Cstatus enum('Included','Excluded','Unknown') NOT NULL default 'Unknown',
PRIMARY KEY (Cid),
UNIQUE KEY Cname (Cname),
) TYPE=MyISAM;
CREATE TABLE TestCmd (
TSCcmd int(11) NOT NULL default '0',
TSCtest int(11) NOT NULL default '0',
UNIQUE KEY TSCcmd (TSCcmd,TSCtest)
) TYPE=MyISAM;
I have inserted 15 rows into the TestSuite table, 3 of which
are 'Included' and 12 which are 'Excluded' in the TSstatus
field.
The Command table has been set up with 1032 rows of which
only 128 have the Cstatus field as 'Included'. (The rest
are 'Excluded' for now.
I then put some relational data into the database (using
the TestCmd table using the following:
"SELECT * FROM Command WHERE Cname LIKE '$name'";
"REPLACE INTO TestCmd VALUES ($Cid,$TSid)";
This works fine. I can view data for 'Included' test suites
on 'Included' commands using:
SELECT Cid,Cname FROM Command LEFT JOIN TestCmd ON TSCcmd=Cid LEFT JOIN TestSuite ON
TSid=TSCtest WHERE TSstatus='Included' ORDER BY Cname
I can also view data for the 'Excluded' test suites on the
'Included' commands using:
SELECT Cid,Cname FROM Command LEFT JOIN TestCmd ON TSCcmd=Cid LEFT JOIN TestSuite ON
TSid=TSCtest WHERE TSstatus='Excluded' ORDER BY Cname
Now for the questions -
I would like to be able to basically do the reverse of what I
just did above. That is,
I would like to be able to view the data where 'Included'
commands do not have any 'Included' tests.
I would also like to view the 'Included' commands that do not
have either 'Included' or 'Excluded' tests.
I have tried a number of combinations for SELECTion with JOINS
and such, but have not been able to come up with something
that is correct. (As mentioned at the first, I actually have
all this information in a very large - hard to maintain -
spreadsheet, so I can verify the results.)
I appreciate, and will try, any feedback this list may
provide.
Thanks,
Doug
--
Doug Beattie
[EMAIL PROTECTED]
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
