SQL table missing values

Dear all,
I have a table that typically has the following collumns:
partnumber
Company
Division
Responsible
Records usually look like:
partnumber company division responsible
A 1 14 AAA
A 1 13 BBB
A 1 CCC
B 1 14 AAA
B 1 12 BBB
B 1 DDD
Now I need to create a query, that returns the following:
A 1 14 AAA
A 1 13 BBB
A 1 12 CCC
A 1 11 CCC
B 1 14 AAA
B 1 13 DDD
B 1 12 BBB
B 1 11 DDD
So, in words, there are 4 possible combinations for Company/division, 1/11, 1/12, 1/13, 1/14.
If the combination is available in the table, it needs to show as is. If the record is not shown, e.g. p/n A, comp 1, division 11 the query needs to show a record that takes the responsible from the record that does show company 1, but has a blank division. in the end, a record should be uniquely identified by the combination of partnumber/company/division
Any idea how I would go about that?

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your response...

Discuss This Question: 7 &nbspReplies

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

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

What database are you using ?
Do you want to do this with pure SQL, or would you use a stored procedure if needed ?
For each part number, is it not possible to have more than one record with null division ?

Hi, Thanks for your reply.
I am using the production database for our ERP system. Let's call it "DUMMY".
I need it in pure SQL, since I need to join the result with another query. The result is for reporting purposes. The table itself should not undergo any changes.

perhaps i have not mentioned it clear enough. I can not make updates or changes to the database, since it is a database belonging to an external erp-system, and support will be void if changes to the database structure is made. Thus i simply need a select-type query that retrieves the info....

i think the solution with creating the "bogus"-table might work, if I change it into a view.
That'll allow me to join it to the rest of the query without making changes to the table structure.
Thanks you all!

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy

Processing your reply...

Ask a Question

Free Guide: Managing storage for virtual environments

Complete a brief survey to get a complimentary 70-page whitepaper featuring the best methods and solutions for your virtual environment, as well as hypervisor-specific management advice from TechTarget experts. Don’t miss out on this exclusive content!

To follow this tag...

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy