using a column value in IN() in a join condition.

[Ken Easson]

Hello,

I am trying to retrieve a cross join of two tables. Table one contains an id column, 
table two contains a column that can list up to three id's from table one.

SELECT dix_ondemand_genre.id, dix_ondemand_shows.genre,
id IN(dix_ondemand_shows.genre) as test
   FROM dix_ondemand_genre CROSS JOIN dix_ondemand_shows ON id 
IN(dix_ondemand_shows.genre);

This select finds only the first value of the IN() column, however i want to return 
every possible result for all genres (Cartesian product):
to illustrate - here is a sample output of what i do get:

id | genre
1 | 1,2,6
3 | 3,6
4 | 4,6

But this s what i want is to have:
1 | 1,2,6
2 | 1,2,6
3 | 3,6
4 | 4,6
5 | NULL
6 | 1,2,6
6 | 3,6
6 | 4,6

actually - what i really want is to be able to group the id column to count how many 
genres are returned for each id, however the group by isn't the problem... I've tried 
quite a number of Join types, all to no avail.

any help would be great.

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.  


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

best way to optimize a table with many to few relationship.

[Ken Easson]

hello,

I have a question about database structure and optimization.

I have a shopping cart where several items can fall into several of many categories, 
but each item can also be several of a few product types.
my product type is limited to aprox. 5 product types. Further 99% of items will be in 
all 5 product types.

I am trying to figure out the best way to optimize the relation ship between product 
type and product.

if i create a product type table and then list each product in this type, i will have 
close to 5 times the number of rows as i do number of products. since almost all 
products will be in all product type lists. Conversely i can do an exceptions table 
where a table lists the product types that a product is NOT in. This of course add's a 
degree of difficulty if a new product type is added.

the other way of solving my problem is to add a types column to my product table, 
and adding the product type as a list, however this seems strangely NOT relational 
database savy:
SELECT * FROM products WHERE type IN ($mytype);

OR since i have such a small number of product types, does it make sense to create a 
table which contains each product id, with a column for each product type with a 
boolean field type? If this is recommended, how is this table optimized?

here is a sample of my data to illustrate where i'm coming from:

item: clown
categories: humorous, figure, 
product: pad, case, appliance

item: robot-boy
categories: si-fi, figure, abstract, robots, boys
product: pad, case, appliance

item: robot-girl
categories: si-fi, figure, abstract, robots, girls
product: pad, case, appliance

item: dragonship
categories: si-fi, animals, fantasy, space
product: pad, case

At 05:08 AM 2/3/2003 -0800, you wrote:
From manual:
LONGBLOB 
A BLOB or TEXT column with a maximum length of
4294967295 (2^32 - 1) characters

If I insert a 400k file into a longblob, will it
occupy 400k in actual space on the harddrive, or will
it occupy the maximum for a longblob?

Is there any difference here between innoDB an MyISAM?
I read that MyISAM only can handle blobs up to 16
mb...

Any thoughts?
// Michelle
SQL, Query

__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.yahoo.com

-
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

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support. 


-
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

best way to optimize a table with many to few relationship.

[Ken Easson]

hello,

I have a question about database structure and optimization.

I have a shopping cart where several items can fall into several of many categories, 
but each item can also be several of a few product types.
my product type is limited to aprox. 5 product types. Further 99% of items will be in 
all 5 product types.

I am trying to figure out the best way to optimize the relation ship between product 
type and product.

if i create a product type table and then list each product in this type, i will have 
close to 5 times the number of rows as i do number of products. since almost all 
products will be in all product type lists. Conversely i can do an exceptions table 
where a table lists the product types that a product is NOT in. This of course add's a 
degree of difficulty if a new product type is added.

the other way of solving my problem is to add a types column to my product table, 
and adding the product type as a list, however this seems strangely NOT relational 
database savy:
SELECT * FROM products WHERE type IN ($mytype);

OR since i have such a small number of product types, does it make sense to create a 
table which contains each product id, with a column for each product type with a 
boolean field type? If this is recommended, how is this table optimized?

here is a sample of my data to illustrate where i'm coming from:

item: clown
categories: humorous, figure, 
product: pad, case, appliance

item: robot-boy
categories: si-fi, figure, abstract, robots, boys
product: pad, case, appliance

item: robot-girl
categories: si-fi, figure, abstract, robots, girls
product: pad, case, appliance

item: dragonship
categories: si-fi, animals, fantasy, space
product: pad, case

SELECT QUERY MYSQL

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.  


-
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

how do i retrieve distinct rows using IN

[Ken Easson]

hello,

I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.

table sys_bld_foo:
component:  varchar 16
catNum: varchar 16
price: decimal (10,2)

i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 
'partB');

my return value ignores the second partB - and my price is short.

when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN ('item1', 'partA', 'partB', 'partB');

my return value doubles up on partA and partB - and my price is too much.

here is a sample database to see where the problem lies:
component | item |  price
-
foo| item1   |  200.00
foo | item2   | 300.00
primary bar | partA   |   75.00
primary bar | partB   | 150.50 
second bar | partA|  75.00
second bar | partB   |  150.50
second bar | partC| 160.00

currently i am using perl to remove the second partB, create a hash $catNum{partB} = 
2, and then for each item returned, check the hash and multiple the price by it's 
results. This seems extremely slow if my problem can be solved in sql alone.
can anyone help?

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support. 


-
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

Re: how do i retrieve distinct rows using IN

[Ken Easson]

hello,

SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN 
('item1', 'partA', 'partB', 'partB');

problem --|--|
partB is duplicated in the IN list. i need IN to find a DISTINCT price for EACH 
element in the list.

Gerald's solution i think gives me the same incorrect sum that i get when i sum the 
results outside of mysql, but i do like the idea, if there is an sql that will 
generate the correct result set, i will use the SUM - save me some of code.

my database includes the general category a part falls into - monitor, cpu, 
motherboard, hard drive etc.
and then the catalog code (catNum); amd 2.8Gh processor, intel 3.0Ghz processor etc. 
normalizing the category column results in about 20 tables, each with about 6 items 
each, which i think is a classic example of over-normalization, also, virtually every 
call to the database will retrieve 1 or more parts from each category, again 
normalization would create a terrific slowdown in the search, and overcomplicate my 
database.

my primary key is a combination of both the component, and the category number. This 
is to allow each component to have none as a valid catNum.

however what i hadn't taken into consideration is that some computers have 2 identical 
hard drives. which is were my particular problem lies, there are two components 
'primary hard drive', and 'secondary hard drive', with virtually the same list of 
catNum's, and if a user only selects one hard drive, the catNum IN (hardDrive1) return 
the price for the hard drive in primary hard drive, and secondary hard drive, or 
effectively doubling the price of that component.
To solve this problem i used the DISTINCT keyword, however now when we build a system 
with two identical hard drives, (hardDrive 1, hardDrive 1) the system see this as a 
duplicate record and ignores the second hard drive, again the incorrect sum of the 
price.
Yes i know it's ugly from a clean database structure, but having only one set of hard 
drives, but doubling them up in the actually program again makes everything else much 
more complicated.

What i am looking for is something like this:

foreach (hardDrive1, hardDrive1, monitorA, CPUA, VideoCardD)
SELECT price FROM sys_comp WHERE catNum = $_
}

hope that makes it clearer what i am trying to accomplish.


At 09:56 PM 1/15/2003 +, you wrote:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN 
('item1', 'partA', 'partB', 'partB');

is a valid SQL statement from all i can see


my return value ignores the second partB - and my price is short.
i dont understand what you mean by 'short'


you comment that if you remove the DISTINCE that it 'doubles up' the partA and 
partB data... yes correct.. that is how your data in the database is.  
multiple rows for 150.50 price

i think you have your items and parts (of items) in the same table and that 
generally is a bad idea..  from your data i would say you have 7 items in your 
table... OR 7 parts  but i think you are trying to say you have 2 items, 
with parts for them maybe??   

What is the problem, the business problem you are trying to solve? or what do 
you need from your data?  i think you are just querieng for the wrong 
information .. but since i dont know what you are trying to do, i cant say 
more right now. 
Id need more information..

Brian Lindner



-- Original Message -
Subject: how do i retrieve distinct rows using IN
Date: Wed, 15 Jan 2003 15:12:37 -0400
From: Ken Easson [EMAIL PROTECTED]
To: [EMAIL PROTECTED]


hello,

I have a list of things ('item1', 'partA', 'partB', 'partB')
which relate to catNum items in a table sys_bld_foo.

table sys_bld_foo:
component:  varchar 16
catNum: varchar 16
price: decimal (10,2)

i want to return 1 row for each item:
when i use:
SELECT DISTINCT price FROM sys_bld_foo WHERE catNum IN 
('item1', 'partA', 'partB', 'partB');

my return value ignores the second partB - and my price is short.

when i use:
SELECT price FROM sys_bld_foo WHERE catNum IN 
('item1', 'partA', 'partB', 'partB');

my return value doubles up on partA and partB - and my price is too much.

here is a sample database to see where the problem lies:
component | item |  price
-
foo| item1   |  200.00
foo | item2   | 300.00
primary bar | partA   |   75.00
primary bar | partB   | 150.50 
second bar | partA|  75.00
second bar | partB   |  150.50
second bar | partC| 160.00

currently i am using perl to remove the second partB, create a hash $catNum
{partB} = 2, and then for each item returned, check the hash and multiple the 
price by it's results. This seems extremely slow if my problem can be solved 
in sql alone.
can anyone help?

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support. 


-
Before

preventing duplicate records when using WHERE foo IN syntax

[Ken Easson]

Hello,

I need to get a single result from a SELECT from a table with the following sturcture:

component - varchar(25) primary key
catNum - varchar(25) primary key
price - decimal(10,2)

my component list may contain duplicate catNum ie:

primary drive   -WD1GJB
- WD800JB
-none
secondary drive - WD800JB
-none
monitor -SONLCD19
-none

any one catNum will have the same price so when i go looking for parts, knowing the 
catNum, i do this:

SELECT price FROM syscomp WHERE catNum = $catNum

no problem - i only look at the first return and am happy!

However when putting together a system - and i have the following:
$system =  qq('WD800JB', 'SONLCD19', 'none', 'WD1GJB');
and insert that into my SQL as:
SELECT price FROM syscomp WHERE catNum IN ($system);

if 'WD800JB' is in TWO components, i get two results - thus, and incorrect number of 
return results.
(price is doubled)

However when i use:
SELECT DISTINCTROW price FROM syscomp WHERE catNum IN ($system);

and my list looks like this:
$system =  qq('WD800JB', 'WD800JB', 'SONLCD19', 'none');

i only get 4 results, the second 'WD800JB' is ignored.

The only solution i can think of is:
$system =  ('partA', 'partB', 'partC', 'partD', 'partD');
foreach ($system){
SELECT price FROM syscompt WHERE catNum = '$_'
# incrememnt $total with the first resutl.
}

BUT this seems painfully slow - as this would result in up to 20 calls to the sql 
server.

I could ensure that each $system had the matching component, to match on the primary 
key, but i don't know how to set up the IN statement for a double part: 
WHERE concat (component, '-', catNum) IN ($system)

can anyone help me with a solution.

thanks.

ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support. 


-
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

including text or blob in large table that is freaquently searched.

[Ken Easson]

