with - subquery reference parent query
MySQL correlated subquery in JOIN syntax (5)
I would write it with multiple joins. When you say that it "would possibly incur additional overhead" that tells me that you haven't tested it to be sure. If you have decent indexes the joins should be pretty trivial.
This also shows just one of the pitfalls of the generic "hold everything" table design pattern.
I would like to provide a WHERE condition on an inner query by specifying innertable.id = outertable.id. However, MySQL (5.0.45) reports "Unknown column 'outertable.id' in 'where clause'". Is this type of query possible?
The inner query is pivoting rows to columns using a GROUP BY. This could be entirely be performed in the outer query, but would possibly incur additional overhead due to the extra joins.
Alternatively, I can leave off the WHERE condition in the inner query and instead specify an ON outertable.id = innerquery.id, but it would then fetch the entire inner query rowset to join again the outer, which is inefficient.
The actual SQL appears below:
select t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, tp.subject, tp.contents, a.PhoneNumber, a.Location, a.Extension, a.BusinessUnit, a.Department from swtickets t inner join swticketposts tp on t.ticketid = tp.ticketid inner join swusers u on t.userid = u.userid left join ( select cfv.typeid, min(case cfv.customfieldid when 1 then cfv.fieldvalue end) as 'PhoneNumber', min(case cfv.customfieldid when 3 then cfv.fieldvalue end) as 'Location', min(case cfv.customfieldid when 5 then cfv.fieldvalue end) as 'Extension', min(case cfv.customfieldid when 8 then cfv.fieldvalue end) as 'BusinessUnit', min(case cfv.customfieldid when 9 then cfv.fieldvalue end) as 'Department' from swcustomfieldvalues cfv where cfv.typeid = t.ticketid group by cfv.typeid ) as a on 1 = 1 where t.ticketid = 2458;
Since the newest 15 will always come from the first 15 if you order them by descending order.You can just delete any id that did not make it into the first 15. like so i just tried it and it worked fine. Hopefully it helps someone
Delete from `table` where id not in (SELECT * FROM (Select id from `table` order by id desc limit 15) as derivedTable);
You can try this:
DELETE p1.* FROM posts p1 INNER JOIN (SELECT id FROM posts ORDER BY timestamp DESC LIMIT 0, 15 ) AS p2 ON p1.id = p2.id;
You're using the Entity-Attribute-Value design, and there's ultimately no way to make this scalable if you try to generate conventional result sets. Don't try to do this in one query.
Instead, query your normalized tables first:
SELECT t.ticketid, u.userid, t.fullname, u.loginapi_userid, t.email, tp.subject, tp.contents FROM swtickets t INNER JOIN swticketposts tp ON (t.ticketid = tp.ticketid) INNER JOIN swusers u ON (t.userid = u.userid) WHERE t.ticketid = 2458;
Then query your custom fields, with the result on multiple rows of the result set:
SELECT cfv.customfieldid, cfv.fieldvalue FROM swcustomfieldvalues cfv WHERE cfv.typeid = 2458;
You'll get multiple rows in the result set, one row for each custom field:
+---------------+--------------+ | customfieldid | fieldvalue | +---------------+--------------+ | 1 | 415-555-1234 | | 3 | Third office | | 5 | 123 | | 8 | Support | | 9 | Engineering | +---------------+--------------+
You then need to write application code to map the result-set fields to the application object fields, in a loop.
Using an Entity-Attribute-Value table in this way is more scalable both in terms of performance and code maintenance.
mySQL subquery limit
DELETE FROM posts WHERE id not in ( SELECT * FROM ( SELECT id FROM posts ORDER BY timestamp desc limit 0, 15 ) as t);