CODEWITHSUNDEEP
sqloracle-database
ashcanschool
ashcanschool

Reputation: 327

Linking a table to two columns in a second table

I have an issue where I think my major problem is figuring out how to phrase it to get an acceptable answer from Google.

The situation:

Table A is 'Invoice's it has a column that links to Table B 'Jobs' in two places. It either links to our 'Job Number' column or the 'Client Number' column. The major issue is the fact that 'Client Number' and 'Job Number' can be the same number if we set the job up instead of the client setting the job up.

What I'm getting is that every time there is the same number in either column the results are duplicated.

Now this is extremely simplifying the situation to try and make it a bit more understandable, but I am essentially looking for a statement that looks at Table A gets the value then compares against Column B1 if that doesn't match then compares it against B2 if that doesn't match then excludes it from the results. The key would be that if it matches when it compares against B1 it doesn't go on to compare it against B2.

Any help with this would be greatly appreciated, even if it is just a point in the direction of the very obvious operator or function that does this. It's hitting the end of a very long day.

Thank you.

Edit:

A further description:

Invoice Table
---------------------------------
PK,  INVOICE_NUMBER,  LINK_TO_JOB

Job Table
---------------------------------
PK, JOB_NUMBER, CLIENT_JOB_NUMBER

Now the crux of the matter is that both PK are database generated sequential numbers, no overlap there. The invoice number and the job number are both application generated sequential numbers with no overlap the link to job is application generated and when an invoice is raised links to one of two fields in the jobs table based on rules. For simplicity lets say those rules are if there is a Client Job Number link to that if not link to the job number.

Now the Client job number is a field that is written into buy people, lots of mistakes can and do happen, but lots of crap gets put in this field as well. Stuff like 'Email' 'Fax' are very common answers. So when there is crap in there like 'Email' it links to a series of other fields holding the same 'Email' tag.

So that's problem one.

Problem two Where Statement:

SELECT INVOICE_NUMBER,
       LINK_TO_JOB
       JOB_NUMBER,
       CLINET_JOB_NUMBER

    FROM JOBS_TABLE a, 
         INVOICE_TABLE b

How do I set up the where statement to get the desire result, I've tried:

WHERE (LINK_TO_JOB = JOB_NUMBER OR LINK_TO_JOB = CLIENT_JOB_NUMBER)

This returns lots of multiples, such as when the job number and client job number are identical and when there are multiple identical written in answers 'email' etc. Now this might be unavoidable and I will end up using a Distinct with this where statement to do the best I can with what I have. However what I want to do is:

WHERE (LINK_TO_JOB = JOB_NUMBER (+) OR LINK_TO_JOB = CLIENT_JOB_NUMBER (+))

Which comes back with an error as you can use outer joins with an OR operator.

If nothing comes from this I might just have to go with the OR connection and then throw in the Select Distinct and then build redundancy into Invoicing process so that when the database misses links a manual process catches them.

Although I'm all ears for any ideas.

Upvotes: 1

Views: 114

Answers (3)

APC
APC

Reputation: 146309

One way of doing this would be to use a set operation. UNION will give you a distinct set of values. You haven't given much detail so I'm guessing at the specifics: you'll need to amend them for your needs.

with j as ( select * from jobs )
select j.*, inv.*
from invoices inv
     join j on ( inv.job_no = j.job_no)
union
select j.*, inv.*
from invoices inv
     join j on ( inv.job_no = j.client_no)

The underlying reason for your difficulties is that the data model is half-cooked. In a proper design INVOICES.JOB_NO would have a foreign key relationship referencing JOBS.JOB_NO. Whereas JOBS.CLIENT_NO would be an additional piece of information, a business key, but would not be referenced by INVOICES. Of course it can be displayed on an actual invoice, that's why Nature gave us joins.

Upvotes: 1

ashcanschool
ashcanschool

Reputation: 327

OK, well group effort here. I used the union join like suggested by APC. and modified to fit my data and all of it's eccentricities (read the French couldn't data model there way out of a paper bag) And then I surrounded everything in a distinct statement suggested by user1871207 and Hikaru-Shindo.

But negative marks go to me, the reason my question was so unclear was several fold, but the big piece of information that was difficult for me to grasp / explain was that Invoices are not always for jobs, coupled with the fact that Invoices can be consolidated (which just went and screwed everything up) and This is just a big mess that I've with your help managed to put a very small piece of two year old scotch tape on.

My only hope for a continued career here is to use the exceptions that come up (and they will come at me like a spider monkey!) to hopefully amend the entire invoice process so that we can report some basic profit and loss numbers.

Cheers for all your help.

Upvotes: 0

NigelK
NigelK

Reputation: 8490

Use SELECT DISTINCT to remove the duplicates from your results set.

Upvotes: 1

Related Questions