Jump to content

Sql to loop through table


honkmaster

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

Link to comment
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
Link to comment
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

Link to comment
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;

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

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.