Posts by tesuji

Hello,

there is a wrong primary key reference in your code, examine red-coloured lines:

USE streamlibrary;

DROP TABLE IF EXISTS streamlibrary.FileProperties ;

CREATE  TABLE IF NOT EXISTS streamlibrary.FileProperties(
  PK_fileName VARCHAR(50) NOT NULL,
  extension VARCHAR(4) NOT NULL ,
  sizeMB DECIMAL(3) NOT NULL ,
  location VARCHAR(50) NOT NULL ,
  data VARCHAR(45) NOT NULL ,
  name VARCHAR(45) NOT NULL ,
  PRIMARY KEY (PK_fileName)
  );

-- -----------------------------------------------------
-- Table streamlibrary.AudioCodec
-- -----------------------------------------------------
DROP TABLE IF EXISTS streamlibrary.AudioCodec;

CREATE  TABLE IF NOT EXISTS streamlibrary.AudioCodec(
  PK_audioCodecID INT NOT NULL AUTO_INCREMENT,
  format VARCHAR(20) NOT NULL,
  bitRate INT NOT NULL,
  PRIMARY KEY (PK_audioCodecID)
  );
  


-- -----------------------------------------------------
-- Table streamlibrary.Audio
-- -----------------------------------------------------
DROP TABLE IF EXISTS streamlibrary.Audio;

CREATE  TABLE IF NOT EXISTS streamlibrary.Audio(
  PK_audioID INT NOT NULL AUTO_INCREMENT ,
  fileName VARCHAR(50) NOT NULL ,
  codec INT NOT NULL ,
  sampleRate VARCHAR(4) NOT NULL ,
  channel VARCHAR(45) NOT NULL ,
  content VARCHAR(45) NOT NULL,
  PRIMARY KEY (PK_audioID),
  FOREIGN KEY (fileName) REFERENCES FileProperties(fileName),
  FOREIGN KEY (codec) REFERENCES AudioCodec(PK_audioCodecID)
  );

Be sure that your database engine is always innodb. If not, foreign keys are entirely useless.

Btw, if there exists a many-to-many relationship between FileProperties and AudioCodec, then primary key of Audio, which obviously is the linking table to making up this relationship, must be at least (PK_fileName, codec). Sometimes this pair of column names is not unique, therefore "at least", additional column must be added to pk. If there are doubts on my suggestion, ask me.

-- tesu

I can't figure out the backgrounds to the extent, so just out of curiosity, why do you using "left outer joins" everywhere? I am sure, you already know this definition of left outer join:

/* [I]The result of a left outer join (or simply left join) for table A and B always contains all records of the "left" table (A), even if the join-condition does not find any matching record in the "right" table (B). 

This means that if the ON clause matches 0 (zero) records in B, the join will still return a row in the result—but with NULL in each column from B. This means that a left outer join  returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). 

If the left table returns one row and the right table returns more than one matching row for it, the values in the left table will be repeated for each distinct row on the right table.[/I] */

If there aren't important reasons, I myself would also examine the result of simple "inner joins".

-- tesu

The query you are trying to run is invalid
Mysql Error Output: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') OR a.addl_category_id IN ())' at line 2
SQL Query: SELECT count(*) AS count_rows FROM probid_auctions a WHERE a.active=1 AND a.approved=1 AND a.closed=0 AND a.deleted=0 AND a.list_in!='store' AND a.catfeat='1' AND (a.category_id IN () OR a.addl_category_id IN ())

Sorry, you posted the above code. Mysql said "You have an error in your SQL syntax;". Then the erroneous select statement follows. Moreover, it points to the error: ') OR a.addl_category_id IN ())' at line 2.

And indeed, the parameter list of IN operator is empty. This is a severe error. This error doesn't have anything to do with the dump file you might have gotten to insert your data in database.

This error has been initiated by your website. When you click categories, assumingly a combo or list box, the above sql statement is dynamically generated and sent to your database for executing through Mysql. Mysql rejects execution because of empty IN operator parameter list. Again, the condition (a.category_id IN () OR a.addl_category_id IN ()) is wrong. A correct condition should look like (a.category_id IN (10,20) OR a.addl_category_id IN (1,2,3, etc)). This is a mere example. Obviously, the php program behind your website does not fill in data (e.g. 10,20 or 1,2,3 etc) correctly when clicking on categories for some reasons .

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

