One question I have is how does the Company relate to a phone number? I mean, is there just a list of internal numbers, and will all of these phone numbers be associated to an employee. If they are you can simply just make your phone number table and have it relate to an employee. For example.
create table company
(
company_id int PRIMARY KEY IDENTITY, --(Primary Key)
company_name varchar(100)
)
create table employee
(
employee_id int PRIMARY KEY IDENTITY, --(Primary Key)
employee_name varchar(100),
company_id int not null --(Foreign Key)
)
create table phone
(
phone_id int PRIMARY KEY IDENTITY,
phone_number varchar(15), -- 555-555-5555
phone_type varchar(10), -- Home | Cell | Work
employee_id int not null --(Foreign Key)
)
employee_id being your foreign key to your employees table.
If each employee only works for one company, then to get all of the phone numbers available for that company, you could do something like this
select *
from phone as p
join employee as e on e.employee_id = p.employee_id
where e.company_id = 1
I hope this helps :)
I have a solution that does not need GROUP_CONCAT
PROPOSED SOLUTION
SET @x = 0;
SET @name = '';
SET @result = '';
SELECT name,consecutive FROM
(SELECT
name,
(@nametag:=MD5(CONCAT(name,':',result))),
(@x:=IF(@name=@nametag,@x+1,1)) consecutive,
(@name:=@nametag) inc
FROM finals ORDER BY name,id) A
WHERE consecutive >= 3;
PROPOSED SOLUTION EXECUTED
mysql> SELECT name,consecutive FROM
-> (SELECT
-> name,
-> (@nametag:=MD5(CONCAT(name,':',result))),
-> (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
-> (@name:=@nametag) inc
-> FROM finals ORDER BY name,id) A
-> WHERE consecutive >= 3;
+------+-------------+
| name | consecutive |
+------+-------------+
| Kyle | 3 |
+------+-------------+
1 row in set (0.02 sec)
mysql>
SUBQUERY'S OUTPUT
mysql> SELECT
-> name,
-> (@nametag:=MD5(CONCAT(name,':',result))),
-> (@x:=IF(@name=@nametag,@x+1,1)) consecutive,
-> (@name:=@nametag) inc
-> FROM finals ORDER BY name,id;
+------+------------------------------------------+-------------+----------------------------------+
| name | (@nametag:=MD5(CONCAT(name,':',result))) | consecutive | inc |
+------+------------------------------------------+-------------+----------------------------------+
| John | 84cc30b986fe149dfb765dd09fad8a60 | 1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 534b3d163a04b74a72c6dbe68db1c01e | 1 | 534b3d163a04b74a72c6dbe68db1c01e |
| John | 84cc30b986fe149dfb765dd09fad8a60 | 1 | 84cc30b986fe149dfb765dd09fad8a60 |
| John | 84cc30b986fe149dfb765dd09fad8a60 | 2 | 84cc30b986fe149dfb765dd09fad8a60 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850 | 1 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850 | 2 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 30fac0873cf25ad17b38bc37bda4b850 | 3 | 30fac0873cf25ad17b38bc37bda4b850 |
| Kyle | 4a4e0aaa102c37f098bd6afd13ccfea0 | 1 | 4a4e0aaa102c37f098bd6afd13ccfea0 |
+------+------------------------------------------+-------------+----------------------------------+
8 rows in set (0.00 sec)
mysql>
GIVE IT A TRY !!!
I just answered a similar question 2 days ago (Logic Value of Assignment Expression)
Best Answer
One way by using a subquery that returns reportsTo of employeeNumber = 1088