asked on

SQL Code

Hi,

I'm trying to create an excel sheet of people's contact details, employment, and notes from our database.

The code is below and some of the results I get are attached.

I have about 28K people in the contact database yet I'm getting over 408K results in my search because people have multiple long note entry in the History tab.

How do I code it so I get about 28K results with the various long note entries for each person in their own column?

For example:

FAKE NAME 1 | LONGNOTES1a | LONGNOTES1b| LONGNOTES1c | LONGNOTES1d |
FAKE NAME 2 | LONGNOTES2a | LONGNOTES2b| LONGNOTES2c | LONGNOTES2d |

Thanks,
Primus

select sysdba.CONTACT.PREFIX,
sysdba.CONTACT.FIRSTNAME,
sysdba.CONTACT.LASTNAME,
sysdba.CONTACT.SUFFIX,
sysdba.CONTACT.TITLE,
sysdba.ACCOUNT.ACCOUNT,
sysdba.CONTACT.WORKPHONE,
sysdba.CONTACT.HOMEPHONE,
sysdba.CONTACT.MOBILE,
sysdba.CONTACT.EMAIL,
sysdba.CONTACT.SECONDARYEMAIL,
sysdba.ADDRESS.ADDRESS1,
sysdba.ADDRESS.ADDRESS2,
sysdba.ADDRESS.CITY,
sysdba.ADDRESS.STATE,
sysdba.ADDRESS.POSTALCODE,
sysdba.ADDRESS.COUNTRY,
sysdba.CONTACT.STATUS,
sysdba.HISTORY.LONGNOTES from sysdba.CONTACT
LEFT JOIN sysdba.ACCOUNT on sysdba.CONTACT.ACCOUNTID = sysdba.ACCOUNT.ACCOUNTID
LEFT JOIN sysdba.ADDRESS on sysdba.CONTACT.ADDRESSID = sysdba.ADDRESS.ADDRESSID and sysdba.ADDRESS.ISPRIMARY = 'T'
LEFT JOIN sysdba.HISTORY on sysdba.CONTACT.CONTACTID = sysdba.HISTORY.CONTACTID

Open in new window

Book1.xlsx

jogos

If you have a fixed column for a specific type of LONGNOTES , example whit types 'c' and 'd'

select ... 
c.LONGNOTES as longnotes1,
d.LONGNOTES as longnotes2
from sysdba.CONTACT
LEFT JOIN sysdba.ACCOUNT on sysdba.CONTACT.ACCOUNTID = sysdba.ACCOUNT.ACCOUNTID
LEFT JOIN sysdba.ADDRESS on sysdba.CONTACT.ADDRESSID = sysdba.ADDRESS.ADDRESSID and sysdba.ADDRESS.ISPRIMARY = 'T'
LEFT JOIN sysdba.HISTORY as c on sysdba.CONTACT.CONTACTID = c.CONTACTID
                                                      and c.type = 'c'
LEFT JOIN sysdba.HISTORY as c on sysdba.CONTACT.CONTACTID = d.CONTACTID
                                                      and d.type = 'd'

Open in new window

jogos

alias 'as c' in second join must be 'as d'

PrimusPilus

ASKER

Hi Jogos,

Thank you for your response! I'm going to try it.

Just a quick question from an observation. What if I don't know how many there longnotes entries there are for a person?

Thanks,
Primus

Lowfatspread

is the a sequence or some other identifier with which to order the notes?

what is the datatype of the longnotes column?

ASKER CERTIFIED SOLUTION

jogos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PrimusPilus

ASKER

Thank you!