Home > IT Answers > Development > Difficult SQL query, is this possible?
Help

Question

  Asked: Apr 16 2008   11:37 PM GMT
  Asked by: David Phillips

Difficult SQL query, is this possible?


SQL, Query, Query tuning, Development, Developers, Database, SQL queries

Two tables,

First Table
--FN---LN----Reasons
John | Doe | 6 |
Jane | Doe | 2 |
-----------------
That's the end of the first table.
Second Table
---Reason----Reason Detail-----
2 | Invalid SSN
4 | Invalid Date Of Birth
--------------------------------------
That's the end of the second table. (*Note, there is NO value of '6' in the second table)

I need a SQL query that returns the following:
------------------------------------------
John | Doe | 4 | Invalid SSN
John | Doe | 2 | Invalid Date of Birth
Jane | Doe | 2 | Invalid Date of Birth
------------------------------------------
John Doe has fallen out of our system for both an invalid SSN and an Invalid DOB, how can I split that information up in a SQL query?

1) These tables were designed to have this type of logic used against them, so there is only one unique combination to give the value '6' in the reasons column.
2) Yes, I do know this isn't the best way to do it... The only reason I can think of was due to how large the first table will get. And no, changing the way we do it now isn't a possibility.
3) The second table is fairly large, about 20-30 reasons, so creating every possible combination to populate a third table is daunting.

Everyone in my office is stumped... can anyone here help?

Subscribe to Alerts! Get questions and answers delivered to your Inbox.


E-mail me updates on this question



   SUBSCRIBE

hidden modal window
Help

Answer Wiki (Improve, edit or add to this answer)

IMPROVE THIS ANSWER
View History
 RATE THIS ANSWER
+1
Click to Vote:
  •   1
  •  0
Last Answered: Apr 17 2008  2:42 PM GMT  by Dwaltr


Latest Contributors: RudyLimeback


1) These tables were designed to have this type of logic used against them

is it too late to go back and shoot the person who did this? it won't solve your problem, but it might make you feel better

actually, the person who should be shot is the manager of the person who thought this scheme up


2) Yes, I do know this isn't the best way to do it...

good news, you are therefore open to suggestions, right?


3) The second table is fairly large, about 20-30 reasons, so creating every possible combination to populate a third table is daunting.

do a cross-join with itself, and voila, all 400-900 combinations are easy to obtain

are there any scenarios where the reason code is comprised of more than two reasons? because then you're in a real pickle

---------------------------------------------------------------------------------------------------------------------------------------------
This appears to be a bitmask. This is used in code to decode multiple errors from a returning function(that can only return one value). Each possible value in the list of reasons should be a power of 2. (0,1,2,4,8...).

What database is this in? That's because the solution will be database dependent. In Oracle the function is BITAND. Your query would look something like this:

select fn,ln,reason_detail
from first_table, second_table
where bitand(first_table.reasons,second_table.reason) = second_table.reason;

Try that and see if it works.

-Dave

P.S. Just looked it up in SQL Server the bitwise and operator is a '&'. So the above query in SQL Server looks like this:

select fn,ln,reason_detail
from first_table
inner join second_table on (first_table.reasons & second_table.reason) = second_table.reason;
  • AddThis Social Bookmark Button

Browse more Questions and Answers on Development, Database and SQL Server.

Looking for relevant Development Whitepapers? Visit the SearchSQLServer.com Research Library.


RSS - Discussions Help

Discuss This Answer


You must be logged-in to discuss a question. Log-in/Register

David Phillips  |   Apr 17 2008  2:57PM GMT

1) What database is this in. That’s because the solution will be database dependent. In Oracle the function is BITAND. Your query would look something like this:

SQL server, I apologize for leaving that detail out. I haven’t seen the Bitand operator in Oracle though, I’m doing some research to see if there is a SQL server counterpart and if it will fit what I need. Thank you for that lead, I’ll let you know if it bears any fruit.

2) are there any scenarios where the reason code is comprised of more than two reasons? because then you’re in a real pickle.

Yes, there is a third row in the Second table.
—Reason—-Reason Detail—–
2 | Invalid SSN
4 | Invalid Date Of Birth
8 | Invalid Address
————————————–
This can lead to possible reasons of: 2,4,6,8,10,12, and 14 in the first table. These of course represent all the possible combinations of those three reason_id’s

In defense to this approach, it does represent a very large amount of data already. Expanding it out even farther would probably make the table difficult to work with due to size. (Though you are correct - beating someone over the head would make feel better)

Thank you both for your comments!

 

Dwaltr  |   Apr 17 2008  4:31PM GMT

I had found the bitwise and operator for SQL Server and posted an example in the answer. That query should work on regardless of the number of reasons.

 

David Phillips  |   Apr 17 2008  5:01PM GMT

I’m receiving this error using your query, I’m trying to see if there is a cast or convert statement that needs to be ran.

“The data types bigint and float are incompatible in the boolean AND operator.”

 

Dwaltr  |   Apr 17 2008  5:32PM GMT

You will need to cast them as ints. You may want to alter the columns themselves to be ints to reduce the complexity of the query and to speed it up as well.

 

David Phillips  |   Apr 17 2008  6:35PM GMT

You’re a good man Dwaltr!
Worked perfectly. Thanks for the help!

 

Dwaltr  |   Apr 17 2008  6:43PM GMT

Glad to hear it…