-
Records in a Date Range
Date: 03/06/06 Keywords: no keywords
I am trying to select records in a date range, but with this query I am not having any records returned. When I remove the BETWEEN statement and dont limit the range, I get results. Dates are yyyy-mm-dd format.
SELECT invoice_items.caregiver_id, sum(((TIME_TO_SEC(invoice_items.end_time) - TIME_TO_SEC(invoice_items.start_time))/3600)) AS invoice_hours, employees.EmployeeID, employees.LastName, employees.FirstName FROM invoice_items LEFT JOIN employees ON invoice_items.caregiver_id=employees.EmployeeID WHERE invoice_items.office_id ='$office_id' AND invoice_items.item_date BETWEEN $ServRange1 AND $ServRange2 GROUP BY invoice_items.caregiver_id ORDER BY employees.LastName
-
HELP
Date: 03/05/06 Keywords: database, sql
So i'm in a sql class and i have no idea what i'm doing. *yikes* I need to...
I'd like to have at least five transactions, with at least two
different movies rented each, and one where they bring back a movie
(RL_INOUT).
You have to decide:
1. which employees are going to make those transactions
2. Which customers are going to rent the movies/games
3. Which movies those customers are going to rent.
Yeah... I'm LOST!
Our current table structure
Here's what I've got for our database structure so far. Discuss here!
Here are the tables (called out by SELECT * FROM TABLE_NAME and DESC TABLE_NAME)
ACCT:
Code:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| ACCT_ADDRESS | char(50) | YES | | NULL | |
| CITY | char(50) | YES | | NULL | |
| ACCT_STATE | char(2) | YES | | NULL | |
| ACCT_ZIP | decimal(5,0) | YES | | NULL | |
| ACCT_DEBT | decimal(6,2) | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
+------------+------------------+-----------------+------------+----------+----- ------+
| ACCT_ID | ACCT_ADDRESS | CITY | ACCT_STATE | ACCT_ZIP | ACCT_DEBT |
+------------+------------------+-----------------+------------+----------+----- ------+
| 8132546892 | 85916 FOXHILL | HAMTRAMMICK | MI | 48325 | 15.50 |
| 8217394659 | 74985 BELINDA | CLINTON TWP | MI | 48310 | 0.00 |
| 8221354897 | 28486 STAPLER | SPINDLE | MI | 48310 | 15.00 |
| 8231645797 | 78456 GARFIELD | LAWSON | MI | 47158 | 25.00 |
| 8312584695 | 3125 CHARITY | LIVONIA | MI | 48310 | 0.00 |
| 8574962138 | 3131 ALBANY | LANSING | MI | 48317 | 2.50 |
| 8579462312 | 28547 RUTHER | DEARBORNE | MI | 48315 | 5.00 |
| 8779654251 | 25816 GEORGIA | KNOB CREEK | MI | 47861 | 0.00 |
| 8796452135 | 25781 SOMESTREET | FAIRFIELD | MI | 48631 | 7.50 |
| 8896354187 | 85749 CHECKERS | TIMBUCKTWO | MI | 48156 | 0.00 |
| 8899775521 | 45612 LAYFETTE | MOUND | MI | 48356 | 5.00 |
| 8974653127 | 31582 RYAN | MADISON HEIGHTS | MI | 48197 | 0.00 |
| 8976542315 | 95846 MARK ORR | ROYAL OAK | MI | 48312 | 5.00 |
| 8995542368 | 21058 SALEM | TROY | MI | 48213 | 7.50 |
| 9478652351 | 20487 PINEBROOK | ALLEN PARK | MI | 48321 | 10.50 |
+------------+------------------+-----------------+------------+----------+----- ------+
15 rows in set (0.00 sec)
CUSTOMER:
Code:
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ACCT_ID | char(10) | | PRI | | |
| CUST_NUM | decimal(2,0) | | PRI | 0 | |
| CUST_FIRST_N | char(15) | YES | | NULL | |
| CUST_LAST_N | char(15) | YES | | NULL | |
| CUST_BDAY | date | YES | | NULL | |
+--------------+--------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
+------------+----------+--------------+-------------+------------+
| ACCT_ID | CUST_NUM | CUST_FIRST_N | CUST_LAST_N | CUST_BDAY |
+------------+----------+--------------+-------------+------------+
| 8132546892 | 1 | MELISSA | LEMMINS | 1950-02-18 |
| 8217394659 | 1 | DESRA | JACKSON | 1981-01-27 |
| 8221354897 | 1 | JUSTIN | MARLBORO | 1987-06-25 |
| 8231645797 | 1 | SARAH | HOPPINS | 1980-08-26 |
| 8312584695 | 1 | LARRY | BROWN | 1968-03-22 |
| 8574962138 | 1 | SUE | EVANS | 1983-03-17 |
| 8579462312 | 1 | JAMES | RAUSCH | 1956-01-16 |
| 8779654251 | 1 | DAN | COOLMEN | 1985-01-08 |
| 8796452135 | 1 | BOB | RAMSEY | 1975-02-14 |
| 8896354187 | 1 | ANDREA | TYLER | 1985-07-13 |
| 8899775521 | 1 | CHRIS | JOANS | 1984-11-02 |
| 8974653127 | 1 | TIFFANY | SCHULTZ | 1985-07-25 |
| 8976542315 | 1 | ROBBY | SMITH | 1982-04-23 |
| 8995542368 | 1 | JESSE | SAAD | 1973-04-20 |
| 8995542368 | 2 | MARY | SAAD | 1976-03-24 |
| 9478652351 | 1 | JOANN | TIMBERSHMIT | 1973-05-18 |
+------------+----------+--------------+-------------+------------+
16 rows in set (0.01 sec)
INVENTORY:
Code:
+------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| INV_ID | char(12) | | PRI | | |
| INV_MEDIA | char(3) | YES | | NULL | |
| INV_TITLE | char(30) | YES | | NULL | |
| RT_TYPE | char(2) | YES | MUL | NULL | |
| RATE_CODE | char(4) | YES | MUL | NULL | |
| INV_COPIES | decimal(4,0) | YES | | NULL | |
| INV_ONHAND | decimal(4,0) | YES | | NULL | |
+------------+--------------+------+-----+---------+-------+
7 rows in set (0.00 sec)
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
| INV_ID | INV_MEDIA | INV_TITLE | RT_TYPE | RATE_CODE | INV_COPIES | INV_ONHAND |
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
| 012236189411 | DVD | WAITING... | NV | UR | 7 | 4 |
| 012569500921 | DVD | GONE WITH THE WIND | OV | G | 2 | 2 |
| 012569593237 | DVD | MILLION DOLLAR BABY | NV | UR | 2 | 0 |
| 013388200177 | GCN | RESIDENT EVIL 4 | NG | M | 4 | 1 |
| 014633147025 | PS2 | NEED FOR SPEED UNDERGROUND | OG | E | 3 | 2 |
| 024543044789 | DVD | FIGHT CLUB | OV | R | 5 | 2 |
| 025192211928 | DVD | CINDERELLA MAN | NV | PG13 | 8 | 3 |
| 043396067660 | DVD | BLACK HAWK DOWN | OV | R | 3 | 3 |
| 045496870461 | N64 | SUPER SMASH BROS | OG | E | 4 | 3 |
| 047875810211 | XBO | CALL OF DUTY 2: BIG RED ONE | NG | T | 4 | 0 |
| 085391773726 | DVD | THE MATRIX | OV | R | 3 | 2 |
| 097360184044 | DVD | CLUE | OV | G | 1 | 1 |
| 097363365143 | DVD | ALONG CAME A SPIDER | NV | R | 2 | 1 |
| 710425274107 | PS2 | GRAND THEFT AUTO: SAN ANDREAS | NG | AO | 3 | 2 |
| 786936161571 | DVD | PULP FICTION | OV | R | 3 | 3 |
| 788687500012 | PS2 | HITMAN 2: SILENT ASSASSIN | NG | M | 2 | 0 |
| 794043749728 | DVD | THE NOTEBOOK | NV | PG13 | 4 | 1 |
+--------------+-----------+-------------------------------+---------+---------- -+------------+------------+
17 rows in set (0.00 sec)
RATING:
Code:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| RATE_CODE | char(4) | | PRI | | |
| RATE_DESC | char(30) | YES | | NULL | |
| RATE_MINAGE | decimal(2,0) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
3 rows in set (0.00 sec)
+-----------+--------------------------+-------------+
| RATE_CODE | RATE_DESC | RATE_MINAGE |
+-----------+--------------------------+-------------+
| AO | Adults Only | 18 |
| E | Everyone | 1 |
| E10 | Everone 10+ | 10 |
| EC | Early Childhood | 1 |
| G | General Audiences | 1 |
| M | Mature | 17 |
| NC17 | Restricted 17 years | 17 |
| PG | Parental Guidence | 6 |
| PG13 | Strong Parental Guidence | 13 |
| R | Restricted | 17 |
| T | Teen | 13 |
| UR | Unrated | 21 |
| X | Mature Content | 21 |
+-----------+--------------------------+-------------+
13 rows in set (0.00 sec)
RENT_TYPE:
Code:
+-----------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| RT_TYPE | char(2) | | PRI | | |
| RT_DESC | char(30) | YES | | NULL | |
| RT_LENGTH | decimal(2,0) | YES | | NULL | |
| RT_FEE | decimal(4,2) | YES | | NULL | |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
+---------+-----------+-----------+--------+
| RT_TYPE | RT_DESC | RT_LENGTH | RT_FEE |
+---------+-----------+-----------+--------+
| NG | New Game | 5 | 5.00 |
| NV | New Video | 2 | 6.00 |
| OG | Old Game | 2 | 4.00 |
| OV | Old Video | 7 | 5.00 |
| S1 | Special 1 | 2 | 3.00 |
| S2 | Special 2 | 2 | 1.50 |
+---------+-----------+-----------+--------+
6 rows in set (0.00 sec)
EMPLOYEE:
Code:
+-------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+--------------+------+-----+---------+-------+
| EMP_ID | char(4) | | PRI | | |
| EMP_FIRST_N | char(15) | YES | | NULL | |
| EMP_LAST_N | char(15) | YES | | NULL | |
| EMP_ADDRESS | char(50) | YES | | NULL | |
| EMP_CITY | char(50) | YES | | NULL | |
| EMP_STATE | char(2) | YES | | NULL | |
| EMP_ZIP | decimal(5,0) | YES | | NULL | |
| EMP_WAGES | decimal(4,2) | YES | | NULL | |
+-------------+--------------+------+-----+---------+-------+
8 rows in set (0.00 sec)
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
| EMP_ID | EMP_FIRST_N | EMP_LAST_N | EMP_ADDRESS | EMP_CITY | EMP_STATE | EMP_ZIP | EMP_WAGES |
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
| 0001 | TIM | WOJOWSKI | 38592 WEST | MACOMB | MI | 48035 | 15.00 |
| 0002 | LARRY | HILLIARD | 29438 NORTH AVE | MT. CLEMENS | MI | 38940 | 9.70 |
| 0003 | WILLIAM | JOHNSON | 29438 GRATIOT | MT. CLEMENS | MI | 38940 | 9.50 |
| 0004 | ANTHONY | WALKER | 24882 FAIRCHILD | MACOMB | MI | 48042 | 9.50 |
| 0005 | GEORGE | TOMLIN | 28784 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
| 0006 | BOB | BATES | 28743 COTTON | CHESTERFIELD | MI | 48315 | 9.50 |
+--------+-------------+------------+-----------------+--------------+---------- -+---------+-----------+
6 rows in set (0.00 sec)
These tables do not have data yet!!
Code:
TRANSACTION:
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| ACCT_ID | char(10) | YES | MUL | NULL | |
| CUST_NUM | decimal(2,0) | YES | | NULL | |
| TRANS_DATE | date | YES | | NULL | |
| EMP_ID | char(4) | YES | MUL | NULL | |
+------------+---------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
RENT_LINE:
+-----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| TRANS_NUM | decimal(10,0) | | PRI | 0 | |
| INV_ID | char(10) | | PRI | | |
| RENT_QTY | decimal(2,0) | YES | | NULL | |
| RL_INOUT | char(3) | YES | | NULL | |
+-----------+---------------+------+-----+---------+-------+
4 rows in set (0.00 sec)
Following is all of the SQL code so far:
Code:
CREATE DATABASE BAKER_DVD;
USE BAKER_DVD;
CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) , -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address 1
EMP_CITY CHAR(50) , -- Address 2
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
PRIMARY KEY (EMP_ID) ) ENGINE=INNODB;
CREATE TABLE RATING
(RATE_CODE CHAR(04) , -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
PRIMARY KEY (RATE_CODE) ) ENGINE=INNODB;
CREATE TABLE ACCT
(ACCT_ID CHAR(10) , -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
CITY CHAR(50) , -- City of residence
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
PRIMARY KEY (ACCT_ID) ) ENGINE=INNODB;
CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) , -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
PRIMARY KEY (RT_TYPE) ) ENGINE=INNODB;
CREATE TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) , -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
PRIMARY KEY (ACCT_ID, CUST_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID) ) ENGINE=INNODB;
CREATE TABLE INVENTORY
(INV_ID CHAR(12) , -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating, (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
PRIMARY KEY (INV_ID),
FOREIGN KEY (RT_TYPE) REFERENCES RENT_TYPE(RT_TYPE),
FOREIGN KEY (RATE_CODE) REFERENCES RATING(RATE_CODE) ) ENGINE=INNODB;
CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10,0) , -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(02) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
PRIMARY KEY (TRANS_NUM),
FOREIGN KEY (ACCT_ID) REFERENCES ACCT(ACCT_ID),
FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE(EMP_ID) ) ENGINE=INNODB;
CREATE TABLE RENT_LINE
(TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_ID CHAR(10) , -- Relavant video code
RENT_QTY DECIMAL(02,0) , -- Quantity rented, usually one.
RL_INOUT CHAR(03) , -- Item In or Out status
PRIMARY KEY (TRANS_NUM, RENT_NUM),
FOREIGN KEY (TRANS_NUM) REFERENCES TRANSACTION(TRANS_NUM) ) ENGINE=INNODB;
Here's the data already entered:
Rent Type and Rating:
Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );
INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );
Inventory:
Code:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');
INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');
INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');
INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');
INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');
INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');
INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');
INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG13', '8', '3');
INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');
INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');
INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');
INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');
INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');
INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');
INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');
Customers and Accounts:
Code:
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');
INSERT INTO CUSTOMER
VALUES
('8574962138', 1, 'SUE', 'EVANS', '19830317');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'LARRY', 'SCHULTZ', '19810322');
INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');
INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');
INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');
INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');
INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');
INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');
INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');
INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');
INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');
INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');
INSERT INTO CUSTOMER
VALUES
('8995542368', 2, 'MARY', 'SAAD', '19760324');
INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');
INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');
INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');
INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');
INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI', '48197', 0);
INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315', '5.00');
INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');
INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310', 0);
INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');
INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');
INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');
INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310', 0);
INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');
INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861', 0);
INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');
INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');
INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156', 0);
Employee:
Code:
INSERT INTO EMPLOYEE
VALUES ('0001', 'TIM', 'WOJOWSKI', '38592 WEST', 'MACOMB', 'MI', '48035', '15.00');
INSERT INTO EMPLOYEE
VALUES ('0002', 'LARRY', 'HILLIARD', '29438 NORTH AVE', 'MT. CLEMENS', 'MI', '38940', '9.70');
INSERT INTO EMPLOYEE
VALUES ('0003', 'WILLIAM', 'JOHNSON', '29438 GRATIOT', 'MT. CLEMENS', 'MI', '38940', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0004', 'ANTHONY', 'WALKER', '24882 FAIRCHILD', 'MACOMB', 'MI', '48042', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0005', 'GEORGE', 'TOMLIN', '28784 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');
INSERT INTO EMPLOYEE
VALUES ('0006', 'BOB', 'BATES', '28743 COTTON', 'CHESTERFIELD', 'MI', '48315', '9.50');
Discuss here. (the text after the -- is a comment.)
Last edited by enigma.0Z on Fri Mar 03, 2006 4:14 am; edited 7 times in total
Tue Jan 31, 2006 10:44 am
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
ER Diagram is complete... I'll get started on the Inventory Table Today... if anybody needs to know any of the info, like the vid num code, let me know... ttyl homies...
Mon Feb 20, 2006 3:33 am
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
I slightly restructured a bit, but mostly with what the primary keys are. The modified tables are as follows:
CREATE DATABASE BAKER_DVD;
CREATE TABLE INVENTORY
(INV_VID_CODE CHAR(12) PRIMARY KEY, -- Video/Game ID, first 5 chars from title & one number
INV_MEDIA CHAR(03) , -- Media type, dvd, vhs, xbox, ps2, gcn, psp
INV_TITLE CHAR(30) , -- Media title. duh
RT_TYPE CHAR(02) , -- Renting Type (new release, game, old, etc)
RATE_CODE CHAR(04) , -- Video/Game rating (G, PG, PG13, R, NG17, EC, E, T, M, AO)
INV_COPIES DECIMAL(4,0) , -- Number of copies total
INV_ONHAND DECIMAL(4,0) , -- Number of copies on hand
);
CREATE TABLE RATING
(RATE_CODE CHAR(04) PRIMARY KEY, -- Video/Game Rating
RATE_DESC CHAR(30) , -- Rating Description
RATE_MINAGE DECIMAL(2) , -- Minimum Age to rent such an item
);
CREATE TABLE ACCT
(ACCT_ID CHAR(10) PRIMARY KEY, -- Account ID
ACCT_ADDRESS CHAR(50) , -- Address
ACCT_STATE CHAR(02) , -- State
ACCT_ZIP DECIMAL(5,0) , -- Zip
ACCT_DEBT DECIMAL(6,2) , -- Debt (late fees accumulated)
);
CREAT TABLE CUSTOMER
(ACCT_ID CHAR(10) , -- Account ID
CUST_NUM DECIMAL(02) PRIMARY KEY, -- Customer Number
CUST_FIRST_N CHAR(15) , -- First Name
CUST_LAST_N CHAR(15) , -- Last Name
CUST_BDAY DATE , -- Birth Day
);
CREATE TABLE RENT_TYPE
(RT_TYPE CHAR(02) PRIMARY KEY, -- Rent type, New, Old, Game, ETc.
RT_DESC CHAR(30) , -- Description of rent code
RT_LENGTH DECIMAL(2,0) , -- How long this item can be rented out before late
RT_FEE DECIMAL(4,2) , -- What the fee is if this item is late (per day)
);
CREATE TABLE RENT_LINE
(RENT_ID DECIMAL(10,0) PRIMARY KEY, -- ID Code for movie rental
TRANS_NUM DECIMAL(10,0) , -- Trans ID
INV_VID_CODE CHAR(10) , -- Relavant video code
RL_INOUT CHAR(03) , -- Item In or Out status
);
CREATE TABLE TRANSACTION
(TRANS_NUM DECIMAL(10) PRIMARY KEY, -- Trans ID, unique for every transaction
ACCT_ID CHAR(10) , -- Account that the transaction is on
CUST_NUM DECIMAL(04) , -- Customer who made the transaction
TRANS_DATE DATE , -- Date the transaction took place
EMP_ID CHAR(04) , -- Employee who made the transaction
);
CREATE TABLE EMPLOYEE
(EMP_ID CHAR(04) PRIMARY KEY, -- Employee ID
EMP_FIRST_N CHAR(15) , -- First Name
EMP_LAST_N CHAR(15) , -- Last Name
EMP_ADDRESS CHAR(50) , -- Address
EMP_STATE CHAR(02) , -- State
EMP_ZIP DECIMAL(5,0) , -- Zip Code
EMP_WAGES DECIMAL(4,2) , -- Wages (per hour)
);
let me know if any of this will cause any problems... hopefully not, because during my boredom, I had completed the data for 3 of the tables: INVENTORY, RATING, and RENT_TYPE. Hopefully nobody had their hearts set on those because they're done now. If you want the info for those tables, here are the codes:
INSERT INTO INVENTORY
VALUES
('043396067660', 'DVD', 'BLACK HAWK DOWN', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('024543044789', 'DVD', 'FIGHT CLUB', 'OV', 'R', '5', '2');
INSERT INTO INVENTORY
VALUES
('012569500921', 'DVD', 'GONE WITH THE WIND', 'OV', 'G', '2', '2');
INSERT INTO INVENTORY
VALUES
('085391773726', 'DVD', 'THE MATRIX', 'OV', 'R', '3', '2');
INSERT INTO INVENTORY
VALUES
('097363365143', 'DVD', 'ALONG CAME A SPIDER', 'NV', 'R', '2', '1');
INSERT INTO INVENTORY
VALUES
('097360184044', 'DVD', 'CLUE', 'OV', 'G', '1', '1');
INSERT INTO INVENTORY
VALUES
('794043749728', 'DVD', 'THE NOTEBOOK', 'NV', 'PG13', '4', '1');
INSERT INTO INVENTORY
VALUES
('012569593237', 'DVD', 'MILLION DOLLAR BABY', 'NV', 'UR', '2', '0');
INSERT INTO INVENTORY
VALUES
('025192211928', 'DVD', 'CINDERELLA MAN', 'NV', 'PG-13', '8', '3');
INSERT INTO INVENTORY
VALUES
('012236189411', 'DVD', 'WAITING...', 'NV', 'UR', '7', '4');
INSERT INTO INVENTORY
VALUES
('786936161571', 'DVD', 'PULP FICTION', 'OV', 'R', '3', '3');
INSERT INTO INVENTORY
VALUES
('013388200177', 'GCN', 'RESIDENT EVIL 4', 'NG', 'M', '4', '1');
INSERT INTO INVENTORY
VALUES
('710425274107', 'PS2', 'GRAND THEFT AUTO: SAN ANDREAS', 'NG', 'AO', '3', '2');
INSERT INTO INVENTORY
VALUES
('788687500012', 'PS2', 'HITMAN 2: SILENT ASSASSIN', 'NG', 'M', '2', '0');
INSERT INTO INVENTORY
VALUES
('045496870461', 'N64', 'SUPER SMASH BROS', 'OG', 'E', '4', '3');
INSERT INTO INVENTORY
VALUES
('014633147025', 'PS2', 'NEED FOR SPEED UNDERGROUND', 'OG', 'E', '3', '2');
INSERT INTO INVENTORY
VALUES
('047875810211', 'XBOX', 'CALL OF DUTY 2: BIG RED ONE', 'NG', 'T', '4', '0');
**MOD EDIT: Rent type and rating have already been made **
INSERT INTO RATING
VALUES
('G', 'GENERAL AUDIENCES', '0');
INSERT INTO RATING
VALUES
('PG', 'PARENTAL GUIDANCE SUGGESTED', '0');
INSERT INTO RATING
VALUES
('PG-13', 'PARENT STRONGLY CAUTIONED', '13');
INSERT INTO RATING
VALUES
('R', 'RESTRICTED', '17');
INSERT INTO RATING
VALUES
('NC-17', 'NO ONE 17 AND UNDER ADMITTED', '18');
INSERT INTO RATING
VALUES
('EC', 'EARLY CHILDHOOD', '3');
INSERT INTO RATING
VALUES
('E', 'EVERYONE', '6');
INSERT INTO RATING
VALUES
('E10+', 'EVERYONE 10 AND OLDER', '10');
INSERT INTO RATING
VALUES
('T', 'TEEN', '13');
INSERT INTO RATING
VALUES
('M', 'MATURE', '17');
INSERT INTO RATING
VALUES
('AO', 'ADULTS ONLY', '18');
INSERT INTO RENT_TYPE
VALUES
('NR', 'NEW RELEASE', '2', '3.50');
INSERT INTO RENT_TYPE
VALUES
('AC', 'ACTION, ADVENTURE', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('CL', 'CLASSIC', '7', '2.00');
INSERT INTO RENT_TYPE
VALUES
('SF', 'SCI-FI', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('CO', 'COMEDY', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('DR', 'DRAMA', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('SH', 'SHOOTER', '5', '2.50');
INSERT INTO RENT_TYPE
VALUES
('SI', 'SIMULATOR', '5', '2.50');
I post the correct values tonight during class.
** End edit **
For the rest of you all, good luck on whatever the hell your part is. Again to enigma, the ER diagram is done. I'll have it for you tomorrow during class at the latest. If i can find a way to e-mail it to you in my spare time, i will do so. Peace yall..
Mon Feb 20, 2006 6:17 am
ncamb02
Newbie
Joined: 31 Jan 2006
Posts: 1
INSERT INTO CUSTOMER
VALUES
('8796452135', 1, 'BOB', 'RAMSEY', '19750214');
INSERT INTO CUSTOMER
VALES
('8574962138', 1, 'SUE', 'EVANS', '19830317');
INSERT INTO CUSTOMER
VALUES
('8974653127', 1, 'TIFFANY', 'SCHULTZ', '19850725');
INSERT INTO CUSTOMER
VALUES
('8579462312', 1, 'JAMES', 'RAUSCH', '19560116');
INSERT INTO CUSTOMER
VALUES
('9478652351', 1, 'JOANN', 'TIMBERSHMIT', '19730518');
INSERT INTO CUSTOMER
VALUES
('8217394659', 1, 'DESRA', 'JACKSON', '19810127');
INSERT INTO CUSTOMER
VALUES
('8976542315', 1, 'ROBBY', 'SMITH', '19820423');
INSERT INTO CUSTOMER
VALUES
('8231645797', 1, 'SARAH', 'HOPPINS', '19800826');
INSERT INTO CUSTOMER
VALUES
('8132546892', 1, 'MELISSA', 'LEMMINS', '19500218');
INSERT INTO CUSTOMER
VALUES
('8312584695', 1, 'LARRY', 'BROWN', '19680322');
INSERT INTO CUSTOMER
VALUES
('8899775521', 1, 'CHRIS', 'JOANS', '19841102');
INSERT INTO CUSTOMER
VALUES
('8779654251', 1, 'DAN', 'COOLMEN', '19850108');
INSERT INTO CUSTOMER
VALUES
('8995542368', 1, 'JESSE', 'SAAD', '19730420');
INSERT INTO CUSTOMER
VALUES
('8995542369', 2, 'MARY', 'SAAD', '19760324');
INSERT INTO CUSTOMER
VALUES
('8221354897', 1, 'JUSTIN', 'MARLBORO', '19870625');
INSERT INTO CUSTOMER
VALUES
('8896354187', 1, 'ANDREA', 'TYLER', '19850713');
INSERT INTO ACCT
VALUES
('8796452135', '25781 SOMESTREET', 'FAIRFIELD', 'MI', '48631', '7.50');
INSERT INTO ACCT
VALUES
('8574962138', '3131 ALBANY', 'LANSING', 'MI', '48317', '2.50');
INSERT INTO ACCT
VALUES
('8974653127', '31582 RYAN', 'MADISON HEIGHTS', 'MI '48197');
INSERT INTO ACCT
VALUES
('8579462312', '28547 RUTHER', 'DEARBORNE', 'MI', '48315' '5.00');
INSERT INTO ACCT
VALUES
('9478652351', '20487 PINEBROOK', 'ALLEN PARK', 'MI', '48321', '10.50');
INSERT INTO ACCT
VALUES
('8217394659', '74985 BELINDA', 'CLINTON TWP', 'MI', '48310');
INSERT INTO ACCT
VALUES
('8976542315', '95846 MARK ORR', 'ROYAL OAK', 'MI', '48312', '5.00');
INSERT INTO ACCT
VALUES
('8231645797', '78456 GARFIELD', 'LAWSON', 'MI', '47158', '25.00');
INSERT INTO ACCT
VALUES
('8132546892', '85916 FOXHILL', 'HAMTRAMMICK', 'MI', '48325', '15.50');
INSERT INTO ACCT
VALUES
('8312584695', '3125 CHARITY', 'LIVONIA', 'MI', '48310');
INSERT INTO ACCT
VALUES
('8899775521', '45612 LAYFETTE', 'MOUND', 'MI', '48356', '5.00');
INSERT INTO ACCT
VALUES
('8779654251', '25816 GEORGIA', 'KNOB CREEK', 'MI', '47861');
INSERT INTO ACCT
VALUES
('8995542368', '21058 SALEM', 'TROY', 'MI', '48213', '7.50');
INSERT INTO ACCT
VALUES
('8221354897', '28486 STAPLER', 'SPINDLE', 'MI', '48310', '15.00');
INSERT INTO ACCT
VALUES
('8896354187', '85749 CHECKERS', 'TIMBUCKTWO', 'MI', '48156');
________________________________________________________________________________ _____________________
I HAVE A COUPLE QUESTIONS... HOPE THE DATA IS RIGHT BUT IF NOT ITS A COUPLE QUICK FIXES. FIRST, THE TABLE STRUCTURE HAS DECIMAL(02) WITHIN THE CUSTOMER TABLE FOR CUST_NUM... I DIDNT KNOW IF YOU WANTED THAT AS A DECIMAL OR JUST A TWO DIGIT NUMBER? SECOND UNDER THE ACCT TABLE NOT EVERY CUSTOMER SHOULD HAVE A SECONDARY ADDRESS SHOULD THEY? AS I WAS TYPING IT IN... DONT U THINK THEY SHOULD HAVE A TELEPHONE #? IF ANYONE WANTS TO ME TO FIX NETHIN ILL BE AVAILABLE ALL DAY BE4 CLASS SO GIVE ME A CALL @ 586-855-9290 ... ALRIGHT IF NOT SEE U GUYS IN CLASS
Mon Feb 20, 2006 4:24 pm
magecougar
Newbie
Joined: 31 Jan 2006
Posts: 5
Location: Riley, MI 48041
I didn't think we would need a second address, that's why I didn't put it in my revised table structure... By the way, I'm still waiting for an opinion on whether that was the way yall wanted to go... let me know... If you want to meet up before class, Nick, I'll be in the learning center from now (12:00) till class... let me know... later...
Tue Feb 21, 2006 2:01 am
Invinciblemoron
Newbie
Joined: 26 Jan 2006
Posts: 2
Technically we need the second address for the city address. I coded with room for the City address
Code:
insert into employee
values ('0001', 'Tim', 'Wojowski', '38592 West', 'Macomb", 'MI', '48035', '15.00');
insert into employee
values ('0002', 'Larry', 'Hilliard', '29438 North Ave', 'Mt. Clemens' 'MI', '38940', '9.70');
insert into employee
values ('0003', 'William', 'Johnson', '29438 Gratiot', 'Mt. Clemens 'MI', '38940', '9.50');
insert into employee
values ('0004', 'Anthony', 'Walker', '24882 Fairchild', 'Macomb', 'MI', '48042', '9.50');
insert into employee
values ('0005', 'George', 'Tomlin', '28784 Cotton', 'Chesterfield', 'MI', '48315', '9.50');
insert into employee
values ('0006, 'Bob', 'Bates', 28743 Cotton', 'Chesterfield', 'MI', '48315', '9.50');
_________________
You have Employees
5 employees is gewd.
place in code bracket and use insert statements
Tue Feb 21, 2006 6:04 am
enigma.0Z
Site Admin
Joined: 25 Jan 2006
Posts: 8
I've already did rating and rent_type. That's what I handed out to everyone. We need the address_2 because addresses have two lines.
As far as the inventory entries go, we just need to change them to match what I handed out last class (I'll edit them myself).
Code:
INSERT INTO RATING VALUES ( "G" , "General Audiences" , 1 );
INSERT INTO RATING VALUES ( "PG" , "Parental Guidence" , 6 );
INSERT INTO RATING VALUES ( "PG13" , "Strong Parental Guidence" , 13 );
INSERT INTO RATING VALUES ( "R" , "Restricted" , 17 );
INSERT INTO RATING VALUES ( "NC17" , "Restricted 17 years old" , 17 );
INSERT INTO RATING VALUES ( "X" , "Mature Content" , 21 );
INSERT INTO RATING VALUES ( "E" , "Everyone" , 1 );
INSERT INTO RATING VALUES ( "T" , "Teen" , 13 );
INSERT INTO RATING VALUES ( "M" , "Mature" , 17 );
INSERT INTO RATING VALUES ( "AO" , "Adults Only" , 18 );
INSERT INTO RATING VALUES ( "EC" , "Early Childhood" , 1 );
INSERT INTO RATING VALUES ( "E10" , "Everone 10+" , 10 );
INSERT INTO RENT_TYPE VALUES ( "NV" , "New Video" , 2 , 6.00 );
INSERT INTO RENT_TYPE VALUES ( "OV" , "Old Video" , 7 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "NG" , "New Game" , 5 , 5.00 );
INSERT INTO RENT_TYPE VALUES ( "OG" , "Old Game" , 2 , 4.00 );
INSERT INTO RENT_TYPE VALUES ( "S1" , "Special 1" , 2 , 3.00 );
INSERT INTO RENT_TYPE VALUES ( "S2" , "Special 2" , 2 , 1.50 );e
As far as dec(02) in customer, that's just the number of the customer under that account number:
for instance Account 12345 Customer 1 is a dad, customer 2 is a mom, and 3, 4, and 5 are their kids -
[mysql 4.1] viewing all InnoDB locks
Date: 03/04/06 Keywords: mysql, sql
Does anybody know method to view all locks, hold by concrete thread?
"show engine innodb status" shows only "waiting for this lock"...
I need info such as "Last detected deadlock"(part "holds these locks:"), but for each current thread.
Can't find that neither standard man or "mysql internals"... -
Connection error
Date: 02/26/06 Keywords: cms, php, mysql, database, sql, hosting
I programmed a CMS for one of my client and put it up on my own site in a test directory while it was in development. It was working just fine. They finally got their own hosting set up with Network Solutions so I uploaded all my files, set up the database and changed the username, password and db name in my scripts. Now I'm getting the following error: Warning: mysql_connect(): Can't connect to local MySQL server through socket '/tmp/mysql.sock'
I've double checked that I have the right username and the right password.
I'm using the following code to connect: $link = mysql_connect('localhost', $user, $pass) or die("Could not connect : " . mysql_error());
When I log into phpMyAdmin I see the following: MySQL 4.1.16-log running on localhost as eclipse@localhost so I'm assuming that 'localhost' is right. What else could be wrong? I've tried emailing tech support at Network Solutions but I doubt anyone there will get back to me. It's a pretty basic error but i can't figure out what else I would've needed to change when switching the site from my server to theirs. -
TIME ZONE
Date: 02/25/06 Keywords: mysql, sql
Server timezone is GMT-6.
What is the fast way to get GMT date using current_date() function or any other in request?
Server version is MySql 4.0 so ADDTIME(), CONVERT_TZ(), UTC_DATE(), DATEDIFF() and other cool MySql 4.1 functions does'nt work :( -
Select question
Date: 02/22/06 Keywords: no keywords
Hi all,
I have two tables
drugs
drug_id | drug_name
qty_price
pharmacy_id | drug_id
I need a fast efficient select that will return all drug IDs that are not in qty_price. The drug table will have thousands of records and the qty_price table will approx. 4 to 5 times that amount.
Any helpful suggestions including restructuring if needed as appreciated. -
MySQL video and presentation up
Date: 02/20/06 Keywords: mysql, sql, google
The first hour of a 2-hour workshop on what to do with a MySQL instance you've inherited. Very detailed, goes into real examples using shell prompts.
Unfortunately, the settings were incorrect on the video recorder, so only the 1st hour was captured. Presented at the Boston MySQL Meetup Group on February 13th, 2006.
Special thanks to!['[info]']('http://stat.livejournal.com/img/userinfo.gif')
lensman for securing the MIT space.
google video
flash slides
pdf slides (this might be broken, please let me know)
worksheet
As always, stylistic criticism as well as content criticism is appreciated. -
Nested LEFT JOIN sql syntax?
Date: 02/15/06 Keywords: mysql, html, sql
Given the following schema, the following datasets are defined:
A procedure is a collection of testcases, a plan_procedure is a collection of plan_testcases which reflect a temporal snapshot of a procedure collection.
So my question is, given a procedure.id and plan_procedure.id, how do I show the differences between a procedure and a plan_procedure? i.e. show missing testcases from plan_testcases and vice versa? I have been studying the docs on LEFT JOINS and am having problems grokking this one.
Schema follows:CREATE TABLE `procedures` ( `id` int(10) unsigned NOT NULL auto_increment, `procedure_name` varchar(80) NOT NULL default '', PRIMARY KEY (`id`), UNIQUE KEY `procedure_name` (`procedure_name`) ); CREATE TABLE `testcases` ( `id` int(10) unsigned NOT NULL auto_increment, `procedure_id` int(10) unsigned NOT NULL default '0', `procedure_steps` text NOT NULL, `expected_results` text NOT NULL, PRIMARY KEY (`id`) ); CREATE TABLE `plan_procedures` ( `id` int(10) unsigned NOT NULL auto_increment, `procedure_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) ); CREATE TABLE `plan_testcases` ( `id` int(10) unsigned NOT NULL auto_increment, `plan_procedure_id` int(10) unsigned NOT NULL default '0', `testcase_id` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`id`) );
I am trying to follow the examples from http://dev.mysql.com/doc/refman/4.1/en/join.html, and not getting any traction. Lend a hand? -
February Boston MySQL Meetup on Mon. Feb. 13th, 7-9 pm. FREE!
Date: 02/12/06 Keywords: mysql, sql
The Boston-area MySQL Meetup group is going strong with over 150 members! (20-50 show up each meeting) This month features a change in location, so now we have close subway access AND FREE PARKING. We are supported by MySQL AB, who provides pizza and soda.
This meetup group is totally free. There are benefits including discounts on O'Reilly books (30% off!) and prizes. The topic for the meeting on Monday, February 13th is "So You've Inherited a MySQL Instance". It will be useful for beginners, and experience MySQL users will refresh their memory on what needs to be secured.
It has a unix focus, but most of the ideas can be applied to Windows users as well.
Afterwards, we'll head to the Cambridge Brewing Company for more discussion and chatting.
More details, including the event location, directions and parking, are at:
http://mysql.meetup.com/137/events/
I encourage you to sign up for the meetup group, as I may not post here every month.
Previous Boston MySQL meetup presentations can be found under the "Presentations" link at http://www.sheeri.com
Any questions, comments, etc should be sent to me.
You may forward this announcement to other groups, lists, blogs, whatever. -
MySQL Version issue with Timestamp
Date: 02/10/06 Keywords: mysql, software, sql, web, google
Hi...
I have googled, dev.mysql.com'ed, RTFM and stuff, and I'm a little frustrated, I hope someone here knows the definitive answer.
We developed our web-based software on version 3.23.41 where the timestamp format is YYYYMMDDHHMMSS, I'm sure you know the drill.
Now, we're migrating to a new server, with 4.1.12 and the timstamp default format is %Y-%m-%d %H:%i:%s
The big question: Can I change this version of MySQL (I do have root access) to use the old type of timestamp format?
If so, How? Or, can you point me to a resource that explains it?
Again, I have tried google, dev.mysql.com, etc. I apologize if this is a dumb question, but so far all sources point to it just being the format and nothing I can do. I kinda don't believe that, as it sounds like an inflexibility that would cause other developers to run into problems upgrading....unless of course, I am doing it all wrong!
Any help is appreciated. -
problems with SELECT * INTO OUTFILE
Date: 02/08/06 Keywords: mysql, sql
Hello,
I am having problems with the SELECT * INTO OUTFILE
I have already ensured that:
1) I have file access
2) the folder is chmod 777
3) the file does not already exist
My code looks like this:
$query="SELECT * INTO OUTFILE 'backup/$date.txt' FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n' FROM journal";
$result=mysql_query($query) or die(mysql_error());
if ($result) { echo "Backup file was created successfully!"; }
This is what I get when I run the script:
Can't create/write to file 'backup/07022006.txt' (Errcode: 2)
Any help would be greatly appreciated. Thanks! -
mySQL/PHP question
Date: 02/06/06 Keywords: php, mysql, sql, web
Howdy,
Suppose I want a way to easily backup a SQL table. (I use MySQL but that shouldn't matter.) I would like to be able to have a link on my website that, when I click on it, lets me download a TXT file with all of the data in tab separated values, so I can easily load it into excel.
Somebody suggested using select into outfile 'filename', but I must admit that the mySQL manual baffles me and I have a hard time getting any help from it. So, how would I use it in this regard?
Thanks much!
(crossposted to php community) -
message structure advice
Date: 02/03/06 Keywords: database
I'm working on a site for a medical/nutritional company with custom "client" and "admin" sections, each with a few different features; one thing I built in was a very simple messaging system for clients to communicate back and forth with staff members (kind of a mini-emailing system)
The way I initially set things up, I have information about staff users and client users stored in 2 different tables, as up until now they were 2 separate elements and I'm storing slightly different information on each;
What I'm thinking of doing now is expanding the messaging system to allow messaging between staff members as well (we just never thought of doing it until now) but I'm wondering which way I should change my database structure and thought I'd see if I could get some input
Currently I have my messaging and client/admin login setup roughly like this:
client
------
[clientID]
staff
-----
[staffID]
messages
--------
[msgID]
clientID
staffID
fromStaff (this is a 0/1 to determine who the message is to and who it is from)
I'm wondering if I should either add another table of messages to hold staff->staff messaging:
staff_mesages
-------------
[staffMsgID]
fromStaffID
toStaffID
subject
body
Or - throw all my logins into one table (client and staff users) and determine type by adding an extra access level for clients (I have multiple access levels in the staff end but only 1 client access level) and then do all my messaging like:
messages
--------
fromUserID
toUserID
message
subject
any suggestions? -
Selecting where not
Date: 01/23/06 Keywords: no keywords
I have two tables
drugs
drug_id | category_id | drug_name | information | warnings
qty_price
id | drug_id | pharmacy_id | cost | generic | strength
I want to get a list of drug names where the drug is not listed in qty_price.
SELECT drugs.drug_name
FROM drugs, qty_price
WHERE drugs.drug_id != qty_price.drug_id
ORDER BY drug_name ASC
LIMIT 50
This seems incredibly inefficient. It has to search every row in drugs for every id in qty_price, I think.
There has to be a better way, but I dont see it because my brain went to bed without me.
Anyone have a resource or answer?
Any help is much appreciated. -
Update multiple tables
Date: 01/19/06 Keywords: php, mysql, sql
** EDIT
turns out the problem is my version of mysql, thanks for the help.
Hi, I am having a problem trying to update ids in a table.
I am using PHPmyAdmin.
I looked at the syntax on mysql.com and I am fairly certain that I have it right but I get the following error:
#1064 - You have an error in your SQL syntax near ' drugs SET synonyms.drug_id = drugs.drug_id WHERE 'synonyms.drug_name' = 'drugs.' at line 1
The two tables are as follows:
drugs
drug_id | drug_name
synonyms
drug_id | synonym | drug_name
This is my SQL statement:
UPDATE synonyms, drugs SET synonyms.drug_id = drugs.drug_id WHERE 'synonyms.drug_name' = 'drugs.drug_name' -
do you know about binary logs?
Date: 01/19/06 Keywords: mysql, database, sql
These tech communities are mostly questions from those that need help which I often do :)
I thought it'd be fun to change the pace a little and post some info I think is good to know if your using mysql.
I've recently had problems with table crashes and running mysql in an environment with very little disk space. This has taught me a few things I did not know before hand. One of which is mysql's binary log (also known as the update log in older versions). It stores all statements that are run that could potentially modify data in the database which allows restores to work as best as possible (i.e. even if your backup is a bit old). For more info check the binary log docsSource: http://www.livejournal.com/community/mysql/83396.html
-
do you know about binary logs?
Date: 01/19/06 Keywords: mysql, database, sql
These tech communities are mostly questions from those that need help which I often do :)
I thought it'd be fun to change the pace a little and post some info I think is good to know if your using mysql.
I've recently had problems with table crashes and running mysql in an environment with very little disk space. This has taught me a few things I did not know before hand. One of which is mysql's binary log (also known as the update log in older versions). It stores all statements that are run that could potentially modify data in the database which allows restores to work as best as possible (i.e. even if your backup is a bit old). For more info check the binary log docs -
Changing fields made by auto-inc
Date: 01/18/06 Keywords: no keywords
I have a field in a table that is an auto-increment id field. Normally the numbers are okay, but sometimes I need to change the id for a record. Apart from making sure that the number isn't already taken by another record, is there anything else I need to worry about when changing the id field?
I've done some research on this, but I haven't been able to find exactly what I'm looking for. (Probably don't know the right words to look for.)Source: http://www.livejournal.com/community/mysql/83058.html
-
Changing fields made by auto-inc
Date: 01/18/06 Keywords: no keywords
I have a field in a table that is an auto-increment id field. Normally the numbers are okay, but sometimes I need to change the id for a record. Apart from making sure that the number isn't already taken by another record, is there anything else I need to worry about when changing the id field?
I've done some research on this, but I haven't been able to find exactly what I'm looking for. (Probably don't know the right words to look for.) -
mysql 4/5 && TIME_WAIT
Date: 01/18/06 Keywords: php, mysql, sql
good evening! has anybody ever seen mysql clients (mysql, php, check_mysql(nagios), etc) choke with an error similar to 'can't assign requested address' ? I've got over 30,000 connections listed in netstat with a status of TIME_WAIT. fairly basic php/mysql site (high traffic), no apparent cause.
possible causes I've thought of:
- maybe somebody initiated a connection but didn't close it.. thrwarted due to the mysql reporting otherwise.
- mysql being retarded? not likely, when the error occurs, no incoming connection is reached by the mysql server (via tcpdump/ngrep), resource usage on the server is fine. same with the process list.
- max files being reached?
any thoughts? :DSource: http://www.livejournal.com/community/mysql/82879.html
| Previous page | || | Next page |