Link to home
Start Free TrialLog in
Avatar of ichikuma
ichikumaFlag for United States of America

asked on

Pulling data from one row into multiple rows?

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.
Avatar of yuching
yuching

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
   )
Avatar of ichikuma

ASKER

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.
Avatar of dougaug
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)
ASKER CERTIFIED SOLUTION
Avatar of dougaug
dougaug
Flag of Brazil image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.