Professional Web Applications Themes

select rows that span column alternatives - MySQL

Hi I have a table like: idx col1 col2 col3 1 a p null 2 a r 23 3 b p 12 4 b p 16 5 a r 3 6 a r null so col1 have possible vaules {a,b}, col2 have {p,r} while col3 are null or an integer. I wish to extract those rows that give me all combinations of values in the columns. Eg. to get all possible values in col1 I'll extract rows 1,3, but those only give me the value 'p' for col2. Therefore I need to extract another row for instance row 2. Now ...

Sponsored Links
  1. #1

    Default select rows that span column alternatives

    Hi

    I have a table like:

    idx col1 col2 col3
    1 a p null
    2 a r 23
    3 b p 12
    4 b p 16
    5 a r 3
    6 a r null

    so col1 have possible vaules {a,b}, col2 have {p,r} while col3 are
    null or an integer.

    I wish to extract those rows that give me all combinations of values in
    the columns.
    Eg. to get all possible values in col1 I'll extract rows 1,3, but those
    only give me the value 'p' for col2.
    Therefore I need to extract another row for instance row 2.
    Now I'll have all possible choices for columns 1 and 2, and by chance
    also for col3 (since I treat it as either null or not null).

    To get all combinations I need (several choices may be possible, and
    optimally I'd like the least amount of rows, but that's another issue):

    idx col1 col2 col3
    1 a p null
    2 a r 23
    3 b p 12
    6 a r null # to get the combo col2=r and col3=null

    So I'd like the query to return rows 1, 2, 3 and 6.
    For this dataset the combination col1=b and col3=null (and also col1=b
    and col2=r) doesn't exist and should obviously not be returned.

    So my question is:
    How do I write this in SQL?


    Cheers,
    Sam

    Sponsored Links
    mrsamme@gmail.com Guest

  2. #2

    Default Re: select rows that span column alternatives


    com wrote: 

    Something like this possibly:

    SELECT idx, `col1` , `col2` , IF( `col3` , 'NOT NULL', 'NULL' ) mycol3
    FROM `mrsa`
    GROUP BY col1, col2, mycol3

    Captain Guest

  3. #3

    Default Re: select rows that span column alternatives


    Thanks!

    It took some tweaking to suit my actual tables but no it works fine!

    mrsamme@gmail.com Guest

  4. #4

    Default Re: select rows that span column alternatives


    com wrote: 

    Glad to be of service.

    Captain Guest

Similar Threads

  1. Span rows/columns?
    By Eagle in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: May 18th, 09:11 PM
  2. DataGrid (body only) contained in <span>...</span> tags
    By Dan Bishop in forum ASP.NET Data Grid Control
    Replies: 2
    Last Post: June 7th, 04:00 PM
  3. How can I make a header column span?
    By Stan in forum ASP.NET General
    Replies: 5
    Last Post: August 12th, 03:39 AM
  4. Select rows where other related rows don't exist
    By Dmand in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 2nd, 06:23 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139