0

I need to get a table from subquery for each row in my query.

For instance I have 2 tables: companies, staff:

**companies**
company_id (int)
company_name (char)

**staff**
staff_id (int)
company_id (int)
first_name (char)
last_name (char)
position (char)

I want to get all company staff as a extra column, something like that:

SELECT c.company_name,
(SELECT first_name, last_name, position FROM staff WHERE c.company_id = staff.company_id) as staff
FROM companies c

Obviously the query above returns "Operand should contain 1 column(s)" error.

Is there a way to do that in MySQL?

AlecTMH
  • 2,615
  • 23
  • 26
  • Ok since joining will not work.... Can you show the results expected? – xQbert Oct 28 '16 at 14:25
  • "I want to get all company staff as a extra column," so if a company has 10 staff members, you want the first, last and position listed for all employees in 1 column? `concat` & `Group_Concat` sound like the functions you want – xQbert Oct 28 '16 at 14:35
  • Sorry, it doesn't work for me, staff data include date of birth, department_id, status, photo, etc so I can't just concat all fields. – AlecTMH Oct 28 '16 at 14:37
  • Yep, not sure what you're after then. Without expected results or full query and English statement of problem, I don't know how to help. – xQbert Oct 28 '16 at 14:40

1 Answers1

2

Maybe you want to join the tables?

SELECT c.company_name, S.first_name, S.last_name, S.position 
FROM companies c
INNER JOIN staff 
  ON c.company_id = staff.company_id

or do you want it all in one column or something... 

SELECT c.company_name, concat(S.first_name, ' ', S.last_name, ' ', S.position) as staff
FROM companies c
INNER JOIN staff 
  ON c.company_id = staff.company_id

Still not sure what you're after so maybe...

SELECT c.company_name, Group_concat(concat(S.first_name, ' ', S.last_name, '-', S.position Order by Last_name, First_name SEPARATOR ':' ) as staff
FROM companies c
INNER JOIN staff S
  ON c.company_id = staff.company_id
GROUP BY c.Company_name

What the above should do is concatenate the first name space last name dash position for each row, and then combine the rows separated by a :. This will result in 1 and only 1 record for each company in table staff. thus it will not cause any 1-M relationship issues. as it will treat company/staff as a 1-1 relationship. If need be we can do the generation as a subquery like the following...

SELECT c.company_name, S.StaffList
FROM companies c
LEFT JOIN (SELECT company_ID, Group_concat(concat(S.first_name, ' ', S.last_name, '-', S.position Order by Last_name, First_name SEPARATOR ':' ) as staffList 
           FROM staff
           GROUP BY Company_ID) S
  ON c.company_id = S.company_id

this should result in a 1-1 relationship between company & staff. though staffList will be rather complex...

xQbert
  • 34,733
  • 2
  • 41
  • 62
  • I can't, my real parent query has a lot of columns joined from different tables so I need some hierarchy there – AlecTMH Oct 28 '16 at 14:27
  • Probably my first example is not good, imagine you need to list all managers and list all his people. Manager columns are bit different. So the result must look like :Manager 1 > Employee 1, Employee 2; Manager 2 > Employee 3, Employee 4, etc. Do you see what I mean? – AlecTMH Oct 28 '16 at 14:32
  • Ok, so you need a hierarchical query in mySQL, not so much fun. but SO has a few examples: http://stackoverflow.com/questions/20215744/how-to-create-a-mysql-hierarchical-recursive-query Are there a finite number of levels? if so a self join should do the trick... – xQbert Oct 28 '16 at 14:42
  • xQbert, thank you for your help, I'll try to find the solution. – AlecTMH Oct 28 '16 at 14:51
  • I stopped on getting the parent query first, then grab staff ids from it and run the second query using mysql in. – AlecTMH Oct 28 '16 at 16:42
  • Sounds like a reasonable course of action; more trips to the DB than I prefer but you also have less overhead on data. you should post your answer and accept your own as aid to others who may have similar issues. – xQbert Oct 28 '16 at 17:57