Posts by tesuji

Hi muppet

CASE is kind of function which is only allowed to return a constant value. However with "'07:20:00' and '15:19:59'" you return part of an executeable sql statement (AND) what is illegal. Actually, mysql must notify an error yet remains silently.

Also experts then rubbing their eyes in disbelief for their statement is considered to be correct by the mighty system though the result is absurd ("...note the time in the timestamp is outside of where it should be.")

However that be, you could try this modified query:

select day(timestamp) as tamp, tech, packer_l, packer_r,  (packed_l + packed_r) as packed, timestamp as ts
 FROM abm_status
  where
   (
   case
     when (time(timestamp) between '07:20:00' and '15:19:59') then 'day' 
     when (time(timestamp) between '15:20:00' and '23:19:59') then 'swing' 
     else 'night' 
   end 
     
   =
   
   case
     when (time(now()) between '07:20:00' and '15:19:59') then 'day' 
     when (time(now()) between '15:20:00' and '23:19:59') then 'swing' 
     else 'night' 
   end
   )
   and tech != ''  
   and tech != 'Dummy Login' 
   and YEAR(timestamp) = YEAR(CURDATE()) AND MONTH(timestamp) = MONTH(CURDATE())
   order by packed desc; /* limit 1  ;*/

Please tell me whether this will work successfully, also what modifications you did to get it going.

Hint: You can't write that: "(time(now()) between '23:20:00' and '07:19:59')". Also this is formally incorrect for the left boundary must always be less or equal to the right one, usually you don't get an error reported, even on Oacle database. Neither you can interchange the boundaries. Instead you should write:

