Pulling data from one row into multiple rows?

ichikuma
ichikuma used Ask the Experts™
on
I have a Clarion database that I'm trying to pull data from to insert into a sql server database.  I'm trying to generate the best query for the job and ran into a snag because of the way the schema is created on the Clarion side.  I have two tables and here are their schemas:

Contacts
  Columns: Account, Contact, Address
Customer
  Columns: Account, Contact, Contact2

The problem is, when they created the Customer table, they made an assumption they would only ever need a max of 2 contacts (Contact, Contact2).  Well it turns out, they needed more so they created the Contacts table for 3..N contacts.  Seems simple right?  Create a join and associate the Customer data with each contact in the Contacts table.  The issue is, how can I break the Contact and Contact2 fields into their own row?

The way my application is setup is that I can only use one query at a time so I was hoping someone had some insight on how I could accomplish this.  Otherwise, I have to run a couple of processes, to make sure I get all of the contact information like so:

1.  Make call to join the Customer and Contacts table and load that data.
2.  Make a call to get Contact and load that data.
3.  Make a call to get Contact2 and load that data.

Was hoping there was a way to do a fancy join or something to get what I need.

Any insight would be greatly appreciated, thanks.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
ya, you can try query below

SELECT c.*
FROM contacts c
INNER JOIN Customers cust on c.Account = cust.Account AND
    (
         c.Contact = cust.Contact OR
         c.Contact = cust.Contact2
--  ... list go on
   )

Author

Commented:
Thanks for the quick response.  Tried the query and it looks like the Clarion odbc driver doesn't support 'INNER JOIN', isn't that special?

Looks like it will have to be three different steps if that's the case unless you may have another idea?

Thanks again.
Try this:

select cust.Account,
          cust.Contact,
          cont.Address
from Contacts cont INNER JOIN Customer cust on (cont.Account = cust.Account and cont.Contact = cust.Contact)
UNION
select cust.Account,
          cust.Contact2,
          cont.Address
from Contacts cont INNER JOIN Customer cust on (cont.Account = cust.Account and cont.Contact = cust.Contact)
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Since your Database doesn't support INNER JOIN, follow a new version:

Try this:

select cust.Account,
          cust.Contact,
          cont.Address
from Contacts cont,
        Customer cust
where cont.Account = cust.Account
    and cont.Contact = cust.Contact
UNION
select cust.Account,
          cust.Contact2,
          cont.Address
  from Contacts cont,
          Customer cust
where cont.Account = cust.Account
    and cont.Contact = cust.Contact

Author

Commented:
I so thought the UNION would work, however, Clarion doesn't support UNION either, I get an "extra token" error.  I guess that's the last option :(.  I'm contacting Clarion to see if it's something I'm doing or if they don't support UNION and JOIN.

Thanks again for your help.

Author

Commented:
This was exactly what I was looking for and it would have been complete but the Clarion odbc driver doesn't support UNION.  Otherwise, a great solution to my problem.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial