Combining the contents of 2 queries on the same file

I am having trouble setting up this query.

 

I want the results of the first query to merge/join/union with the second query where the ownerid from the first query equals the ownerid from the second query. The bold line should not be part of the query. I put it in to get the records from the second that equal the ownerid from the first query.

Select n1.ownerid, n1.surname, n1.given, n1.nametype, n1.prefix, n1.isprimary

from nametable n1

where n1.nametype = 1

and n1.isprimary = 0

order by surname, given

I get the results in Pic 1

 

select n2.ownerid, n2.surname, n2.given, n2.nametype, n2.prefix, n2.isprimary

from nametable n2

where n2.nametype = 0

and n2.isprimary = 1

and ownerid in(10518, 10519, 11626, 11976, 3536, 11805, 10773, 11655, 16605, 11802, 255, 129, 130, 15721, 9046, 11495, 11531)

order by surname, given

I get the results in Pic 2

I want the results of query 1 to be combined with the results of query 2 where the ownerids from the first query are only the ownerids of the second query.

So I get something like Pic 3

2 Replies to “Combining the contents of 2 queries on the same file”

  1. Only 2 of 3 pictures visible and they are very small so I’m not sure I’ve got this right but my interpretation is that what you want to see is the Primary Name side-by-side with the Alternate Name for a select set of people. Maybe this?

    Select n1.ownerid, n1.surname, n1.given, n1.nametype, n1.prefix, n1.isprimary,
    n2.ownerid, n2.surname, n2.given, n2.nametype, n2.prefix, n2.isprimary
    from nametable n1
    JOIN nameTable n2 USING (OwnerID)
    where n1.nametype = 1
    and n1.isprimary = 0
    and n2.nametype = 0
    and n2.isprimary = 1
    and OwnerID IN (your list)
    ;
    — add whatever ORDER BY you want but you will have to specify whether the fields are from n1 or n2

  2. Thank you Tom. That did it. I tried one almost like that. I didn’t think the and n2.nametype = 0
    and n2.isprimary = 1 part would yield anything but wrong it did exactly what I needed.

Leave a Reply