One non-primary contact per ID 1

[kernal]

I have a table where IDs can have contact names. An ID could have 1 primary contact and more than 1 non-primary contact (some IDs might not have any non-primary contacts and some IDs might not have a primary or non-primary contact). Is it possible to return the following from this example below?

ID Contact Primary
1 John Doe Y
1 Jane Doe N
1 Mary Doe N

2 John Smith Y

3

Results wanted:

ID Contact Primary
1 John Doe Y
1 Jane Doe N (only 1 non-primary contact is retrieved)

2 John Smith Y

3

Note: I'm using a query manager that won't allow me to have "from" in an expression so if this can be done, hopefully, it doesn't involve a "from" in an expression.

I appreciate the help.

Thanks

 

[Thargy]

kernal,

if I've understood correctly, then no you can achieve nothing. You must select data from something. If you can't use the keyword FROM, then you can't get any data.

Game over I'm afraid.

Is this really what you meant, or am I missing something here?

T

Grinding away at things Oracular

 

[SantaMufasa]

Kernal,

So that we can better assist you, could you please post a query that represents your best effort to this point (even thought the query might not yet be working successfully)? I need to understand more clearly what you mean by "I'm using a query manager that won't allow me to have "from" in an expression".

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kernal]

Thanks for helping me. Here is an example of the query.

SELECT a.id, a.name, b.contact, b.primary
FROM
person a,
contact b
WHERE
a.id=b.id (+)

It returns:

ID Name Contact Primary
1 Rob Doe John Doe Y
1 Rob Doe Jane Doe N
1 Rob Doe Mary Doe N

2 Jane Smith John Smith Y

3 Judy Jones no data in contact or primary field since she has no contacts in the contact table

====================
I'm hoping there is a way to modify the query so all of the data above is retrieved except that it only returns one of the non-primary contacts for an ID (doesn't matter which non-primary contact is retrieved per ID) so the results are:

ID Name Contact Primary
1 Rob Doe John Doe Y
1 Rob Doe Jane Doe N

2 Jane Smith John Smith Y

3 Judy Jones no data in contact or primary field since she has no contacts in the contact table

Thanks

 

[SantaMufasa]

Here is how I might achieve your results:

SELECT a.id, a.name, b.contact, b.primary
  FROM person a
      ,(select ID,contact,'Y' primary
          from contact
         where primary = 'Y'
        union
        select ID, max(contact), 'N'
          from contact
         where primary = 'N'
         group by ID) b
  WHERE a.id=b.id (+);

Let us know how this works for you.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[kernal]

Thanks Dave. I'm going to try it when I have time tomorrow.

 

[kernal]

Thanks Dave. It worked