Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: SQL Permutation

  1. #1
    Join Date
    Dec 2003
    Posts
    7

    Unanswered: SQL Permutation

    Hi;
    Here is my question:

    1. I have 16 columns on my database.
    2. I need to open 16 empty box on user interface.
    3. User can fill any number of them. (For example; 10 of 16)
    4. User will select a number from a combobox. (2,3,4,5...) (For example; 4)
    (HERE IS THE PROBLEM!)
    5. I have to make a query with this filled boxes that get result of ANY 4 OF THAT 10 COLUMS MATCH.
    There is 10!/4!*(10-4)! combination in math.

    Is there any simple way instead of using nested queries?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Dynamic sql?

    Is this Access, SQL Server, Java, MSDE, what?

    Columns are in tables, not databases...a table is an object in a database...

    what does the TABLE look like..

    Does the selection critera for each "box" go against 1 column? or all columns?

    need a little help here with a clearer explination.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Dec 2003
    Posts
    7

    ...

    1. i know that tables are database objects.
    2. i know columns are on tables not on databases.
    3. After my writing faults...
    Yes, dynamic sql.
    And the selection critera for each "box" go against only that column not all columns.

    ________________________
    A | B | C | D | E | F | G | H |
    ________________________
    1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 |

    For example:
    User can say;
    A=1 and C=3 and D=4 and E=5 and F=6
    And if he say "any of 3", query can be;
    1,3,4
    3,4,5
    4,5,6
    1,4,5
    1,5,6
    1,3,5
    1,3,6
    ...

    I don't want to write complex sql queries. If anybody can find an another solution it will be perfect, because the column number and data can increase.

    Any idea?

    Suhan Duman
    Software Engineer
    MCSE,MCDBA
    CLS
    IBM DB2 Certified

  4. #4
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    You mean to say .. you allow a person to fill in at max 16 boxes and you then generate all possible combinations for that.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Got it...you building a search engine?

    I think you need to use some form of ranking using CONTAINSTABLE

    Where's the Oracle cert?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You'll need to make sure the columns are set up for fulltext indexed...

    check out this topic in the bol index...

    good luck
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Didnt get that ... what are you talking about Brett ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You mean this?

    CONTAINSTABLE
    Returns a table of zero, one, or more rows for those columns containing character-based data types for precise or fuzzy (less precise) matches to single words and phrases, the proximity of words within a certain distance of one another, or weighted matches. CONTAINSTABLE can be referenced in the FROM clause of a SELECT statement as if it were a regular table name.

    Queries using CONTAINSTABLE specify contains-type full-text queries that return a relevance ranking value (RANK) for each row. The CONTAINSTABLE function uses the same search conditions as the CONTAINS predicate.
    or the Cert?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by Brett Kaiser
    Got it...you building a search engine?

    I think you need to use some form of ranking using CONTAINSTABLE

    Where's the Oracle cert?
    I mean these
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  10. #10
    Join Date
    Dec 2003
    Posts
    7

    thanks all...

    but;
    i am not making a search engine.
    and also i am not sure that Contains or Containstable statements will help my problem. Because they are for text searching and they can get the result like that: 'APPLE' word in 'APPLE TREE' or in 'OLD APPLE TREE'

    I mean;
    You have to get the result of all combinations. For example;
    You have 10 column in a table,
    User fills 6 boxes, and says "AT LEAST 3",
    Query must get the result of ALL;
    XXX
    XXXX
    XXXXX
    XXXXXX
    combinations.

    As you see that i have to write a very complex query.
    I have to say;

    select ... from ... where COL1=1 and COL2=2 and COL3=3
    select ... from ... where COL1=1 and COL2=2 and COL3=3 and COL4=4
    select ... from ... where COL1=1 and COL2=2 and COL3=3 and COL4=4 and COL5=5
    select ... from ... where COL1=1 and COL2=2 and COL3=3 and COL4=4 and COL5=5 and COL6=6

    It can seem as simple but, this is only a sample. I have more columns and user can say "AT LEAST 1","AT LEAST 5","AT LEAST 10" ... etc

    In that case, it will be hard to create the query.
    Actually i think that, i have to do it programatically but, does anyone has a good sql trick to help me?

    Thanks again...

    Suhan

  11. #11
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    That's why I mentioned the rankikng....it'll tell you the percentage hit...

    Other wise it's a dynymaic loop to build n factorial OR Clauses...

    Or how about interegation?

    IF EXISTS(SELECT 1 FROM myTable WHERE Col1 = @Col1)
    SET @X1 = 1
    IF EXISTS(SELECT 1 FROM myTable WHERE Col12 = @Col2)
    SET @X2 = 1
    IF EXISTS(SELECT 1 FROM myTable WHERE Col3 = @Col3)
    SET @X3 = 1


    Then interogate the variables, and then build the dynamic sql..

    just a thought

    hth
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Here's my try:

    select *
    from YourTable
    where (case when Column1 = @Combo1 then 1 else 0 end
    + case when Column2 = @Combo2 then 1 else 0 end
    + case when Column3 = @Combo3 then 1 else 0 end
    + case when Column4 = @Combo4 then 1 else 0 end
    + case when Column5 = @Combo5 then 1 else 0 end)
    >= @MinimumMatches

    Extend if for you 16 columns and combo boxes.

    blindman

  13. #13
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    ooooooo...I like it...

    didn't think (what again?) of that one....cool

    gotta test that one...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I tried to do it using isnull and nullifs, 'cause I don't like case statements, but I couldn't find an elegant solution.

    blindman

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...it's doing a clustered index scan (beats me..didn't know there was an index on these columns..
    Code:
    USE Northwind
    GO
    
    DECLARE   @ShipName nvarchar(80), @ShipAddress nvarchar(120), @ShipCity nvarchar(30)
    	, @ShipRegion nvarchar(30), @ShipPostalCode nvarchar(20), @ShipCountry nvarchar(30)
    	, @MinimumMatches int
    
    SELECT    @ShipName = 'QUICK-Stop', @ShipAddress = '123 Main', @ShipCity = 'Cunewalde'
    	, @ShipRegion = 'NE', @ShipPostalCode = '12345', @ShipCountry = 'Germany'
    	, @MinimumMatches = 3
    
    SELECT *
      FROM  Orders
     WHERE (  CASE  WHEN ShipName 	    = @ShipName		THEN 1 ELSE 0 END
    	+ CASE  WHEN ShipAddress    = @ShipAddress	THEN 1 ELSE 0 END
    	+ CASE  WHEN ShipCity	    = @ShipCity 	THEN 1 ELSE 0 END
    	+ CASE  WHEN ShipRegion     = @ShipRegion	THEN 1 ELSE 0 END
    	+ CASE  WHEN ShipPostalCode = @ShipPostalCode	THEN 1 ELSE 0 END
    	+ CASE  WHEN ShipCountry    = @ShipCountry	THEN 1 ELSE 0 END)
    	>= @MinimumMatches
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •