Welcome Guest, Not a member yet? Register   Sign In
Mysql query question
#1

[eluser]megabyte[/eluser]
Two tables

People

personID | name
-----------------
1 Bob
2 Rick


Jobs

jobID | personID | jobtitle
-----------------------------
1 1 Baker
2 1 Painter
3 2 Cook
4 1 Welder



I want to query both tables and find out all the jobs Bob has and combine like this



personID | name | jobID | jobtitle
-----------------------------------------
1 Bob 1,2,4 Banker, Painter, Welder




*** Notice how only one row is returned based on the People table then the job id and job titles which have multiple rows found in the Jobs table and stored in a single column each seperated with a comma.


What I don't want is what I do know how to do which is a regualr join and get this

personID | name | jobID | jobtitle
-----------------------------------------
1 Bob 1 Banker
1 Bob 2 Painter
1 Bob 4 Welder


Someone please help? Tongue
#2

[eluser]Krzemo[/eluser]
This should work - if not digg around group_concat mysql function

Code:
SELECT p.personID
, p.name
, GROUP_CONCAT(j.jobID) AS jobID
, GROUP_CONCAT(j.jobtitle) AS jobtitle
FROM people p
JOIN jobs j ON j.personID = p.personID
GROUP BY p.personID
, p.name
#3

[eluser]slowgary[/eluser]
Edit: Well, I stand corrected (I'm actually sitting)

I think the join is the only way. You could use 2 queries. Think about what you're asking for, how would mysql return that data?

If it COULD return a row like that, what would the code look like to get that data?

E.g.
Code:
while($person = mysql_fetch_array($results))
{
     echo $person['name'] . $person['job1'] . $person['job2'] ... $person['job54'];
}

See what I mean? You wouldn't have a definite number of fields.

Basically, you're asking for vertical data in a horizontal format. Mysql wouldn't have identifiers for all those fields. Even if you could use the AS keyword, you'd need to supply mysql with a pattern to use for naming.

There may be something you could do with stored procedures, but wouldn't it be so much easier just to issue two queries? One for the person, and one for their jobs.

In an ideal world, you'd never need a table like that - one job for each person.[/b]I think the join is the only way. You could use 2 queries. Think about what you're asking for, how would mysql return that data?

If it COULD return a row like that, what would the code look like to get that data?

E.g.
Code:
while($person = mysql_fetch_array($results))
{
     echo $person['name'] . $person['job1'] . $person['job2'] ... $person['job54'];
}

See what I mean? You wouldn't have a definite number of fields.

Basically, you're asking for vertical data in a horizontal format. Mysql wouldn't have identifiers for all those fields. Even if you could use the AS keyword, you'd need to supply mysql with a pattern to use for naming.

There may be something you could do with stored procedures, but wouldn't it be so much easier just to issue two queries? One for the person, and one for their jobs.

In an ideal world, you'd never need a table like that - one job for each person.
#4

[eluser]megabyte[/eluser]
Thanks guys, and yes grou_cat() is the solution. I figured itout before I got to check your answers.

I always use forums as a back up then start trying to solve it myself. Tongue
#5

[eluser]slowgary[/eluser]
So how about answering my question then? ;-P

How is the data returned?

For instance, if you were to call mysql_fetch_array() on your results, what would the array keys for each job be?
#6

[eluser]Josh Ellis[/eluser]
Assuming he would just explode the field(s) on the "," character.

This would then give you one "name" with 2 arrays of jobs and job id's.
#7

[eluser]Yorick Peterse[/eluser]
The JOIN query should be something like the following:

Code:
SELECT people.personid,jobs.jobid,name,jobs.jobtitle FROM `people` join jobs on jobs.personid = people.personid ORDER BY people.personid;




Theme © iAndrew 2016 - Forum software by © MyBB