I suggest standard mysql connector c++. Here is a complete tutorial. There I wrote something about odbc interface. There is also a mysql connector/odbc interface.

-- tesu

Well, I am not sure whether you are mistaken about identity and uniqueidentifier.

See the difference:

-- scope_identity(), @@identity and ident_current() are dealing with identities as in:

create table t (id integer identity(1000, 1), m varchar(100))
insert into table t (m) values ('sequence number starting from 1000 incremented by 1');
select scope_identity('t') as "Last inserted identity number for table t is";

whereas

create table ui (id uniqueidentifier, m varchar(100))

greates in table ui a globally unique identifier (GUID) which cannot be managed with above mentioned functions/variable. There are some ways to access such generated GUID, as for example using transact sql:

declare @tt table (idtt uniqueidentifier);
insert into ui output inserted.id into @tt  values (newid(), 'This is a GUID!');
select idtt as "Last inserted GUID: " from @tt;
-- Instead of newid() which generates randomly distributed GUIDs you can also use newsequentialid() which generates sequential GUIDs.

Attention: This code not tested!

GUIDs (example: 70E2E8DE-500B-4630-B3CB-16A131D35CD1) are extremly rarely used for primary keys and foreign keys. GUID identifiers should never appear on human-being interfaces. For such purposes identities which usually have integer datatype are quite usual.

-- tesu

Hi. Is this line ""AND (a.category_id IN () OR a.addl_category_id IN ())"" supposed to be in the dump file?? I don't see it in there

Well, I understand. What do you think of reading your own text more carefully? This is what you posted in one day ago:

A Mysql error has occurred while running the script:

The query you are trying to run is invalid
Mysql Error Output: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') OR a.addl_category_id IN ())' at line 2
SQL Query: SELECT count(*) AS count_rows FROM probid_auctions a WHERE a.active=1 AND a.approved=1 AND a.closed=0 AND a.deleted=0 AND a.list_in!='store' AND a.catfeat='1' AND (a.category_id IN () OR a.addl_category_id IN ())

Please, if anyone knows what to do here, help me fix this problem.
Thanks in advance!

To save you from getting overworked by yourself I tried to expose the cryptic letters in question by highlighting them reddishly.

-- tesu

Hi,

...AND (a.category_id IN () OR a.addl_category_id IN ())

IN() can not be empty. It needs at least one element, for example: ..AND (a.category_id IN(10) OR a.addl_category_id IN(100, 200, 300)), datatype integer assumed.

-- tesu

There is another aspect to consider:

Your a/d converting result is 10 bits long but with some certainty your mc has 8 bit long registers or 8 bit data bus width (what is more likely).

Therefore, low part of analog result may fill bit 0 up to 7 of low result register. The remaining 3 bits may be stored in bit position 0,1 and 2 of high result register.

So depending on what you are to convert, the high result register will mostly contains zeros. You may test this by a/d converting of the maximum analog value.

If you need a whole number for sending it to LCD display you can put together the contains of high and low result register by SHIFT and AND/OR instructions: whole_number = high * 10H + low

-- tesu

write a program that will identify the longest substring in two given strings that is common to both.

Hi

LCS is a famous problem in programming. You my have a look at my posting there and follow the links you possibly will find solutions.

Be aware of that LCS isn't that easy to program. Poor solutions have time complexity of O(n^4), optimal solutions based on dynamic programming have O(n^2) or (O(mn)) in both time and storage.

-- tesu

Hi

Unfortunately, I don't know that microcontroller. However, there are things in common among mcs.

As for example, how do you make your program wait until a/d conversion is completed? Usually you will get the low a/d result first, later follows high result. Therefore you need to wait until conversion is completed, what for example can be signaled by an interrupt or be determined by polling a status register/channel.

I thing, posting in your assembly code previously programmed is good idea, so we can examine it.

-- 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