(time(now()) between …

Hello again

He, what about the 66 whales, could any of them be rescued?

Hell yeah, '07:20:00' AND '15:19:59' evaluates to 1! I have never seen other database systems than mysql doing seriously boolean algebra on time strings! Though this be kind of compensation for the below rough violation of ANSI SQL standard aliases? (now() of 1st line can differs from 2nd and 3rd lines ones!)

No, your case is not correct. I would make use of such a construct:

select time(now()) as "too sad, mysql cannot use this alias elsewhere",
case 
  when (time(now()) between '07:20:00' and '15:19:59') then 'The early bird catches the worm'
  when (time(now()) between '15:20:00' and '23:19:59') then 'We are from swinging' 
  else 'Nighty night everyone!'
end as "what a pitty";
/*
too sad, mysql cannot use this alias elsewhere  what a pitty
--------------------------------------------------------------------
15:50:42                                        We are from swinging

*/

-- tesu

(btw, a practial example like your earlier one would help much)

Hello

If your problem is still existed, you may check whether TNSNAMES.ORA is in your directory path. There could also be discrepance between SQLNET.ORA and TNSNAMES.ORA. You could post both files here (make copy of them and replace your private data). Well, I am not an ora admin, just working with it, nevertheless I am trying to help you.

Where is our friend Deabsis Das? He is true Oracle expert and can help you instantly.

-- tes

Hello Garrett85

Congratulations that you started to learn SQL from this great book Simply SQL written by my friend Rudy Limeback! This is the finest book for beginners I ever read. Clearly and precisely written, covering all important aspects of (DML-) SQL, contains a wealth of useful examples and tips, thoroughly founded on Rudys comprehensive experiences on database field.

Yes, it is "The fun and easy way to learn best-practice SQL".

I am sure that Rudy will add a small chapter on DDL-SQL to the next edition, the only very little SQL-part I am really missing from his wonderful book.

As for MySQL workbench you have already downloaded, assuming you have installed Ubuntu 10.04 (lucid) then workbench version must be 5.1.xx which does not have integrated Query SQL broswer to execute simple SQL statement like SELECT * FROM ... nor it has mysql administration functionality. However, it is possible to build version 5.2, which contains query and admin, from the sources. I have tested 5.2 new functionalities and in my opinion it is still in a very beta state. Of course, it is a good tool for designing kind of entity relationship models (ERM, not EER), much more better than its old error-prone predecessor (V4.2) from source-forge.

However, the most important thing is, you need to install a standard SQL database first, if you don't have already done this job. So what database for beginners?

Well, if you complete Rudy's book, you will have …

XP is a pretty good OS. No reason for changing it. Regardless of your future database wouldn't it a good idea to install service pack 3? Consider, MS has stopped further development of XP, there are only security-updates, so sp3 is important.

-- tesu

Hello

Developer version can be downloaded here. It doesn't matter whether you use V11.0.1 or V12.0.0. However, V11.0.1 is absolute stable (you should update to newest EBF, express bug fix) and V12.0.0 has been out for some months and doesn't support all languages, only English so far. I have both running on Linux, and they are absolutely stable. I have forgotten to mention in my prior posting that sqlanywhere v12 already supports many of the new ANSI SQL2008 standards.

You will get a developer key per email, what you will have to enter when install it.

btw, what is your operating system?

--tesu

Well, Oracle went to the Internet with version 8i (i for Internet). That happened in 1999. Since then there were versions 9i, 10g, and 11g in 2007.

So 8i is somewhat outdated. There have been dramatic improvements since 8i. If you want to stay with Oracle, I suggest free 10g express version (there isn't 11g express so far).

If you have free choice, I suggest to try free Sybase SQL Anywhere V12 developer (which is, in my opinion, simply the best for beginners), a complete database system, almost 100% ANSI SQL 1999/2003 (no fakes and flaws a la mysql), programable in almost all languages (ada, fortran, cobol, c, c++, Java, mumps, php, perl, ruby, .net, jsp ...), runs on Windows, Linux, Mac, is embedable, leading product in mobile computing..., MS SQL Server is derived from Sybase (licenced in 1995), however Sybase is more modern and has better coverage of ANSI SQL, (no, I am not an agent of Sybase).

-- tesu

+++ deleted +++
Sorry, you aren't allowed to use two-dimensional arrays, so my posting is useless !
+++ deleted +++

You are using "0" for labelling the root of a tree. If "0" is a numeric value, it may function. If it were NULL, you can't get the root by my first select. For example:

SELECT cpy_id, cpy_name FROM cpytable WHERE cpy_id IN (SELECT parent_id FROM cpytable WHERE cpy_id=4444 OR parent_id=0);

gives parent of 4444 and ALL parents having parent_id = 0, those are all parents theirselves. To differentiate between the root parents why not setting parent_id = cpy_id, as for example:

cpy_id cpy_name   parent_id 
1111 "ABBB"       1111
2222 "CDDD"       2222
3333 "ABBB Japan" 1111
4444 "CDDD China" 2222

-- and the first select could work:

SELECT cpy_id, cpy_name FROM cpytable WHERE cpy_id IN (SELECT parent_id FROM cpytable WHERE cpy_id = 4444 OR cpy_id = 2222);

-- tesu

In your example say, 1 and 4, is 1 identical to 1111 respectively 4444? if so, this select could help:

select cpy_id, cpy_name from cpytable where cpy_id in (select parent_id from cpytable where 'your criteria to get 1 and 4');

However, if 1 and 4 kind of level, than I would add level number to your table:

cpy_id cpy_name       parent_id   level
1111   "ABBB"         0             1
2222   "CDDD"         0             1
3333   "ABBB Japan"   1111          2
4444 "CDDD China"     2222          2

-- and the select could be for example:
select cpy_id, cpy_name from cpytable where cpy_id in (select parent_id from cpytable where level = 2);
/* result
1111   "ABBB"
2222   "CDDD" 
*/

Btw, there is a famous guy on the internet: Joe Celko, he wrote a book only dealing with SQL and trees, hierarchies. You may google celko trees sql.

-- tesu

Hi

I am really confused, how is your "set of values (1,4)" related to your example table? An why you decide that then you should get the names 1 and 2? The names 1 and 2 point to what of your example table?

I understand that your table is kind of recursive table which defines a tree because parent_id points to rows in same table, however I don´t see the pair (1,4) in that table.

-- tesu

Hi

Thanks for posting your data.

Finally I figured out why my suggested query never ends on your Mysql database if there are just 10585 rows affected. The reason is that simple and also that frustrated: MySQL does NOT have effective Query Optimizer. I found that indirectly by calculating the execution plans for my select statement on those databases: MS SQL Sever, Oracle and Sybase SQL Anywhere. All these three database systems replace slow correlated selects by fast table join operations! Query optimizing is quite usual and virtually almost all database systems have this useful functionality except mysql.

On Sybase database I have SQL test tools for testing and optimizing select statements by means of automatically generated test data. I fed the test tool with the select statement in question, defined your both table and necessary data pattern for automatic generation of test data.

Testing was carried out with the following volume: table viscawangan 10585 rows (your number), historytable 105850 rows. I decided that each branch_code should have 10 randomly generated date_update and amount_of_people values on average, that is 10 times 10585. So the total amount of rows processed in first inner join was 116435 rows. The result set contained 9843 rows something less than viscawangan rows for I allowed that also viscawangan rows were generated without entries in historytable.

Testing result for this scenario: Execution time 0.445 seconds (less than a half second). Well, that's it, the result of a true relational database system.

Tesu,
The count record is 10585.

Well, 10585 rows is something heavy for correlated selects. Can you tell me the indexes and keys which are chosen for this query? Simply run

explain 
select v.branch_code, v.branch_name, h.date_update as date_latest, h.amount_of_people as amount
  from viscawangan v join historytable h on v.branch_code = h.branch_code
   where h.date_update=(select max(date_update) from historytable where v.branch_code = branch_code) order by v.branch_name;

Then post its result. Also post your original select what you have derived from mine.

You see the max(date_update) above. Can you estimate from how many row per branch_code the max() is taken?

I think, if you put an index on (branch_code ASC, date_update DESC), execution time will be substantially reduced.

-- tesu

Well, my suggested query should meet your requirements. Can you examine how many rows this join viscawangan v JOIN historytable h on v.branch_code = h.branch_code includes by executing:

select count(*) from viscawangan v JOIN historytable h on v.branch_code = h.branch_code;

The drawback of my suggested query is that it uses a correlated query (WHERE v.branch_code = branch_code) what becomes very slow if a plenty of rows are affected and no index or key can be used.

One can speed up correlated queries by putting indexes/keys on the appropriate columns. In your case the max(date_update) is the obstacle. Therefore you could index the important column date_update of historytable. And you should then use EXPLAIN to examine whether this newly generated index is really used in the correlated queries.

I am about to thinking over whether these correlated queries might be replaced by better query not having correlations, however, I am afraid there is no other solution. If so, only additional index/key on column date_update of historytable wil be able to speed up calculation considerably.

-- tesu

well jwebb, this is kind of "disimproving".

Why didn't you exactly do what I suggested?

int factorial(int input)
{
// my suggestion and NOTHING else!
}
int main(){
int input;
cin >> input;
cout << factorial(input);
return 0;
}

-- tesu

Well, and the body of function factorial(...) should be better completely replaced by

{
  if(input<=1) return 1;
    else return input*factorial(input-1);
}

Hi

Supposing your both queries select staffids which are not on holidays OR in
sessions you should select staffids which are in both result sets:

select * from staff 
  where staffid in (your select not on holidays )
    AND staffid in (your select not in sessions);

-- tesu

Selamat Siang

Tell me what will be the result if you carry out this:

select v.branch_code, v.branch_name, h.date_update as date_latest, h.amount_of_people as amount
  from viscawangan v join historytable h on v.branch_code = h.branch_code
   where h.date_update=(select max(date_update) from historytable where v.branch_code = branch_code) order by v.branch_name;

You may examine all words and syntax, there might be typos etc.

-- tesu

Hi red_ruewei,

the result corresponds completely to your select statement. However, it seems that it does not meet your concept. So what's wrong, what should be the exact result? Can you give an example? (Unfortunately, I have deleted the data once you sent to me. So I am unable to look inside the problem).

-- tesu

Hi

You already have got this:

select name, country_code from city where population >= 5000000);

If you omit "name", you get a country_code list with all countries having at least one city with pop. >= 5000000.

select distinct country_code from city where population >= 5000000);

