select statement

[djl3779]

Hello,

I'm new to this. I'm trying to build a print quota system from data in several mysql tables.

The important tables and columns are:

table1: job_id user_name
table2: job_id sheets_printed
table3: user_name total_sheets_printed

I wanted to make a statement that would UPDATE table3's total_sheets_printed by table2's sheets_printed if table1's user_name is equal to table3's username.
(The job_id's in table1 and table2 will match up.)

A big sticking point is that the user_name column in table1 is in the form (username@server). I'd like to chop that @server off temporarily for comparison between table3 and table1 (table1's user_name is simply the username).

Also, I'd like the statement to INSERT a new record if a user printed paper, but did not exist in table3.

BTW, I hacked together a script in perl to do all this, but I'd like to know if this is possible to do with sql statements.

Thanks,
Danny

[Joecool]

First thing I would do is normalize your data. It decreases total space needed for the database and makes queries quicker.

Table1:UserID, UserName, TotalSheetsPrinted
Table2:JobID, UserID, SheetsPrinted

I do not see a reason to have seperate tables for user_name and total_sheets_printed.

I will add to this later, at work right now.

Joe

[djl3779]

Unfortunately, just normalizing the tables isn't that straightforward.

Markvision 10 (the printer management software) creates the majority of the tables. This software is closed. The only leeway I have is with the 3rd table or any temporary tables.