Explain explains that tables users and messages have both keys named username. Table comments has a key named page. Possibly this is also a foreign key related to username of table users in real world.

Indeed, myisam allows creation of foreign keys, unfortunately, it doesn't make use of them.

Maybe you can rename column page of comments into username and rebuilt index on that new column or you change over to innodb? Another idea: can you also put column page into primary key of comments? It doesn't matter whether this pk will then have more than one column.

Attention, renaming columns on improper data models (lacking of foreign keys) and on engine myisam is a very dangerous task for it could destroy your fairly intact referential integrity entirely.

-- tesu

You didn't tell your database to use eventually existing indexes.

Your first explain has kind of join constraint "messages.username = 'johndoe' AND users.username = 'johndoe'". Such similar thing is missing in second explain.

I would also suggest always to join tables in such a way:
...
FROM messages JOIN users ON messages.username = user.username
...

But be aware that username (e.g. 'johndoe') usually is not unique. If so, the join constraint of your first explain and others would generate wrong results when executing the queries.

So it is mostly a good idea defining convenient primary keys and foreign keys for every table what explicitly prevents from generating wrong joins. Additional profit: your database can easily find existing indexes (they are put automatically on every primary key column by your database, thus it knows them) to speed up table scan when joining tables. If you hide such keys from the database and the database determines that large joins cannot be computed without indexes, database is forced to generate temporary indexes every time join is executed, what is sheer waste.

-- 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

I should seriously observe the following to my proposed classtime table:

If pernb, the period number 1,2 etc, is unequivocally derived from dati, the timestamp column, then there would be a partial dependency dati ---> pernb what strictly speaking means that second normal form is violated. Well, seems to be kind of nit-pickery :D

I also came up with the almost interesting question on how to figure out which students are considered to be late riser. If we decide that students which have more than as 30% tardy time falling in first and second period be late riser, the following query might give us the crucial answer:

select c1.stuID, FirstName, LastName, 
 (select sum(tarti) from classtime c2 where c1.stuID = c2.stuID) as TotalTardy, 
 (select sum(tarti) from classtime c3 where c1.stuID = c3.stuID 
     AND ((c3.pernb=1) or (c3.pernb=2))) as Period_1_and_2,
 case  TotalTardy
    when TotalTardy > 0 then cast(100*Period_1_and_2/TotalTardy as decimal(5,1))
    else 0
 end as inPercent 
 from classtime c1 join student s1 on c1.stuID = s1.stuID
    where inPercent > 30 order by inPercent, TotalTardy desc;

Attention: this code not tested ;)

How to find that out by the inital classtime table?

-- tesu

Hi,

student's table is OK (see note below) but classtime table is a bit poor (for theory lovers: it doesnt even fulfill first normal form, 1NF).

You can simply try out whether this table functions well or badly by creating a sql query (select) to answering this question: How often was student Taichy56 too late in class and how many minutes was he absent? Well, how could such query select look like?

A better idea for this table could be:

classtime
---------
stuID   int          -- student's number
dati    timestamp    -- the date/time the student was late
pernb   int          -- 1, 2, 3 ... your period number
tarti   integer      -- how much minutes too late

Primary key: (stuID, dati)
Foreign key: stuID references Student

Above table looks somewhat complicated but is ideal for evaluating by sql queries (even it fulfills 1NF up to 3NF).

This is how my answer to the differencing good/bad question would look like:

select FirstName, LastName, count(*) as HowOften, sum(tarti) as TartiMinutes
 from Student s join Classtime c on s.stuID = c.stuID 
   where c.stuID = stuID_of_Taichi56 [B]group by[/B] FirstName, LastName;

Note: If unique, primary key of Student's table should be StuID as you already stated. Therefore the never-missing "ID" is useless and should be dropped.

-- 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

Ah, he is definitely a She. So I say sorry for having mistaken he and she. OK, you did make those two line changes, so you did also add missing fabs() twice? However, there is definitely more to change, for example lines 186&204: potentially endless loops.

Can you explain what solve_b and solve_x are supposed to do?