This list can be used to get all cities of a particulary country where the condition is true:

select "Name" from city where country_code in (select distinct country_code from city where population >= 5000000);

You may add further country data by inner join:

select city."Name", country."Name" from city join country on city.country_code = country.country_code
  where city.country_code in (select distinct country_code from city where population >= 5000000);

You may omit or replace delimeters " " by mysql ` `. For "Name" is a reserved word in most database systems it isn't a good idea to use it for a column. In such case some database require that the word be surrounded with delimeters.

There is also an if-statement (if (cond) then ... else... end if; ) what can be used in user definded functions written in PSM language. This is supported by mysql. Unfortunately, mysql does not support the ANSI SQL if-statement for select-statements, yet it has case-statement.

-- tesu

well muppet,

yes, there are other solutions up the sleeve, for example a permanent view (for mysql's unability processing standard sql with clause) or simply a stored procedure which returns the union-part of the censured query.

However such stuff wouldn't have been necessary if one had simply replaced ansi sql standard delimeter " " by mysql's propritary well-known ` ` ones! You may study attached screen shot below.

I wish you all the very best!

-- tesu

Don't worry there are two further tries to go which I know from (or feel so at least)

Ah muppet, too bad that mysql still don't support ANSI SQL WITH-clause which has been introduced since SQL 1999 standard.

Yet there are some other approaches to solve it even on mysql.

So next try:

select Reason, sum(Quantity) as "Total Quantity" from
(
  select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma2
    where "Reason" IS NOT NULL group by "Reason" 
  union
  select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma2
    where "Reason" IS NOT NULL group by "Reason"
) as musthavename
group by Reason order by "Total Quantity" desc;

Should function on mysql for msyql manual tells that subqueries in from clause would be fully supported now. I am about to believe that :D

Result might be as desired:

| Reason      | Quantity |
| wide ties   |    7     |
| Torn        |    6     |
| dirt        |    2     |
| line up     |    1     |

-- tesu

Hi muppet,

sorry for the problems arose on your side through my simple solution. Indeed, I didn't consider that the same reason could appear on both sides. So, if you apply my old union select

select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma
  where "Reason" IS NOT NULL group by "Reason" 
union
select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc

on the new sample data which seems to be the old one extended by two further rows so as to reveal that weak select, the result is:

/*
Side   Reason    Quantity
-------------------------
right  torn      6
left   wide ties 6
left   dirt      2
right  wide ties 1
right  line up   1
*/

To show which side the rows are from, I added text 'left'/'right'. So it's clear reason 'wide ties' must appear twice. This will also occasionally happen for the other reasons.

Nevertheless, further aggregation is simple if above select is put in SQL-WITH statement, also supported by mysql, to further sum up identical rows:

WITH totaltotal (Side, Reason, Quantity) as
(
  select 'right' as Side, reason_right as "Reason", sum( scrap_right) as "Quantity" from bakma
    where "Reason" IS NOT NULL group by "Reason" 
  union
  select 'left' as Side, reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma
    where "Reason" IS NOT NULL group by "Reason"
  order by "Quantity" desc
)
select Reason, sum(Quantity) as "Total Quantity" …

I am back again. You are completely right, I have mistaken your problem. However, the most important thing is that:

Given your table:

DB_Salgspris db_interval_start db_interval_end db_customer
59          |         2       |   3           | 59
99          |         7       |   8           | 149
149         |         11      |   12          | 199
699         |         65      |   68          | 1099
1099        |         102     |  110          | 1699
 
If I do this:

SELECT DB_SALGSPRIS from pris_interval WHERE DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

I get:

DB_SALGSPRIS
------------
149

If I do this:

SELECT DB_CUSTOMER  from pris_interval WHERE DB_INTERVAL_START < '7.19'AND DB_INTERVAL_END >= '7.19'

I get:

DB_CUSTOMER
-----------
149

These results are correct. There aren't any further rows but the listed two. So something must be wrong with your database. I did both selects on Sybase database (MS SQL Server's mother).

-- tesu

Instead of:

... DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END >= '11.78'

should it not be:

... DB_INTERVAL_START < '11.78' AND DB_INTERVAL_END <= '12.78'
? Sorry, wrong too

or better:

...  '10.78' < DB_INTERVAL_START AND DB_INTERVAL_END <= '12.78'  -- to get 5th line
this works

-- tesu

Glad to meet you again.

Are you still suffering from the old database problem we were talken about in this thread? What about the ERM fragment I sent you in the very last post of that thread, wasn't there any chance to improve data model?

As for your first select, yes, this cannot function for the GROUP BY is plain wrong, (it might work like game of dice or even French roulette ;)). You may read here and some more details are there on wrong usage of GROUP BY clause.

-- tesu

Hi firstPerson,

I am not sure of that you just stated. Below is a small program where I tested a C function which searchs for longest common substring, a question arised by peter budo about a week ago.
Actually I also had some problems with clock() taken from time.h. I did a somewhat practical calibration for the execution time shown after the end of the program was always around 2 seconds in total. This is very close to the computed value of 1.75 seconds what resulted from clock()-values (ends minus starts) divided by CLOCKS_PER_SEC, see below.

#include <iostream>
#include <string>
#include <sstream>
#include <time.h>
using namespace std;
#include "lcstring_V4.h"
int main(){ 
  // testing LCString() --> Peter Budo
  clock_t starts, ends; double dura; int i,n;
  char s1[] = "/system/images/777/medium/Debrecen_-_University.jpg?1279547675";
  char s2[] = "Debrecen_-_Protestant_Great_Church.jpg";
  char s3[strlen(s1)];
  cout << "s1: " << s1 << endl;
  cout << "s2: " << s2 << endl;
  starts = clock();
  n = 100000;
  for (int i = 0; i<n; i++) LCString (s1, s2, s3);
  ends = clock();
  dura = double((ends-starts))/ CLOCKS_PER_SEC;
  cout << "LCString (v4) callings: " << n << endl;
  cout << "Effective loopings:     " << lcstring_counter << endl;
  cout << "Worst case loopings:    " << n*strlen(s1)*strlen(s2) << endl;
  cout << "Begin (clocks):         " << starts << endl;
  cout << "End (clocks):           " << ends << endl;
  cout << "Duration (clocks):      " << ends-starts << endl;  
  cout << "Duration (sec):         " << dura << endl; 
  cout << "LCS:                    " << s3 << endl; …

I am trying to understand :)

What you can also do is explicit type casting, for example

select '12345.678' as stringnumber, cast(stringnumber as decimal(15,2)) as "now numeric value"

results in 12345.68

cast() function also works for insert and update.

btw, text datatype has some limitations as compared with varchar. There were some postings regarding varchar vs. text recently on daniweb.

Why you need datatpye CLOB for bulbDesc? CLOB is huge string type (4GBYTE ore so) but very restrictive usage. So most standard functions cannot handle clob data.

-- tesu

Hi

simply divide clock-result by clocks per second, and don't forget the cast:

double_difference=double((end-start)) / CLOCKS_PER_SEC;

to get the correct seconds.

-- tesu

Hi

I am sure, you know that the precision and scale should be explicitely specified. If not, precision and scale of numeric and decimal DT are 10 and 0, that is default is decimal(10,0) or numeric(10,0). So numbers having decimal places will be automatically rounded. If you have money values, decimal(15,2), scale 2, for example is good idea.

Just out of curiousity, which mysql version are you running, below 5.0.3 ?

-- tesu

Hi

what did you already do, are there any sql statements approaching the problem by yourself, tentatively in nature?

-- tesu

These union selects

select reason_right as "Reason", sum(scrap_right) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
union
select reason_left as "Reason", sum(scrap_left) as "Quantity" from bakma_table
  where "Reason" IS NOT NULL group by "Reason"
order by "Quantity" desc ;

should give such a list

| Reason      | Quantity |
| Torn        |    6     |
| wide ties   |    5     |
| dirt        |    2     |
| line up     |    1     |

Quotation marks (" ") could be omitted, sometimes.

This code not tested, thought.

-- tesu

Hi

You can convert time string values with stringstream to double values. From double values you can cast hours and minutes:

#include <iostream>
#include <string>
#include <sstream>
using namespace std;
int main(){  
  stringstream conv;
  double t; int h,m;
  conv<<"9.45"; conv>>t;
  h=(int)t; m = (int)(100*(t-h)+0.5);
  cout<<t<<" = "<<h<<":"<<m<<endl;   // 9.45 = 9:45
  return 0;
}

I have choosen your "9.45" sample as constant input value for conv. You may use getline(cin, stime) to read in various strings stime and convert them by conv<<stime; conv>>t. What I liked to show is how to convert data from one datatype into another datatype using stringstream. Possibly there are shorter solutions.

-- tesu

Well, then you should qualify your duplicate columns, such a kind

select x.a as "a from x", y.a as "a from y"
from t1 x join t2 y on x.ca = y.cb ...

Maybe I got you wrong and you would like to add the table name to the row's values themselves?

Can you give a simple instance/example?

-- tesu

Well, can you tell me why you want to create a resultset which has pairwise exactly duplicate columns?

You need to specify them individually.

You know that the resultset of the cartesian product (select * from a,b) is extremely rarely useful.

-- tesu

Well, I think what he is looking for is to find the longest common substring (LCS) of both strings

/system/images/777/medium/Debrecen_-_University.jpg?1279547675

Debrecen_-_Protestant_Great_Church.jpg

where the green marked substrings seems to be such LCS. There is also a longest common subsequence problem where the sequence of chars need not to be contiguously stored.

LCS is a famous problem usually solved by dynamic programming, its time complexity is O(mn). Some theory can be found here. There are also implementations on wikibooks in various languages. Unfortunately, I haven't seen any implementation in PSM (pl/sql) for SQL user defined function (UDF). I am sure one cannot do that in SQL queries directly. So an UDF written in PSM or even a C/C++ implementation what can be called from SQL, what is possible as for example in postgresql, seems to be a solution. Yet what I have seen are some PHP implementations having poor O(n^4).

-- tesu

Hi Tommy,

greetings from the hood.

If I understand you correctly, you want to list all customers which have purchased goods for more than 5000EUR in 2010. This query should solve it (you need to substitute in your appropriate column names):

WITH totalAmount (Kunder, ID, Amount) as
(
  SELECT k.kunderName as "Kunder", k.kunderID as "ID", 
   SUM(f.Quantity * f.Itemprice) as Amount from kunder k join faklin f 
    ON k.kunderID = f.kunderID
     WHERE f.purchasedDate between '2010-01-01' AND '2010-12-31'
      GROUP BY k.kunderName, k.kunderID
)
SELECT * from totalAmount where Amount >= 5000 ORDER BY Amount desc;

Possibly you need to replace SUM(f.Quantity * f.Itemprice) by SUM(f.invoiceAmount), if you don't deal with invoice items.

Possibly you may also have to change date format. I took standard ISO date format yet your DB
may have other settings (Denmark's date format same as Bavaria's?)

-- tesu

Hi tanha

It is always a good idea to start database design with an entity-relationship model. Depending on whom you design the ERM for, there are more or less flaws present. First, relationship student many-to-many dependent allows that a student would have many mothers and fathers.

This impossibility can be solved by two many-to-one relationships between student and dependent. Because primary key of dependent now appears as foreign key (fk) twice in student, what is not allowed, this fk must be given role-names, e.g. fatherID and motherID.

Second, in your comment-table there are comments from dependents and teachers. Assuming the primary keys are those given in your ERM there would be intransitive dependencies (dependent -->> comments, teacher -->> comments), thus 3NF not fulfilled. Therfore I would decompose comment table into two: teacherscomment and dependentscomment.

Third, from the viewpoint of relational theory (Codd, 1NF-3NF etc) and what students usually learn in database course at college/university surrogate/artifical primary keys those replacing the keys which define the many-to-many relationships are not correct.

Such surrogate keys should be replaced by natural primary keys as for example in table activity: This table originates from student many-to-many teacher. Therefore primary key of activity must be (studentID, teacherID). If such a pair has more than one content value associate, a third attribute, e.g. date/time the content was generated (your create_at column) should be added to pk.

One may interpose using compound keys would wast storage and time what might be simply …

Hi

google docs includes an API which can be used for example from Java. The API offers complete document search facilities. There are various query classes, also for spreadsheets. You may dive into the details here. Then the form to enter data can be build with Java. However,google offers also GWT to compile Java to Javascript.

I am also strongly interested in google docs, and a college of my own is completely enthusiastic of it.

I myself have some doubts about what will happen with the documents once stored on google's site. Possibly they will get indexed instantly and google will pelt one with tons of endless ads.

-- tesu

You should replace primary key of STORE by only storeID. Then the reference in PURCHASE must be FOREIGN KEY(StoreID) REFERENCES STORE(StoreID).

Thought there is no plausible reason that storeName is part of primary key in STORE, if you stick to this pk(StoreID,storeName), you must define the full reference in PURCHASE: FOREIGN KEY(StoreID, storeName) REFERENCES STORE(StoreID,storeName)

-- tesu

Ah, I see ! A not that negligible piece of information slided in.

** deleted **

Why not using the correct reference:

CONSTRAINT StoreFK FOREIGN KEY(StoreID) REFERENCES STORE(StoreID)

Also:

StoreName	CHAR(20)	NOT NULL,

should be:

StoreID		INTEGER		NOT NULL

Just out of curiosity: Why didn't you tell in your very first posting that the problem is table PURCHASE ?

-- tesu

Hi,

Instead of:

CREATE TABLE STORE (
	StoreID		Integer		NOT NULL AUTO_INCREMENT,
	StoreName	Char (20)	NOT NULL,
	City		Char (12)	NULL,
	Country		Char (15)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (20)	NULL,
	Contact		Char (15)	NULL,
	CONSTRAINT	StorePK		PRIMARY KEY(StoreID, StoreName),
);

try this:

CREATE TABLE STORE (
	StoreID		Integer		NOT NULL AUTO_INCREMENT,
	StoreName	Char (20)	NOT NULL,
	City		Char (12)	NULL,
	Country		Char (15)	NULL,
	Phone		Char (12)	NULL,
	Fax		Char (12)	NULL,
	Email		Char (20)	NULL,
	Contact		Char (15)	NULL,
	PRIMARY KEY(StoreID, StoreName)
);

What is the result? (isn't constraint restricted to foreign keys only?)

Btw, how come that you also put StoreName in primary key? Isn't StoreID already uniquely unique ?

-- tesu

no, I meant the "=" in your statement: myList[placeholder] = number;

** deleted ** (i miss-understood you)

-- tesu

Hi
Shall only whole numbers be counted as: myList[placeholder] = number; (if so, then change 0 into +=). Or shall each digit of an inputed number be counted, e.g. input 33 here 3 counts twice.

>> Andreas5: deleted, i got it, sorry
-- tesu

Now I have got some better understanding of your problem. I am supposing you are using adodb. So why not populating your dropdown explicitly, in principle:

1. create an adodb recordset object
2. create your select statement to get the ProductName from db, order doesn´t matter.
3. connect and open select
************
4. populate dropdown with your existing ProductName, makes first row  on top of dropdown 
************
5. Populate dropdown in while-loop with other ProductName from products (now I do understand 
   why you made the UNION, first predicate with =, 2nd predicate with != )

do while not eof
  here populate dropdown list with ProductName from table products 
  excluding already existing ProductName (simple if) 
  move next
loop

Hopefully, that your wanting: "I need a way to have it return the existing ProductName that is in use currently, and THEN list the remaining products below." could be fulfilled, at least above pseudo code may inspire you a little.

(I don't know ASP, however I did some adodb programming a couple of years ago)

-- tesu

Just think of alpha-numeric phone numbers such as 867-5309/Jenny. Therefore always char-type without any restriction. I suggest varchar(12). There is no minus when searching for alpha-numeric values.

-- tesu

hi

Because of many-to-many relationship you need a linking table:

create table employee_college(employee_id, college_id, ... here you can add further data
e.g. date of graduation etc);

where pk is (employee_id, college_id), both are also foreign keys.

Hint: I myself name foreign keys of child tables and primary keys of father tables always equally.
Advantage: joins are very simple to construct without any join constraint in where-clause or ON conditions.

As for the 1 to many relationship between college and state: Foreign key state_name in table college already defines this 1-to-many. You should define this foreign key explicitly:

create table college (....state_name char(3)... foreign key (state_name) references states_table on delete RESTRICT); -- restrict is default, therefore on-delete clause can be omitted.

Don't use this "on delete CASCADE" that much (otherwise your children would be killed automatically if one of their fathers dies).

-- tesu

hi

the server mysqld.exe is usually installed as a service on MS XP. You can check it:

Start > run > enter: services.msc

Examine sevice mysqld whether it is started. If not start it.

If already started and error still exists, examine service mysqld again, then click right mouse key on it to see its properties tabs. Here click on LOG ON tab and check mark for Allow Service To Interact With Desktop.

Hopefully this will help.

-- tesu

What is the meaning of review_type in third schema ?

Now that you have also shown schema last_review which has just 5 attributes, it is obviously that the 3rd schema is more suitable, especially if there would be only an 1 to 1 relationship of employee with last_review.

I thought column review_type were to identify various sorts of last-review information. But this is obviously wrong.

-- tesu