SQL Query with Primary and Additional Contact Indicator 1

[sardine53]

Hello,

We are using GoldMine Premium 8.03. I have the following query (simplified) which I would like to add a column that indicates whether the contact is from the Primary or Additional table:

SELECT
c1.company
,c1.contact
,c1.phone1 AS Phone
,c1.accountno
FROM
contact1 c1
UNION
SELECT
c1.company
,cs.contact
,cs.phone AS Phone
,cs.accountno
FROM
contsupp cs
JOIN contact1 c1
ON cs.accountno=c1.accountno
WHERE
cs.rectype = 'C'
AND (cs.address1 is null
OR cs.address1 < '1')
ORDER BY
company

Thanks in advanced for your help!

 

[DJHunt]

Have you considered putting the word Primary into a field in the Contact1 table, and Secondary into a field in the ContSupp table. Now you can pull those fields in your query.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US

 

[goner]

I'd do something like:

SELECT
c1.company
,c1.contact
,'primary' as conttype
,c1.phone1 AS Phone
,c1.accountno
FROM
contact1 c1
UNION
SELECT
c1.company
,cs.contact
,'additional' as conttype
,cs.phone AS Phone
,cs.accountno
FROM
contsupp cs
JOIN contact1 c1
ON cs.accountno=c1.accountno
WHERE
cs.rectype = 'C'
AND (cs.address1 is null
OR cs.address1 < '1')
ORDER BY
company

Doug Castell
Castell Computers

http://www.castellcomputers.com

 

[DJHunt]

All the better.

DJ Hunt
Phone: (978)2-3333
WebSite:

http://www.DJHunt.US

- GoldMine Premium - The Definitive Guide
- One-on-One GoldMine Technical Support ( Fee Based )

http://www.DJHunt.US

 

[sardine53]

Perfect! Thanks so much for the quick replies.

- Jackie