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
) AS st ON st.ModelID = md.ID
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