Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2012
    Posts
    13

    Unanswered: Running bitwise operations on bit SELECTs

    I have two SELECT queries that select the bit column from two different tables. Now I'd like to do a bitwise AND on all combinations of all the rows from these tables. Something akin to

    SELECT (SELECT ...) & (SELECT ...)

    though this of course turns out not to work. But how do I do this then?

  2. #2
    Join Date
    Jan 2012
    Posts
    84
    A cross join is a kind of the join that combines (joins) all rows from one table to all rows in another table.
    My guess is that you need a query like this:
    Code:
    SELECT table1.fieldA & table2.fieldB
    FROM table1
    CROSS JOIN table2

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I want to express one of my opinions for formatting a query(especially SELECT statement).

    I like the following way.

    How do you thought with that? Weocome other opinions!

    Code:
    SELECT <column1/expression1>
         , <column2/expression2>
         ...
     FROM
           table1
     CROSS JOIN
    [INNER JOIN]
    [LEFT  OUTER JOIN]
    [RIGHT OUTER JOIN]
           table2
      ON   <join condition1>
       AND <join condition2>
       ...
     WHERE <condition1>
       AND <condition2>
       ...
    
     GROUP BY
           <grouping column1/expression1>
         , <grouping column2/expression2>
         ...
     ORDER BY
           <ordered column1/expression1>
         , <ordered column2/expression2>
         ...
    The reasons/rationals are:
    (1) Other than SELECT clause subordinate to SELECT, and all other clauses not dependent each other.
    So, all other clauses start by one charachter right to SELECT.
    (2) Joined table1 and table2 are equal, so it would be better to place same column in a line.
    (3) An ON clause is subordinate to a JOIN clause,
    so it would be better to place (one) right place in a line than JOIN keyword.
    (4) Positions of clumn names in GROUP BY clayse and ORDER BY clause are same as SELECT clause,
    by this way.
    (5) If placed AND conditions in this way, all the positions of the condttions are same in ON/WHERE/GROUP BY/ORDER BY clauses.
    Last edited by tonkuma; 04-26-12 at 12:45.

  4. #4
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    I want to express some issues for the way of formatting(especially SELECT statement).
    Can you please stop posting the same stuff over and over again?

  5. #5
    Join Date
    Apr 2012
    Posts
    13
    Thanks kordirko, that worked like a charm!

    One question though. I want to limit the rows to those that only occur once after ANDing the two columns. There's no point in calculating the AND twice, so I'd be better off saving it in a variable, and then using that variable in the HAVING clause. But how would I go about doing that?

  6. #6
    Join Date
    Jan 2012
    Posts
    84
    Quote Originally Posted by ThemePark View Post
    Thanks kordirko, that worked like a charm!

    One question though. I want to limit the rows to those that only occur once after ANDing the two columns. There's no point in calculating the AND twice, so I'd be better off saving it in a variable, and then using that variable in the HAVING clause. But how would I go about doing that?

    Do you mean that 'only those which count is 1' ?

    Code:
    SELECT table1.fieldA & table2.fieldB AS result
    FROM table1
    CROSS JOIN table2
    GROUP BY result
    HAVING COUNT(*) = 1

  7. #7
    Join Date
    Apr 2012
    Posts
    13
    Yes that is perfect, thank you.

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by shammat View Post
    Can you please stop posting the same stuff over and over again?
    I'm very sorry.

    I didn't realised that I posted twice, may be because of slow network response.
    It is my mistake.

    I tried to remove it after I realised it.
    But, I couldn't that nor I couldn't edit my post,
    even if I saw at the bottom
    Posting Rules
    You may post new threads
    You may post replies
    You may post attachments
    You may edit your posts

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by tonkuma View Post
    I want to express one of my opinions for formatting a query(especially SELECT statement).

    I like the following way.

    How do you thought with that? .
    Hi, Tonkuma. I agree with most of your formatting rules, the one exception I make is to place table on same line as FROM / JOIN. Not a formatting rule, but I also remove INNER / OUTER since I find SQL very chatty.

    Code:
    SELECT <column1/expression1>
         , <column2/expression2>
         ...
     FROM table1
     CROSS JOIN
    [JOIN]
    [LEFT JOIN]
    [RIGHT JOIN]  table2

Posting Permissions

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