A personal repository of random information in compensation for a fatigued biological computer
Breaded IT » Web Development » Internet Tools » MySQL Snippets
Inline query & case statement:
SELECT mf.Name AS Manufacturer, md.ID AS ModelID, md.manuID, md.KnownAs, md.FrameID, md.CC, md.FirstYear, md.LastYear, md.Alias1, md.Alias2, md.Alias3, md.Alias4, md.OtherParts, ph.ID, StockCount, CASE WHEN md.manuID = '' THEN 1 ELSE 0 END AS ManuChoice FROM Manufacturer mf JOIN Model md ON md.ManuID = mf.ID JOIN PhotoID ph ON md.ID = ph.ModelID JOIN ( SELECT COUNT( sm.ID ) AS StockCount, sm.ModelID FROM StockModel sm JOIN Stock st ON st.ID = sm.StockID WHERE st.StockStatus = 'F' GROUP BY sm.ModelID WHERE md.CC >10 AND NOT upper( left( mf.Name, 1 ) ) = 'z' ORDER BY ManuChoice DESC , StockCount DESC , mf.Name, md.KnownAs, md.LastYear DESC , md.FirstYear DESC |
Trying an 'IN' subquery | SELECT md.ID FROM Model md WHERE md.ID IN ( SELECT DISTINCT md.ID FROM Model md JOIN StockModel sm ON sm.ModelID = md.ID JOIN Stock st ON st.ID = sm.StockID WHERE st.OtherParts = 'Y') |
Works ok |
update using IN clause |
update Model md
set md.OtherParts = 'Y' where md.ID in ( select distinct sm.ModelID from StockModel sm join Stock st on st.ID = sm.StockID WHERE st.OtherParts = 'Y') |
Works ok |
SELECT DISTINCT CC
FROM Model md JOIN Manufacturer mf ON mf.ID = md.ManuID ORDER BY CC DESC LIMIT 0 , 30 |