What I am missing completely in your code is where you calculate the LU decomposition (lines 31to 34 of my code) and where you solve the equation system now being in triangular form where you get the solution vector from X(n), X(n-1), ...X(1) by way of back substitution, see lines 38 to 42.

-- tesu

@mike_2000_17, I already assumed that this offside vector is stored in n+1-th column, see my posting. If so, he should replace

---> for(int m = 0; m < N; ++m)

by

---> for(int m = 0; m <= N; ++m)

to swap RHS vector elements also.

As for posting complete code, I believe you are partly right. He did almost already solve the complete Gaussian elimimation algorithm, so my hidden agenda was to show him some errorless code which he might compare with his almost complete solution to figure out some essential flaws he made. I hadn't believe that he would have replaced his fine code by mine.

-- tesu

Hi vavazoom

the only thing what immediately leaped out at me is the line where you determine pivot element:

if (C[i][j] >= C[k][j]) ...

// what should be:

if ([B]fabs[/B](C[i][j]) >= [B]fabs[/B](C[k][j])) ...

More serious is where you are exchanging rows:

for(int m = 0; m < N; ++m)
{
temp = C[start][m];
C[start][m] = C[p][m];
C[p][m] = temp;
}

After this loop right side vector elements r[start] of your LEQ Ax=r MUST also be exchanged, for example:

temp = r [ start ]; r [ start ] = r [ p ]; r [ p ] = temp;
where r is right side vector, see my following code.

Because I didn't find this right side vector in your code you may have stored it together with nxn matrix in an nx(n+1) matrix, where column (n+1) contains this vecor.

Below is a method which is taken from a larger matrix class container I wrote.

This method does exactely what are you want to do: determine pivot element, exchanges pivot row (in both nxn matrix and right side vector r), calculates triangular matrix and finally solves Ax=r.

If you are into your code, the kernel algorithm of my method is easily understood. Only consider that in my matrix class array indexes are denoted by (), e.g. conventional C++ a[i,j] is a(i,j) in my class. Then I decided () instead of [] for strictly discriminating between c++ arrays and my own class arrays …

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

Yes, your version is less error prone than strcat() and more gallant:

char sqls[200];
sprintf(sqls, "SELECT * FROM table_name WHERE lname = '%s' and fname = '%s';", lname, fname);

(You live and learn, thx :))

-- tesu

lets say for example I have the following code

char *fname = "John";
char *lname = "Doe";
char *query = "SELECT fname, lname FROM table_name;"

The last statement won't work, so my question is how to pass the fname and lname variables to the query?

** deleted ** (got it wrong)

I also prefer Ancient Dragon's suggestion much more than columm names John Doe.

"SELECT * FROM table_name WHERE lname = 'Doe' and fname = 'John'"

As for pure C you can create such sql string by concating char-arrays, for example:

sqls = (char *) malloc (200);  // some mem first
sqls[0] = '\0';                // don't forget this
strcat(sqls, "SELECT * FROM yourTable WHERE fname =   ");
strcat(sqls, fname);
strcat(sqls, "   AND lname =  ");
strcat(sqls, lname);
strcat(sqls, "  ; ");

Do you already know how to send this select string to database and receiving some data?

-- tesu

ALTER PROCEDURE [dbo].[spAdminChangePassword]
(
@userName varchar(20),
@oldPass varchar(20),
@newPass varchar(20),

@ok int  OUTPUT

)

and drop declare @ok int;

Hope, that it will work.

-- tesu

(I am really astonished that over 15-year old transact SQL from Sybase company is still alive in modern Microsoft SQL Server today.)

Hello

I was also able to find the answer to Napoleon Bonaparte most impotant statement by means of this code:

int palindrom(char s[]){
int i,l;for(l=0;s[l]!='\0';l++);
for(i=0;i<l/2;i++) if(s[i]!=s[l-i-1])return(0);
return(1);}

// usage int main():
   if (palindrom("able was I ere I saw elba")) printf("Napoleon was right!\n");
     else printf("Wrong, wasn't he banned to Santa Helena!\n");

-- tesu

Hello

How should your letter sequences be read? Does "no" belongs to "name" so as "nonname" or is it a single column's name? What are "sub" and "table" ? Maybe these separated words are name of many-to-many linking table? Are there any keys?

Most Important: All your four anwsers can easily be found by way of sub-selects, in principle:

select t.this, o.and, y.that from there t  join overthere o join yonder y  -- the many-to-many inner join
  where theunknown ??which_predicate?? (select ... yes, i am a subselect ...);

This select only works fine, if you know your primary and foreign keys, furthermore, they must be defined correctly. If you don't know, you have to state them explicitely by ON clause for every table in FROM-clause, or if this sort of modern joins be not allowed, you have to put join constraints in WHERE clause, traditionally.

Getting these hints (ok, you also need to know where to place aggregate function avg(?) and how to build a perfect GROUP BY clause), it should be really easy for you to find the appropriate answers. You may post in what you will have found then.

-- 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

Despite whether your below select statement meets your requirments, it has a severe fault:

SELECT city.name, COUNT(flight.id) AS numflight
->FROM city LEFT JOIN flight ON origincityid=origincityid.id
->GROUP BY destinationcityid;

That is, all selected columns not being parameters of aggregate functions (e.g. count) must be enumerated in GROUP BY clause no matter mysql manuals are alleging the opposite. Therefore your GROUP BY clause should be: GROUP BY city.name (everything else is comparing apples and oranges).

Ah, what about your former thread "Trying to join 2 tables in mysql-Help", really solved ?

-- tesu

Hi

your indirect address modes are wrong. Unfortunately, you won't get any error message. That's life in assembly programming.

To fast show you the problem in principle I copied your assembly code inline a C program:

void myFunc(float *a, float *b, float *result)   ; *myFunc not necessary but ok
{
__asm {
        mov edi, a;          ; <--- mov via 32bit reg only!
	movups xmm0, [edi]   ; <--- indirect adress mode [a] not allowed!

        mov edi, b;
        movups xmm1, [edi]   ; <--- dito, indirect adress mode [b] not allowed!

        addps xmm0, xmm1

        mov edi, result
        movups [edi], xmm0   ; <--- dito, indirect adress mode [result] not allowed! 
      }
}
int main(int argc, char *argv[])
{
	float inA[] = {1.0f, 2.0f, 3.0f, 4.0f};
	float inB[] = {1.0f, 2.0f, 3.0f, 4.0f};
	float ret[4];

	myFunc(inA, inB, ret);


	cout << ret[0] << endl; // only first element of vector is calculated
        // result is
        // 2

	//cout << ret[1] << endl;  
	//cout << ret[2] << endl;
	//cout << ret[3] << endl;

	system("pause");
	
	return 0;
}

Now it's your turn to program the loop for adding all four elements of the vectors.

-- tesu

let me explain u clearly..
I have a table name product Details I have Pid as a field.
then another table name is product order so if i update my Pid in product details simultanoeusly in product order also have pid,it should be also updated...how to do that..hope its clear for u now?

This objective can easily be achieved by the table's definitions. You should make use of properly defined foreign keys, as for example:

create table productdetails(pid integer not null, description varchar(100), ...further data..., 
primary key(pid));

create table productorder (shouldhaveprimarykeytoo integer not null, pid integer, 
...further data...,
primary key(shouldhaveprimarykeytoo),
[B]foreign key (pid) references productdetails(pid) [U]ON UPDATE CASCADE[/U][/B]);

Now if pid changes in master table productdetails its reference in table productorder will automatically be changed due to update policy CASCADE.

Well, as already stated, changing values of primary keys is a severe issue and should not be daily business for it requires such dangerous policies as cascade which can easily destoy data consistence of a whole database.

-- tesu

Can SERIAL_NUM be assigend to different RMA_NUM in PRODUCT_Table ?

If answer is yes, you should decompose your existing tables into:

ORDER_Table(RMA_NUM, CUSTOMER, ...)

PRODUCT_Table_unique (SERIAL_NUM, TYPE, MODEL_NUM, ..)

PRODUCT_Table (RMA_NUM, SERIAL_NUM, Quantity?, ...)

Where underlined columns denote primary keys. Now 2NF and 3NF seems to be fulfilled.