hello,
i have a table as follows:
Members  (
 teamID   int(6) unsigned default '0',
 ID   int(11) unsigned NOT NULL auto_increment,
 name varchar(30) default NULL,
 number   smallint(5) unsigned default NULL,
 height   tinyint(3) unsigned default NULL,
 weight   tinyint(3) unsigned default NULL,
 DOB  varchar(21) default NULL,
 Hometown varchar(30) default NULL,
 yearsPlaying tinyint(3) unsigned default NULL,
 gamesPlayed  tinyint(3) unsigned default NULL,
 Points   smallint(6) unsigned default NULL,
 emailvarchar(60) default NULL,
 retired  tinyint(3) unsigned default NULL,
 detailsIDint(11) unsigned default NULL,
   PRIMARY KEY ( ID  ),
   KEY   teamID  (   teamID  )

i am trying to determine which will make this table work faster in my web 
applications.
if i keep detailsID as a tinyint that refers to a new table of the structure:
memberDetails(
 ID   int(11) unsigned NOT NULL auto_increment,
 details  blob,
   PRIMARY KEY ( ID  ),

OR if i alter detailsID int(11) to details blob,
i'm not interested so much in normalization, but optimization where i may 
be searching for records regularly based on teamID, ID, name, number, and 
name AND number.
i doubt i'll ever need to search the details records.
but with so many criteria for indexing - i'm wondering if having the blob 
in the main table will slow things down.
this table is my primary table in a very hi volume website, and should 
expect some 3000 - 5000 hits per day.

i've looked thru table optimization in the manual, but i'm not confident 
with how mysql sets up its searches based on indexes. and find that it 
doesn't always use the indexes i create to assist in the searches.

i'm designing for around 15,000 records in this table. where details can be 
several lines of text (on average 150 words).


ken easson
justken.net
[EMAIL PROTECTED]
justken web programming and technical support.


-
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

RE: merge tables using sql to find table names

[Ken Easson]

Does your master table have a list of table names and the table's
characteristics? How is the search called?


currently i have a master table called files, the primary key is id and 
the data tables are called filedata1 filedata2, etc.
where 1 and 2 correspond to the file id. Each of the filedata tables 
contains all the content to dynamically create a new file based on a linked 
template.

file
id = 1, name=greenville,  contact=bob, template=3, defdirectory = 
soccer/ etc...

filedata1
id = 29, type=1, data1=April 3, we won 6-4 against the bedford bears, 
data2=null, data3=null
id=34, type=1, data1=Welcome to the greenville soccer team web page, 
data2=null, data3=null

when the type=4 it looks for the text in the TextData file. However, when 
there is much less text, it is stored in the filedata file itself in the 
data1 column. data1 also contains the text that will be used in the alt tag 
for images(type=2), and the link's tags for links (type=3), etc.

currently i am searching the TextData table using:

$sql = SELECT files.location, files.filename, TextData.*, 
MATCH(TextData.text) AGAINST ('$searchFor') AS score ;
$sql .=FROM TextData, files WHERE MATCH(TextData.text) AGAINST 
('$searchFor') AND files.id = TextData.fid;

1) i want to be able to merge the FileDataX tables like this- only this 
doesn't work: error near (SELECT CONCAT...

$sql = CREATE TEMPORARY TABLE searchtable (id int, type tinyint, data1 
varchar(255), data2 int, data3 int)  TYPE=MERGE UNION=(SELECT 
CONCAT('filedata',id) FROM files WHERE defdirectory LIKE '$defdirectory%') 
INSERT_METHOD=LAST;

and then perform my full text search on data1 where type=1?

snip: jay
Seems like you would have to call multiple queries no matter what, unless
you creat one large merged table for your several thousand small tables. Is
this normalization gone overboard? Do all of the tables match in
configuration (same # of columns, same datatype per column, etc.)?
/snip

actaully - i thought about one BIG table, or even medium sized tables 
however the primary use of the tables is to build one file from one 
filedata table, with the future ability for files to reference each other, 
to share data. to simplify the process, the editor reads in the entire 
table, presents the old data, and then on update, it deletes the entire 
table, and inserts the new data. otherwise, i would have to look for data 
that was removed, create an array of deleted rows, and then run the delete 
query, which actually takes lot more time than delete * from filedataX. 
There are several other factors that caused me to go the route of several 
filedata tables for each file.
i will also say - my normalization isn't as smart as i'd have liked, and 
i'm thinking that i may move ALL the text into the textdata table, which is 
3 columns
fid = files.id INT
line = filedatax.id INT
text = whatever text TEXT

but i have an index on my text column (required for the fulltext seach) and 
am concerned that if it's tooo big, it'll be a very slow search! as well as 
slowing down my editor too much, if got it down to about 20:1 small text : 
large text.


ken 


-
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