CODEWITHSUNDEEP
mysql
user6751235
user6751235

Reputation: 59

mysql Select Query based on same table

i have a mysql table promotions main fields are PromotionMaintenanceID (Primary) PID PromotionID PromotionName

I have a special case where the PromotionID of one record (This will be the main Record) become the PID of some of some other records. I need to create a MySql statement to get record with PromotionMaintenanceID (of main Record) , PromotionID (of main Record) , PromotionName (of main Record) and PromotionID of all the records which has PID =PromotionID of the main record

PromotionMaintenanceID ,PID , PromotionID ,PromotionName
1  | T1  | 12 | Promo1
2  | 12  | 22 | PromoSub
3  | 12  | 33 | PromoSub2

I need my result like 

PromotionMaintenanceID | PID | PromotionID | PromotionName | Sub PromoID
1  | T1  | 12 | Promo1  |22,33

Any one know how the query should be?

Upvotes: 0

Views: 42

Answers (2)

Nick
Nick

Reputation: 147246

This query (SQLFiddle) should do what you want:

SELECT p1.PromotionMaintenanceID, p1.PID, p1.PromotionID, p1.PromotionName, GROUP_CONCAT(p2.PromotionID) AS `Sub PromoID`
FROM promotions p1
JOIN promotions p2
ON p2.PID = p1.PromotionID
GROUP BY p1.PromotionID

Output:

PromotionMaintenanceID  PID     PromotionID     PromotionName   Sub PromoID
1                       T1      12              Promo1          33,22

Upvotes: 3

Rando
Rando

Reputation: 473

For that you should be able to do a straight join (or double select depending on your favourite way)

SELECT
  a.PromotionID,
  a.MaintenanceId,
  a.PromotionName,
  GROUP_CONCAT(b.PID) as SubIds
FROM
  PromotionTable a
LEFT JOIN
  PromotionTable b
ON
  a.PromotionID = b.PID
//ADD AND's here with a.(column) = b.PID if you absolutely need all the ID's for each column to link up
GROUP BY
  a.PromotionID, a.MaintenanceID, a.PromotionName

Upvotes: 0

Related Questions