get rows who have serveral columns matched with an array

[Rutger83]

Hi great people of the autoit community, I am stuck for serveral hours now and hope that this post will help me to move on

The problem is: I have a sql database table with 5 columns, out of this table I want to select the rows of which at least 3 or more columns occure in an array of 8 values

for example:

my database table is like this

| column 1 | column 2 | column 3 | column 4 | column 5 |
row 1| Smith    | Anderson | Clark   | Wright   | Mitchell |
row 2| Johnson  | Thomas   | Rodriguez| Lopez   | Perez |
row 3| Williams | Jackson  | Lewis   | Hill  | Roberts  |

out of this table I would like to get the rows where at least 3 or more columns have an occurrance within this array:

$array[8]=["Jackson","Robinson","Taylor","Williams","Nelson","Perez","Hill","Thomas"]

Thus the above array has three matches with row 3.

I have googled the internet to the end to find a way to do this within a sql query, but I have started to belief that it is not possible.

Now I can think of of another way to this, beeing: using loops where I load the db table in a 2d array.

for $iRow=0 to ubound($dbArray,1)-1
$found=0
for $iColumn=0 to ubound($dbArray,2)-1
    for $iCompare to ubound($compareArray)-1
        if $dbArray[$iRow][$iColumn]=$compareArray[$iCompare] then $found+=1
    next
  next
if $found>=3 then ;this row is a match
next

The problem with this would be that its to slow, a table can have over 100,000 rows, and I want to preform this search over serval of these tables. But a test on one table took about 3 minuts while I am hoping for a time of a few seconds.

I am hoping that there is another way of doing this, I was thinking that maybe combining the columns in a string and doing a regular expresion search on this could be a solution, but I just cant figure out a way to this with regexps (since I havent been experienced with it). So now I am turing to the forumns hoping that someone here can point me to a solution or perhaps somebody knows a way to tackle a situation like this?

Edited March 21, 2012 by Rutger83

[jchd]

The issue you experience is a direct consequence that you're using SQL backwards. Your design is utterly denormalized, hence you end up with awfully complex SQL queries or unduly complex application code.

As a rule of thumb, NEVER, EVER use the columns of any table as an array. Always use SQL the other way round!

Just because SQL is, by its fundamental design, manipulating sets stored as rows of identically structured data, you must avoid creating columns having the same semantic. Since the 5 columns are names and you whish to match any 3 or more in a row, that is a proof that they have the exact same semantic. This is enough to consider reorganizing your DB.

I've no clue what problem you're trying to modelize, but here's a crude DB with the same data you posted, arranged "the SQL way":

Gangs.zip

If you don't already have it, go download SQLite Expert and use it to open this DB. Look at its design and how foreign keys are used to link groups and their members (whatever that means in your context).

Then run the following query in the SQL tab (or in your application!):

select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3

And the answer comes out as 3 before you can breathe.

Is that the result you're after?

EDIT: I always forget to mention that: to use foreign keys with a DB, execute the following statement right after opening it:

PRAGMA foreign_keys=ON;

(FK are OFF by default).

Edited March 20, 2012 by jchd

[jchd]

If you insist on something more explicit, have a look on the enhanced version, where I added a view listing the groups and their members. I've changed the query accordingly to produce a more explicit result.

Gangs2.zip

select * from groupmembers where "group" = (select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3)

Once created, a view may be used in any query just like a true table.

One more note: since names are that common and terribly ambiguous, it all depends on the real semantic (meanings) which apply in your actual context that you leaves names in a plain list (as I did) without duplicates, or differenciate the Lewis which is a member of group 1 (say) and the Lewis which is member of group 48 (say). What dictates a design is the relationships between entities and that is the duty of the people who know the problem at hand.

Edited March 21, 2012 by jchd

[Rutger83]

Great examples jchd! It is the result I am after

I am not experienced with a few methods you used like 'foreign keys' and 'indexes' thus I am reading up on these, but I tried your examples and I have allready learned a lot of them (allthough I havent got the second one working yet, but I am sure I will by the end of the day). Your rule of thumb of how to approach a db might sound logical for a lot of people here, it wasnt for me , but it is now

Thank you very much for your time and the excelent product of your time

Edited March 21, 2012 by Rutger83

[jchd]

Indexes are the easy part. Only create an index if you need one for consistency or for removing a verified performance bottleneck (and then not always). E.g. the unique index on name guarantees you won't be able to insert two Anderson in the Names table and the primary compound index on Members table guarantees you don't make a name doesn't appear more than once in a group. But don't create indexes just guided by the feeling that "it may help": it can be a performance killer as well!

Of course, this is only as an example: your actual problem may require something different, as the relationships between your entities are probably different from those in my toy examples.

For instance if your names are in fact modelling identity of individuals, then my schema is plain wrong, as it won't allow for homonyms (and last names only are way too ambiguous). You'd have to make an Individuals table with one entry for each person and place there the groupID of the group they belong to. You then get rid of the Members table. BUT, if people may belong to more than one group, then you need to keep the Members table and remove the groupID in the Individuals table (repeat: no array in a row).

If, on the contrary, your "names" are unique pseudos of a forum, then my schema is useable.

So you see the design of a DB must correctly model the real-world relationships. That may take time to setup, but please trust me that it always pays back in the long run.

Foreign keys (FK) are the magic wand of any real-world DB. They permit to avoid duplication of data in the DB, replacing the actual data by a reference to the row where the data actually is. They also force consistency: it's impossible to create a membership for an ID (group or name) which isn't already created. Should you delete a name, then it will be removed from its group and the group itself will be deleted in turn (due to the DELETE CASCADE clause used in the FK). It may or not fit your problem, but many other options are available.

A typical DB has a number of tables, and very often much more than you'd think initially (just like my tiny examples), most of them linked by FKs. This is perfectly normal and even if that make many queries use a number of JOIN to "glue" pieces of information together, that small clumsiness is rapidly overcome by the power it gives. It's also common to have queries with a number of sub-selects (often nested to several levels). Even if that makes the SQL look intimidating, don't bother because the engine is precisely tailored to eat that stuff and produce efficient code for delivering the corect result.

Sidenote: should the list of names you're after need to be expanded or be subject to conditions, then don't hesitate to make one or more columns in the Names table to reflect those conditions. Suppose you name one (boolean) column Blacklisted along Names and wish to know which groups have 3+ members blacklisted, you would replace the "IN (<list of literal names>)" in the select query by "IN (select name from Names where blacklisted)" and that's it.

I stop here the list of endless possibilities, that we only scratched here. Moral: SQL is a fast and efficient engine including parts of a programming language (recursive triggers make SQLite a Turing-complete language). You should try to have the consistency and complex queries work done by the engine, while leaving application-level typical task to ... the application.

Don't rush to write AutoIt code. Use SQLite Expert to the max in order to have all setup and working (including INSERT, UPDATE, SELECT statements you'll need). Rework the DB until it reflects the real-world relationships. Then only you can start coding your actual application. Don't forget to always set pragma foreign_keys=ON before use!

If you have questions, you know where to chime.

Edited March 21, 2012 by jchd

[Rutger83]

Well I got a question, how would I get multiple rows as result out of this query?

select * from groupmembers where "group" = (

select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3

)

When

select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3

contains multiple results. It seems that select * from groupmembers where "group" = ( is only executed for the first row of the "sub query".

edit:

this query seems to do it:

select groupmembers.GroupID, groupmembers.members from groups natural join GroupMembers natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupmembers.GroupID having count(*) >= 1

here I added an extra join. (and renamed groupmembers.group to groupmembers.groupID, because group was parsed as a keyword)

Edited March 21, 2012 by Rutger83

[jchd]

You're over-complicating it.

In fact I'm trying to cover my ass since the failure to return multiple rows is mine.

Original query:

select * from groupmembers where "group" = (select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3)

Should be this:

select * from groupmembers where "group" in (select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3)

When the operator is = then it's obvious that the first one to match will be the only result! My bad, I apologize for writing that in a rush.

No, "group" can't be mistaken to a keyword, just because it's within double quotes. This way, you can have fancy schema names (table names, column names, ...) just like "this is a fuc*ing unusual column name, even with Unicode symbols like ¶⅕⋡⍰░▒♬" is a perfectly valid syntax for a schema name (using the double quotes). Note that SQL mandates that schema names be case insensitive, but SQLite only enforce case insensitiveness for ASCII (english) letters. Anyway, GROUP as a keyword must be followed by BY and would be an error placed where "group" is.

In the query, "group" is the alias name of the column in the view and "Members" is the alias name of the column reuslting from group_concat(name, ', '). Should those aliases (something [AS] aliasname) not be there, the view would have groupID and "group_concat(name, ', ')" as names and headers, not very pretty.

(Don't play with the following clue: SQLite even accepts empty schema names but then you must be VERY careful at the syntax you use, else ...)

Keep on playing with the baby and come back any time if you hit a block.

[Rutger83]

Ah in, seeing it makes it logical. Thank you for pointing that out

With the risk of asking for help to soon, I would like to present the following situation. Say I want to exclude names, thus having name NOT beeing IN '(name exclude collection)'. My first thought was to expand the expresion:

where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') to:

where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') AND name NOT in ('Anderson')

But this seems to make no difference.

So now I am making an extra temp view. Beeing a view out of the above query, thus with all groups which have enough names matched:

CREATE VIEW totalResults AS select * from groupmembers where "group" in (select groupid from groups natural join members natural join names where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groupid having count(*) >= 3)

And then I use:

SELECT * FROM totalResults WHERE members NOT LIKE '%Anderson%'

To get the groups which have 3>= of ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') and dont contain %Anderson%

Oh and:"DROP VIEW totalResults" when its done

This works, but it feels like its aswell 'over'complexed.

Another step I am trying to accomplish is to get the names in the group which are present and also the names which are not present, and to be honest I've again resorted to code, loops and arraySearch for example:

3>= ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') matches with group:

('Jackson','Robinson','Taylor','Williams','Boneparte')

An output I am looking for is like this: found: 'Jackson','Robinson','Taylor','Williams' not found 'Boneparte', but I am not there yet with my sql knowledge

Edited March 21, 2012 by Rutger83

[jchd]

AND name NOT in ('Anderson') can't make a difference.

Look again at the query from the inner part: we're selecting rows whose name match the list, then we group by groupid while counting the members and insinting that the count be >= 3. If a row has a name in the NOT list, it won't be counted, BUT that guy may still be part of a group where 3 members are already matching.

What you would do in this case is exclude from the previous set the new set of groups which have blacklisted members. EXCEPT does that with sets but here you can apply you exclusion easily.

Just add:

and "members" not like '%Roberts%'

at the end of the query and you're done.

But as your last § shows you need to have a storage for 'Boneparte' . It's impossible to have it as a litteral in the query and make it appear in the result (lest making it explicit there as well).

To achieve what you want, you need to have "selected" and "blacklisted" names flags in some table, so that the engine will be able to refer to them by mere logic and not comparison to a literal part of the query. Either make one or two tables (using FKs to Names!) or make those characteristics new boolean columns in Names (choice depends on your context).

[Rutger83]

All this experimenting while trying to accomplish new things, trail and error for the progress has learned me alot. But I am a bit adrift from my intended goal -also due to me starting with a wrong approach- the following query brings me a bit towards the shore again.

select groups.groupid, count(*) as remainingNum, group_concat(name, ', ') as remaining

from groups natural join members natural join names

where name NOT in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas') group by groups.groupid having count(*) <=4 and remaining not like '%Roberts%'

ORDER BY remainingNum;

I have dropped the view: 'groupmembers', and put: "group_concat(name, ', ') as remaining" in the select, togeheter with addding NOT to "where name in (' " this gives me the names not matched out of the groups where there are 4 or less remaining names.

Yeah!

Another thing I am trying to acomplish is to get the combinations of the remaining names, with the above query runned on gangs.db3 it gives this result

#|GroupID|remainingNum|remaining      
-+-------+------------+----------------
1|2    |3          |Johnson, Rodriguez, Lopez

Say for example the result I am after should be like this:

#|GroupID|remainingNum|remaining      
-+-------+------------+----------------
1|2    |3          |Johnson, Rodriguez
2|2    |3          |Rodriguez, Lopez
3|2    |3          |Johnson, Lopez

Where I have the feeling that also in this situation I am doing comparison to a literal part of the query (which is impossible?) But to be honest I am still processing your last post so its going to a take a bit longer before I understand it fully. But with risk of overloading my head What would a good aproach be to achieve a result aslike the the above three rows?

Edited March 22, 2012 by Rutger83

[jchd]

I'm glad to see you're well on your way learning SQL[ite].

About combinations, what you want is essentially a CROSS JOIN (= cartesian product) of the set of remaining names.

Of course you can do that in a single query but it's going to be non-trivial. In such cases, the best way to achieve a complex result is to use a temporary table (or view) in a series of queries. That allows you to better understand how to build the desired result.

First let's get rid of the literals and make "selected" and "blacklisted" two new boolean columns in the names table. A few updates are very fast and will make things easier. Remember that all this can be (should be!) made into a single, atomic, transaction.

You query (the names not matched out of the groups where there are 4 or less remaining names) now becomes:

begin immediate;-- set status for some membersupdate names set selected = 1 where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas');update names set blacklisted = 1 where name in ('Roberts');-- issue query and grab result hereselect groups.groupid, count(*) as remainingNum, group_concat(name, ', ') as remainingfrom groups natural join members natural join nameswhere not selected and not blacklisted group by groups.groupid having count(*) <= 4ORDER BY remainingNum;-- restore member statusupdate names set selected = 0, blacklisted = 0;commit;

You get:

GroupID remainingNum remaining              
------- ------------ -------------------------
      3 1           Lewis                   
      2 3           Johnson, Rodriguez, Lopez

I'll post about 2-combinations from there later in the afternoon.

Edited March 22, 2012 by jchd

[jchd]

Here's how you can query for 2-combinations.

drop table if exists "temp";
-- make a temporary table with groups which qualify (we need at least 2 members to make 2-combinations!)
create table "temp" as
select groupid
from groups natural join members natural join names
where not selected and not blacklisted group by groupid having  count(*) between 2 and 4;
-- now make a self join. In fact we want a half self join (A, is the same as (B,A), hence the t1.nameid < t2.nameid condition
select t1.groupid "Group ID", t1.name "Name #1", t2.name "Name #2" from
("temp" natural join members natural join names) t1
join
("temp" natural join members natural join names) t2
on
t1.nameid < t2.nameid and t1.nameid != t2.nameid
and not t1.selected and not t1.blacklisted
and not t2.selected and not t2.blacklisted;

Edited March 22, 2012 by jchd

[Rutger83]

Wow that is very usefull, thank you so much for beeing so chivalrous. Both examples are very clear and helpfull and will give me hours to play around with. I will try to adjust it so that I dont lose groups in which 0 are 1 persons are needed and some other things. To accomplish this I bought a sql book today . Late in the evening I will post my progress and will hopefully be showing off

Edited March 22, 2012 by Rutger83

[jchd]

You're welcome. You know it's fairly rewarding to see someone willing to go forward by learning, so the time spent giving you simple advices is a good and pleasant investment in the global community.

Also every SQL engine has it's own particularities. While grabbing "standard" SQL is a good thing, reading the SQLite docs is required to squeeze the most of it (and avoid pitfalls).

Edited March 22, 2012 by jchd

[Rutger83]

I was hoping I could show something but I havent made any real progress with the query yet.

-ignoring the combinations for a moment- I do understand why it does not collect the groups with 0 remaining with a 'where' like this:

where not selected and not blacklisted group by groupid having count(*) <= 4;

Simply because not one name is true with: 'not selected'. I thought that extending the where clause with an "or count(*) is NULL" would perhaps make it throw out the rows with 0 remaining but this was without succes. Then I read about 'case and when' in sql, so I tried something simulair as the pervious attempt but this time trying to convert NULL to 0

select groupid, case when count(*) is NULL then 0 else count(*) end as remainingNum, group_concat(name, ', ') as remainingfrom groups natural join members natural join nameswhere (not selected and not blacklisted) OR (remainingNum=0) group by groups.groupid having count(*) <= 4

But this isnt a step forward.

At the moment I am wondering if I should make a temporary table with the groupids which dont have remaining names. And use a union to combine this table with a table having the groupids of every group which has four or less remaining. But first I am reading and trying left,right,inner and outer joins

Edited March 22, 2012 by Rutger83

[jchd]

That's a slightly distinct problem. But note that you're changing the query for remaining people while you'd like to see groups with no remaining!

For example I inserted group 4:

Group    Members
1    Smith, Anderson, Clark, Wright, Mitchell
2    Johnson, Thomas, Rodriguez, Lopez, Perez
3    Williams, Jackson, Lewis, Hill, Roberts
4    Joe, Jack, Jackie, Sue, Helen

and made those members selected:

NameID  Name    Selected    Blacklisted
1   Smith   True    False
2   Anderson    True    False
3   Clark   False   False
4   Wright  True    False
5   Mitchell    False   False
6   Johnson False   False
7   Thomas  False   False
8   Rodriguez   False   False
9   Lopez   False   False
10  Perez   False   False
11  Williams    False   False
12  Jackson False   False
13  Lewis   False   False
14  Hill    False   False
15  Roberts False   True
16  Joe True    False
17  Jack    True    False
18  Jackie  True    False
19  Sue True    False
20  Helen   True    False

Then the following shows what you want, reversing the selected condition:

select groupid, count(*) as selectedNum, group_concat(name, ', ') as selected
from groups natural join members natural join names
where selected and not blacklisted group by groupid having selectedNum > 1
ORDER BY selectedNum;

GroupID selectedNum selected
1   3   Smith, Anderson, Wright
4   5   Joe, Jack, Jackie, Sue, Helen

Edited March 22, 2012 by jchd

[Rutger83]

Ofcourse, you are right. So to combine the results, I use:

select groups.groupid, count(*) as remainingNum, group_concat(name, ', ') as remainingfrom groups natural join members natural join nameswhere not selected and not blacklisted group by groups.groupid having count(*) <= 4UNIONselect groupid, 0 as remainingNum, "" as remainingfrom groups natural join members natural join nameswhere selected and not blacklisted group by groupid having count(*) =groups.SIZE

to combine the two result sets I added an extra column to groups: size, which is the size of a group. So count(*) =groups.size equals no names remaining and no names blacklisted in the group.

edit:

create table "temp" asselect groupid, count(*) as remainingNum, group_concat(name, ', ') as remainingfrom groups natural join members natural join nameswhere not selected group by groups.groupid having count(*) <= 3UNIONselect groupid, 0 as remainingNum, "" as remainingfrom groups natural join members natural join nameswhere selected and not blacklisted group by groupid having count(*) =size;select * from "temp" where remaining NOT LIKE "%Robertson%" ORDER BY remainingNum

I was not getting the results I wanted. I want to exclude a group which has a blacklisted name remaining. At this moment I did this by putting the results of the union in a table called "temp" and selecting by where remaining NOT LIKE "%Robertson%" to exclude groups which need Robertson. to be continued

Edited March 23, 2012 by Rutger83

[Rutger83]

To get the combinations I am intrested in, I again used a union.

I make a second combination select, with the aim to combine a single remaining name with all the names left which are not flagged, like this (where group 3 with lewis is a single name group) :

Group ID |  Name #1 |   Name #2 |
2   Johnson Lewis
2   Johnson Lopez
2   Johnson Rodriguez
2   Lopez   Lewis
2   Rodriguez   Lewis
2   Rodriguez   Lopez

3   Lewis   Anderson
3   Lewis   Clark
3   Lewis   Johnson
3   Lewis   Lopez
3   Lewis   Mitchell
3   Lewis   Rodriguez
3   Lewis   Smith
3   Lewis   Wright

select t1.groupid "Group ID", t1.name "Name #1", t2.name "Name #2" from
("temp" natural join members natural join names) t1
join names t2
on
t1.nameid != t2.nameid
and not t1.selected and not t1.blacklisted
and not t2.selected and not t2.blacklisted
WHERE t1.remainingNum=1
UNION
-- union with the other combinations select
where t1.remainingNum>1; --edit: this is probally not needed since if there is just one there wont be a join (at least not a join like this)

this works , allthough I got a icky feeling about unions and thus two selects running trough the db instead of one. But I am far form done and calling it quits so.. to be continued

Everything for these combinations

-- restore member status
update names set selected = 0, blacklisted = 0;
drop table if exists "temp";

-- set status for some members
update names set selected = 1 where name in ('Jackson','Robinson','Taylor','Williams','Nelson','Perez','Hill','Thomas');
update names set blacklisted = 1 where name in ('Roberts');


-- make a temporary table with groups which qualify
create table "temp" as
select count(*) as remainingNum, groupId, group_concat(name,', ') as remaining
from groups natural join members natural join names
where not selected and not blacklisted group by groupid having count(*) <= 3;

-- now make a self join. In fact we want a half self join (A, is the same as (B,A), hence the t1.nameid < t2.nameid condition
select t1.remainingNum as RemainingNum, t1.remaining as Remaining, t1.name "Name #1", t2.name "Name #2"
from ("temp" natural join members natural join names) t1
join
(names) t2
on
t1.nameid != t2.nameid
and not t1.selected and not t1.blacklisted
and not t2.selected and not t2.blacklisted
WHERE t1.remainingNum=1
UNION
select t1.remainingNum as RemainingNum, t1.remaining as Remaining, t1.name "Name #1", t2.name "Name #2"
from ("temp" natural join members natural join names) t1
join
("temp" natural join members natural join names) t2
on
t1.groupid=t2.groupid
and t1.nameid < t2.nameid and t1.nameid != t2.nameid
and not t1.selected and not t1.blacklisted
and not t2.selected and not t2.blacklisted
WHERE t1.remainingNum>1

expandcollapse popup

            
                


    Edited March 23, 2012 by Rutger83
    
    

            
        

        

        
    

    

    




    Link to comment
    
        
    
    
    

    
    Share on other sites
    

    
        
            

    

        
            

    

        
            

    

        
            

    

        
    


    
    More sharing options...

    


    

                    
                    
                    
                

                    

                    
                    





    

    

    
        
            
                


    
        
    

                
                
                    
                        

                    
                
            
        
        
            
                


jchd
            
            
                Posted March 23, 2012
                
            
        
    
    
        


jchd
            
        
        
            
                
                    


    
        
    

                    
                    
                        

                    
                
            
            
                MVPs
                
                    
                
            
            
                
                    
                        
                            
                                
                            
                                 9.8k
                            
                                
                            
                        
                        
                            
                                
                                    
                                        
                                        97
                                
                                    
                                
                            
                        
                    
                
            
            
                

    
    
        
Infinitely drawing infinity
    
    

            
        
    
    
        



    
        
            
                
                    
                    
                    
                    
                    
                
            
            
                
                    
                    
                        
                        
                            Share
                        
                        
                        
                        
                        
                            
                                
                            
                            
                            
                            
                            
                            
                        
                    
                
                
            
        

        
            Posted March 23, 2012
            
            
                
                    (edited)
                
                
            
        
    

    

    

    
        
        
            Geez, it gets a little bit confusing now.

About the group size: this information is redundant and hard to maintain.  When you change the status of some people, remove people, etc the size has to be updated in sync, which isn't trivial.  If you need a groupsize information (without having to count() # of members of the group), you can still --and should-- maintain it automatically by using SQL triggers.  More about this someday.

We're going into gory details about obscure queries but you still didn't answer important questions, whose answer may invalidate the design itself.  Are names unique?  In the names list, in groups?  In other words, may a group have two (distinct) people called Lewis?  May some Joe be member of more than one group? Are groups subject to shrink, expand, disappear, ...?  Same for people. Same for "selected" & "blacklisted" statuses.  Same about combinations (the query and result of post #18 sounds confusing).

In post #17 your union merges sets from people selected and not selected.  How can that have a meaning?  My guess is that the gang, group, member, selected, blacklisted metaphor is not a correct model for your actual problem.  I agree I was the one to coin those terms here, but it was out of thin air and my feeling is that we're loosing tracks.

Can you explain in plain english more about the real-world problem and the rules you're trying to modelize?  Until then, I fear we're gonna run rings around the beef without ever eating it.


            
                


    Edited March 23, 2012 by jchd
    
    

            
        

        

        
            

    
        

        
            
This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe hereRegExp tutorial: enough to get startedPCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.
SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

        
    

        
    

    

    




    Link to comment
    
        
    
    
    

    
    Share on other sites
    

    
        
            

    

        
            

    

        
            

    

        
            

    

        
    


    
    More sharing options...

    


    

                    
                    
                    
                

                    

                    
                    





    

    

    
        
            
                


    
        
    

                
                
                    
                        

                    
                
            
        
        
            
                


Rutger83
            
            
                Posted March 24, 2012
                
            
        
    
    
        


Rutger83
            
        
        
            
                
                    


    
        
    

                    
                    
                        

                    
                
            
            
                Active Members
                
            
            
                
                    
                        
                            
                                
                            
                                 42
                            
                                
                            
                        
                        
                    
                
            
            
                

            
        
    
    
        



    
        
            
                
                    
                        Author
                    
                    
                    
                    
                    
                
            
            
                
                    
                    
                        
                        
                            Share
                        
                        
                        
                        
                        
                            
                                
                            
                            
                            
                            
                            
                            
                        
                    
                
                
            
        

        
            Posted March 24, 2012
            
            
                
                    (edited)
                
                
            
        
    

    

    

    
        
        
            I must admitt that during testing and trying things over the last couple of posts, I thought of new exceptions and rules which changed my intentional goal a bit. But since yesterday I have a some sort of clear picture (from begining to end) in my head.Example: The candy factoryIn the candy factory 10 types of candy bags are produced out of 80 different candies.So: There are 10 unique group types(bags) with 5 different sizes: (2,3,4,5,6);a group is combination out of 80 unique candies.Out of this I make a database, (with some rules about which candy combinations gets into a group).At this point I have a database with 40791 unique candy bags.Step 1: is to compare a collection of candies with all the candy bags, to have the bags which are still possible togheter with the candies missing returned.A collection of candies to compare is subject to these rules.1) the minimum size is 32) the maximum size is 63) every collection has atleast two wildcard candies, and at a minimum collection size (8-3=5) a maximum of 5 wildcard candies (these wildcard candies can be any of the non selected and non blacklisted candies)Step 2: out of this return result, pairs are made based upon the missing candies.The rules for these pairs are:1) A pair should be a combination of two missing candies2) if only one candy is missing for a bag, this candy should pair up with every non selected and non blacklisted candy.3) If no candies are missing for a bag, pairs should be made with every non selected and non blacklisted candy.Step 3: Making a ranking of pairs based upon the returned results of step 2.A pair can be member of serval bags of candy, but for the ranking I am only intrested in the highest ranking candy bag with the least amount of candies needed and the highest ranking candy used.example database: candies.rarFor step 1, I have at the moment:-- restore candy statusupdate candies set selected = 0, blacklisted = 0;-- set status for some candiesupdate candies set selected = 1 where name in ('candy01','candy02','candy03','candy04');update candies set blacklisted = 1 where name in ('candy08');-- select the remaining groups of candyselect  groupId, GroupType, max, count(*) as remainingNum, group_concat(name,', ') as remainingfrom groups natural join members natural join candieswhere not selected and not blacklisted group by groupid having  count(*) <= 4;

For step 2 (plus step 1), I have this:

drop table if exists "temp";-- restore candy statusupdate candies set selected = 0, blacklisted = 0;-- set status for some candiesupdate candies set selected = 1 where name in ('candy01','candy02','candy03','candy04');update candies set blacklisted = 1 where name in ('candy08');-- select the remaining groups of candycreate table "temp" asselect  groupId, GroupType, max, count(*) as remainingNum, group_concat(name,'') as remainingfrom groups natural join members natural join candieswhere not selected and not blacklisted group by groupid having  count(*) <= 4;-- making a combinations select for 1 remaining and a select for 2 or more remaining. Using union to combineselect t1.groupId as GroupID, t1.GroupType as Gtype, t1.remainingNum as RemaingNum, REPLACE(REPLACE(t1.remaining,t1.name,''),t2.name,'') as remaining, t1.name "Name #1", t2.name "Name #2", t1.max as maxfrom ("temp" natural join members natural join candies) t1join(candies) t2ont1.CandyID != t2.CandyIDand not t1.selected and not t1.blacklistedand not t2.selected and not t2.blacklistedWHERE t1.remainingNum=1UNIONselect t1.groupId as GroupID, t1.GroupType as Gtype, t1.remainingNum as RemaingNum, REPLACE(REPLACE(t1.remaining,t1.name,''),t2.name,'') as remaining, t1.name "Name #1", t2.name "Name #2", t1.max as maxfrom ("temp" natural join members natural join candies) t1join("temp" natural join members natural join candies) t2ont1.groupid=t2.groupidand t1.CandyID < t2.CandyID and t1.CandyID != t2.CandyIDand not t1.selected and not t1.blacklistedand not t2.selected and not t2.blacklistedWHERE t1.remainingNum>1  

And at step 3, I add this to the union in step 2

GROUP BY "Name #1","Name #2"ORDER BY  Gtype ASC, remainingNum ASC,  max DESC

Edited March 24, 2012 by Rutger83