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
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
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.