ichikuma
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.
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.
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.
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)
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
Thanks again for your help.
ASKER
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.
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
)