On Thu, 14 Jul 2005, Peter Brawley wrote: >Dan, > >Hi, I remember reading about an SQL query type which did something like >select all 'aircraft hangers' which contained exactly (or at least) some >given set of aircraft. > >Or did it select the list of pilots qualified to fly all the airplanes in >the hanger... > >Anyway, I forget the syntax (and the fancy name for this kind of query). > > >I think the concept you're after is relational division. The aeroplane >hangar was one of Celko's examples. Another from him is at >http://www.artfulsoftware.com/queries.php#28, other examples at >http://www.artfulsoftware.com/queries.php#22, >http://www.artfulsoftware.com/queries.php#33,
Cheers, I will try to rephrase my query below as 'RELATIONAL DIVISION' :) In the short term I solved my problem with group_concat_max_len system variable - For some reason I thought it would already be at the maximum lenght. I doubled it up and saw my warnings dissapear Warning: 1260 Thanks very much for the above links, Dan. > >PB > > >Dan Bolser wrote: > >>Hi, I remember reading about an SQL query type which did something like >>select all 'aircraft hangers' which contained exactly (or at least) some >>given set of aircraft. >> >>Or did it select the list of pilots qualified to fly all the airplanes in >>the hanger... >> >>Anyway, I forget the syntax (and the fancy name for this kind of query). >> >>What I want to do is the following, given this data... >> >> >>Table: ATTRIBUTE_LIST; >> >>ID ATTRIBUTE >>W A >>W B >>W C >>X A >>X B >>X C >>Y A >>Y B >>Y C >>Y D >>Z E >> >>--> SQL MAGIC --> >> >> >>Table: CLUSTERS >> >>G_ID ID >>1 W >>1 X >>2 Y >>3 Z >> >> >>That is, to group together all ID's with the same 'set' of ATTRIBUTES. >> >>Currently I am doing this using 'GROUP_CONCAT', but my attribute list just >>went above the limit for the GROUP_CONCAT column... >> >>+---------+------+--------------------------------------+ >>| Level | Code | Message | >>+---------+------+--------------------------------------+ >>| Warning | 1260 | 7 line(s) were cut by GROUP_CONCAT() | >>+---------+------+--------------------------------------+ >> >>My query looks roughly like this... >> >> >>SET @i:=0, @x:='', @row:=''; >># >>DROP TABLE CLUSTERS; >>CREATE TABLE CLUSTERS >> (PRIMARY KEY (ID), INDEX (G_ID)) >># >>SELECT >> ID, G_ID >> # >>FROM >>( >> SELECT >> ID, >> # >> @x:= ATTR_LIST AS HIDDEN1, >> # >> IF(@row = @x, @i, @i:[EMAIL PROTECTED]) AS G_ID, >> # >> @row:= @x AS HIDDEN2 >> # >> FROM >> ( >> SELECT >> ID, >> GROUP_CONCAT(ATTRIBUTE) AS ATTR_LIST, >> FROM >> ATTRIBUTE_LIST >> GROUP BY >> ID >> # >> ) AS vt1 >> # >> ORDER BY -- This is very important for >> ATTR_LIST -- the overall query. >> # >>) AS vt2; >> >>(And thats the highly simplified version!) >> >> >>I can't shake the feeling that this 'string based' approach (while quite >>speedy) is inherently messy, and that a proper 'set based' approach >>should exist, and shouldn't have the limitation in the number of >>attributes that the above method has. >> >>In general I would really like to (somehow) develop a suite of easy to use >>'SQL CLUSTER' commands, as the data mining community needs that kind of >>thing in nice general (set based) abundance :) >> >>Anyway, thanks for any feedback on any of the above, >> >>Dan. >> >> >> >> > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