If the answer is no, there is nothing to do for PRODUCT_Table is already in 3NF
reason: there is no transitive dependency. A transitive dependency would exist for example if for each ALPHA in non-key column TYPE the values in associate non-key column MODEL_NUM would be the same. This is not true, e.g. ALPHA --> ALPH-1000 and ALPHA --> ALPH-2000, therefore TYPE -|-> MODEL_NUM, thus no transitive dependency. Therefore 3NF complies.

-- tesu

History:

operation_id  	int(6)
data 	timestamp
amount 	float(7,2)
customer_id 	varchar(6)
debt_id 	int(6)
optype tinyint(1)
PRIMARY_KEY(operation_id)

How can I select the date of the most recent record in "history" for each "debt_id"?
Thanks

Hi trashed

If you are only interested in getting data and for example debt_id of the most recent rows from your second table History, your can simply do this by way of correlated selects, as for example:

select a.data, a.debt_id  from History a
  where a.data  = (select max(b.data) from History b where b.debt_id = a.debt_id) order by a.data, a.debt_id;

(This only functions if debt_id is not a unique key of table History, what is obviously true because operation_id is the only unique key of this table by definition.)

-- tesu

Please state all your tables and give them clear names. Is there a one-to-many relationship between the order table and the above table (what name?) ? On the other side there seems to be also a product table. If so, there could be a many-to-one exists between product table and the above table (meaninful name for that supposed linking table then could be orderproduct).

Then you would have: ORDER ---< orderproduct >--- PRODUCT, where the primary key of linking table orderproduct could be pk(rma_num, product_num). (---< stands for one-to-many)

-- tesu

Hi,

>>> RMA_NUM is the primary key in both tables.

This is not true because there are duplicates in RMA_NUM (1,1,1, etc).

So what is your primary key ?

(As for 3NF you need to check whether transitive dependencies exist. However, pk must be given priorly.)

-- tesu

Almost forgotten:

>> "Third is the intersecting table: EponymCategory : ID, EponymID<FK>, CategoryID<FK>"

The primary key of this table which makes up the many-to-many relationship must be at least (EponymID,CategoryID). If not, many-to-many relationship would be destroyed. There is no further ID here! This is an example on how to create it:

create table EponymCategory (EponymID integer not null, CategoryID integer not null,
  primary key (EponymID,CategoryID),
  foreign key (EponymID) references Eponyms(EponymID),
  foreign key (CategoryID) references Categories(CategoryID)
);

For rare situations pk(EponymID,CategoryID) appears to be not not unique. If so, we shall discuss it in detail.

-- tesu

Hi

In principle, the trigger you imagine may look like this:

CREATE TRIGGER InsertIntoEponyms AFTER INSERT ON Eponyms
  REFERENCING OLD AS oldRow NEW AS newRow FOR EACH ROW
BEGIN
  DECLARE Eponyms_CATEGORIES varchar(255);
  -- further declarations ...
  set Eponyms_CATEGORIES = newRow.CATEGORIES; -- get current value of Eponyms.Categories
  /*
  Here should be some code to process Eponyms_CATEGORIES:
  1. parsing Eponyms_CATEGORIE to get all tokens e.g. 'PSYCH', 'CHEM', 'BIO' etc 
  2. Looking up table Categories whether the tolkens already exist there
  3. If not, insert them into table Categories
  4. Finally insert linking data into EponymCategory, to link Eponyms with Categories
  */
END;

Sure, the sketched steps 1 to 4 can be programmed. The serious problem I see is CATEGORIES of Eponyms where you encode data like 'PSYCH CHEM BIO'. Its length and its contains may vary from row to row. So it isn't that easy to decode such string and use its result in sql statements efficiently. You may have a look at my last posting where we have similar decoding problems.

I believe that you could significantly simplify your problem if you improve your data modell (more precisely: simply drop Eponyms.CATEGORIES). I am rather sure that you don't need such a decoding task neither a trigger to carry out the tasks you explained.

I will construct an example to explain this more detailed (can post it Tuesday afternoon). In the meantime I suggest to study the following thoroughly:

There is a true many-to-many relationship …

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

I am sorry to have kept you waiting. (I am a bit under stress)

The older code I posted should only show how to solve the problem in principle. Therfore it was rather incomplete.

Here is the complete code of function ParseAndReplace. Consider that all local variables must be declared first. Also this code is for Sybase and MS SQL server, you need to check these functions: length, INSERTSTR, substr, locate whether they exist - and important too - if they exist, carefully check the parameter lists for mysql has its very own order of parameters in standard functions. cast(..) function is standard sql, which is supported by mysql.

I plan to program a generic ParseAndReplace function for such thing is often required.

create function ParseAndReplace (in vName varchar(50)) returns varchar(255) 
begin
declare i integer;
declare vSubject varchar(255);
declare vsubjectName varchar(255);
declare vsubjectNameConcat varchar(255);
declare vSubjectLength integer;
declare comma integer;
declare vID integer;
set i = 0;
select subject into vSubject from user_subject where name = vName;
set vSubjectLength = length(vSubject)+1;
set vSubject = INSERTSTR(vSubjectLength, vSubject, ',');  
set vsubjectNameConcat = '';
while i < vSubjectLength loop
  set comma = locate (vSubject, ',', i);
  set vID = cast(substr(vSubject, i, comma-i) as integer);  
  select subjectName into vsubjectName from subject where id = vID;
  if ( vsubjectNameConcat = '' ) then set vsubjectNameConcat = vsubjectName; 
   else set vsubjectNameConcat = vsubjectNameConcat || ', ' || vsubjectName;
  end if;
  set i = comma+1; 
end loop;
return vsubjectNameConcat;
end;

I hope this …

Ah, I see. So you own two accounts here?

Sorry, I don't understand what you meant. Pls, can you rewrite again.

-- tesu

Btw, did I meet your conception of two one-to-many relationships?

Hi

I am glad to hearing of you again. It's nice to see the changes in your script, especiallly tables store and purchase now delight.

As for error around line 57, there could be 2 reasons

1. I don't know such delete policy: ON DELETE NO CASCADE, so obviously mysql don't

or

2. Sometimes error 121 occours if the name of a constraint is not unique on whole database. This is a namespace problem in sql databases. Therefore, duplicate constraint names are not allowed.

-- tesu

Yes, I was afraid of that. Based on your data model the result you endeavour to cannot be achieved by way of simple select statement (there is no loop statement for select to-day, yet there are case and if). There are two solutions:

1. Do it in any programming language, e.g. php, java, c, c++ ... using a proper sql api

or

2. Improve your data model (try to archieve 3NF)
(repetition column is a nice thing which is responsible for that table events even didn't meet 1NF)

-- tesu

As StuXYZ already suggested, drop these lines:

10. do
11. {
22. }
23. while (true); // endless loop

Hi,

assuming your data model is correct an owner's event list can be created by

select * from event WHERE owner = 'yourOwner';

* should be avoided and replaced by the list of row names you actually need,

e.g.

select event_from, event_to, description from event WHERE owner = 'yourOwner';

Hi

I don't have octave experience, I am using matlab. But I know there is a octave library for c/c++. Maybe this small tutorial helps a little. There is a further advanced tutorial.

-- 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

Hi,

well, two one-to-many relationships of two entities where each entity is related to the other one usually makes up a many-to-many relationship between them.

There is also a possibility that both one-to-many relationships are treated separately. In such case the primary key of one entity becomes foreign key of the other entity. To express a strict one-to-many relationship these foreign keys are non-identifying that is, they are not part of the another entity's primary key.

The attached ERM shows such a relationship.

On a relational database the tables could be created as follows:

create table A_Entity(aID int not null, adata char, bfk int, primary key (aID));

create table B_Entity(bID int not null, bdata char, afk int, primary key (bID), foreign key afk references A_Entity);

alter table A_Entity add foreign key bfk references B_Entity;

One should consider that alter table is necessary because of the recursive foreign key references. There is no forward declaration on SQL. I made use of standard SQL/99 alter-statement. Depending on your database alter-statement may vary.

-- tesu