Difficult SQL query, is this possible?

35 pts.
Tags:
Database
Developers
Development
Query
Query tuning
SQL
SQL Query
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?
Asked: Last updated:
Related Questions
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

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;

Discuss This Question: 10  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • David Phillips
    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!
    35 pointsBadges:
    report
  • Dwaltr
    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.
    900 pointsBadges:
    report
  • David Phillips
    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."
    35 pointsBadges:
    report
  • Dwaltr
    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.
    900 pointsBadges:
    report
  • David Phillips
    You're a good man Dwaltr! Worked perfectly. Thanks for the help!
    35 pointsBadges:
    report
  • Dwaltr
    Glad to hear it...
    900 pointsBadges:
    report
  • Sakthisara
    select aaa.fname,aaa.lname, sec1.reason ,aaa.reasondetails from (SELECT firsttable.fname,firsttable.lname,secondtable.reasondetails,secondtable.reason from firsttable ,secondtable where firsttable.reason!=secondtable.reason) as aaa, secondtable as sec1 where aaa.reason!=sec1.reason;
    10 pointsBadges:
    report
  • Sgngopi2020
    it's very nice
    10 pointsBadges:
    report
  • Barun
    hi, i have a better solution for this problem
    20 pointsBadges:
    report
  • Barun
    hi, we can do it without any bit wise operator as-------- WITH CTE AS ( SELECT t1.FN,t1.LN,t2.Reason,t2.ReasonDetail FROM First1 t1 INNER JOIN Second1 t2 ON t1.Reasons>=t2.Reason ) SELECT c.FN,c.LN,c.Reason,s.ReasonDetail FROM CTE c INNER JOIN Second1 s ON c.Reason<>s.Reason regards, Barun kumar
    20 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: