Jump to content
honkmaster

Sql to loop through table

Recommended Posts

Hi, I have been stuck on best way to achieve the following (I'm still learning php and Sql so for give me if this is obvious)

I have a table (See Screen Shot) which has the following headings placeID, place, job1, job2, job3, job4, job5 etc I'm trying to run query which will return as the the result screen shot attached.

I'm not sure if I should be doing this within PHP or SQL? What I have is correct for job 001but i need all the other jobs underneath as in screen shot.

SELECT Campaign.PlaceId, Campaign.Place, Campaign.`001`
FROM Campaign
WHERE Campaign.`001` >='1' 

Any help would be great, Cheers Chris

Table.jpg

Result.jpg

Share this post


Link to post
Share on other sites

The first part of the solution is normalize your data. Those "job" columns should be in a separate table, one job per row  plus place id

+--------+---------------+
|placeid | area          |
+--------+---------------+
| 310    | Abbotsinch    |
| 9002   | Ballymena     |
+--------+---------------+
    |
    +------------+
                 |
            +---------+-------+---------+
            | placeid |  job  |   qty   |
            +---------+-------+---------+
            |  310    |   001 |      1  |
            |  310    |   003 |      1  |
            |  9002   |   002 |      1  |
            |  9002   |   003 |      1  |
            +---------+-------+---------+

Then the query would be

SELECT c.placeid
     , area
     , job
     , qty
FROM campaign as c
     JOIN job as j USING (placeid)
ORDER BY job, area

Share this post


Link to post
Share on other sites

Hi Thank you very much for your response, which is a great solution. The issue we have is the data comes in as displayed. What we are trying to do is not have to manipulate the data as this is the bit that takes time. So the benefit is only there is sql or php can produce the result as displayed in the result jpg. Cheers Chris

Share this post


Link to post
Share on other sites

Then you need 10 queries with UNIONS

SELECT Campaign.PlaceId
     , Campaign.Place
     , '001' as job
     , Campaign.`001`
FROM Campaign
WHERE Campaign.`001` >='1'
UNION
SELECT Campaign.PlaceId
     , Campaign.Place
     , '002' as job
     , Campaign.`002`
FROM Campaign
WHERE Campaign.`002` >='1'
UNION

...

UNION
SELECT Campaign.PlaceId
     , Campaign.Place
     , '010' as job
     , Campaign.`010`
FROM Campaign
WHERE Campaign.`010` >='1'

ORDER BY job, Place;

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.