SELECT psi.id,
psi.price,
psi.accessory,
rm.catalogmodel rnum,
rm.id rmid,
r.model_name rname,
r.image rimage,
a.access_name aname,
a.access_num anum,
a.image aimage,
a.listprice as aprice,
a.id as AID
FROM ps_inventory psi
JOIN radio_model rm on psi.item_id = rm.id
JOIN radios r on rm.radioid = r.id
LEFT JOIN ps_accessory on psi.item_id = ps_accessory.item_id
LEFT JOIN accessories a on ps_accessory.access_id = a.id
WHERE Customer_id = 320
GROUP BY
rm.catalogmodel,
rm.id,
r.model_name,
r.image,
a.access_name,
a.access_num,
a.image,
a.listprice,
a.id,
psi.accessory,
psi.price,
psi.id
In essence, each line should pull down the model, and the accessory for it.
What I get is the model, a long listing of records of each accessory but it
"inherits" the accessory bit and pricing value from the catalog model. What
have I done wrong?
Example output:
240 530.0000 0 AAM25SKF9DP5_N 1000035 Motorola CDM1550
Mobile CDM Power Supply SL11A
2300100
240 530.0000 0 AAM25SKF9DP5_N 1000035 Motorola CDM1550
Mobile SL15CDM Power Supply with Hood 64709 2300103
240 530.0000 0 AAM25SKF9DP5_N 1000035 Motorola CDM1550
Mobile SL15R Power Supply 90413 2300102
SHOULD BE:
240 530.0000 0 AAM25SKF9DP5_N 1000035 Motorola CDM1550
Mobile
241 20.0000 1 AAM25SKF9DP5_N 1000035 Motorola CDM1550 Mobile CDM
Power Supply SL11A 2300100
242 25.0000 1 AAM25SKF9DP5_N 1000035 Motorola CDM1550 Mobile
SL15CDM Power Supply with Hood 64709 2300103
243 55.0000 1 AAM25SKF9DP5_N 1000035 Motorola CDM1550 Mobile
SL15R Power Supply 90413 2300102
So its not grabbing the main inventory items, and then it sub accessories.
How does one go about that properly? What did I monkey up in the Joins or
whatever?
Thanks!
Regards,
Eric J. Hoffman
Managing Partner
Datastream Connexion, LLC
1.888.690.2893
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
