I have created a table representing the employees in my company. Each record has an employeeID field (Primary Key, a smallint, that autoincrements), lastName, firstName, etc. Also, each record has a managerID field, which will store the employeeID of the manager of the employee represented by the current row. I'll have the president of the company be his/her own manager.
For example, if the "employees" table looks like this:
employeeID lastName firstName managerID
---------- --------- --------- ---------
1 Boop Betty 1
2 TheCat Felix 1
3 Slate Henry 1
4 Flintstone Fred 3
5 Rubble Barney 3
Then Betty Boop is the president, Felix TheCat and Henry Slate report to her. Fred Flintstone and Barney Rubble report to Henry Slate.
I want to create some queries:
1) SELECT lastName, firstName FROM employees WHERE 'manager's lastName is Slate'
2) SELECT 'manager's lastName' FROM employees WHERE lastname LIKE rubble
but I do not know how to represent the portions in the "...".
In other words, I want to be able to reuse the employeeIDs to indicate the manager, but I also want to be able to map the managerIDs back to their names.
Is this possible?
http://forums.mysql.com/read.php?10,138487,138487#msg-138487
Have a problem.
Columns:
Date_start , Date_Stop , User_ID , info
Date_stop is optional ( NULL if not used )
sample database information
2007-01-01 , 2007-01-04 , 102 , infotext
2007-04-09 , NULL , 105 , infotext2
2007-06-04 , 2007-06-06 , 222 , infotext3
i want the Query output look like this.
Column: Date , User_Id , Info
not any start/stop date
one row for each date
2007-01-01 , 102 , infotext
2007-01-02 , 102 , infotext
2007-01-03 , 102 , infotext
2007-01-04 , 102 , infotext
2007-04-09 , 105 , infotext2
2007-06-04 , 222 , infotext3
2007-06-05 , 222 , infotext3
2007-06-06 , 222 , infotext3
is it possible to make a Query/view to fix that? or must i use Stored Procedures?
how shall i do?
http://forums.mysql.com/read.php?10,140674,140674#msg-140674
How can I determine the offset of a result in an ordered result set?
I would like to pass the calculated offset into the limit half of and ordered select statement.
E.g. I have a table that records a id and datetime for captioned photographs. I'd like to show the five photos that were taken after the photo with id=23.
To do that I need to find the offset of photo with id=23 in
select id, datetime, caption from photos order by datetime;
Then I could get the result I want by doing....
select id, datetime, caption from photos order by datetime limit $offset, 5;
http://forums.mysql.com/read.php?10,140514,140514#msg-140514
CREATE TABLE `goods` (
`cat_id` int(10) UNSIGNED NOT NULL,
`seller_id` int(10) UNSIGNED NOT NULL,
`price` decimal(10,2) NOT NULL,
KEY `cat_id` (`cat_id`,`price`),
KEY `cat_id_2` (`cat_id`,`seller_id`
)
mysql> EXPLAIN SELECT * FROM goods WHERE cat_id=5 AND seller_id=1 ORDER BY price DESC LIMIT 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: goods
type: ref
possible_keys: cat_id,cat_id_2
KEY: cat_id_2
key_len: 8
ref: const,const
rows: 296338
Extra: USING WHERE; USING filesort
1 row IN SET (0.00 sec)
mysql> EXPLAIN SELECT * FROM goods force INDEX(cat_id) WHERE cat_id=5 AND seller_id=1 ORDER BY price DESC LIMIT 10 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
TABLE: goods
type: ref
possible_keys: cat_id
KEY: cat_id
key_len: 4
ref: const
rows: 989171
Extra: USING WHERE
1 row IN SET (0.00 sec)
http://www.mysqlperformanceblog.com/2007/02/16/using-index-for-order-by-vs-restricting-number-of-rows/
Updating multiple rows
I need to update several rows. Each row has a date column with data like "2004-03-02". I want to change the year to 2007 for all the rows in my data base without changing the month and the day.
Basically change a date like "2004-03-02" to "2007-03-02"
Is there a way to do this do this all in one update query?
http://community.livejournal.com/mysql/109009.htmlLabels: mysql, mysql case study
I have imported data into my mysql table 'contacts' which has the columns lname and fname. Many of these 4,000 entries are in all capitals. Is there a way to convert all the records in a particular column to Initial Caps?
http://forums.mysql.com/read.php?10,137473,137473#msg-137473
Given two tables:
Table Person:
ID | Age
--------
1 | 45
2 | 32
etc.
Table Answer (ID: Foreign-key which relates to the table Person, 1:n)
ID | QuestionNo | Answer
1 | 1 | 3
1 | 2 | 1
1 | 3 | 2
2 | 1 | 1
etc.
Let's say, each person fills out three questions. Now I would like to have a resultset in which each row represents a person, and each of the three questions (QuestionNo) gets a column of its own, in where you would find the given answer:
Age | Question1 | Question2 | Question3
45 | 3 | 1 | 2
32 | 1 etc.
How should the sql-statement look like?
http://forums.mysql.com/read.php?10,137607,137607#msg-137607
i have a csv file a section of which is below, when i try and load this in 5.0 using load data infile
etc it fails as the timedate field - first column has " characters in there, and ideas how to make this work...
"2007-01-31 20:55:00",576,554,1,11.91,24.84,12.36,10.4,11.02,11.87,10.24,11.68,11.54,10.84,
11.26,11.43,11.55,10.83,11.64,12.49,12.28,12.96,12.65,11.85,12.72,10.34,10.92,
11.72,12.69,0,0,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,0,355,355,355,1
"2007-01-31 10:25:00",0,554,1,11.91,24.39,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,
0,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,0,1,1,1,1
"2007-01-31 10:26:00",1,554,1,11.91,24.39,0.933,1.118,0.907,0.01,0,0,0,0,1.206,1.172,0.231,
0,0,0,0,0.374,1.261,1.21,0.02,0,0,0,0,0,0,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,12,
12,12,12,12,12,12,12,12,12,12,12,0,2,2,2,1
as you can see only the date/time has the " char. the rest is CSV
assuming we can get this working, any ideas how to schedule this to run to update the table at regular intervals...
http://forums.mysql.com/read.php?10,137706,137706#msg-137706