CODEWITHSUNDEEP
sql
Andrew S
Andrew S

Reputation: 1

SQL: combine two tables for a query

I want to query two tables at a time to find the key for an artist given their name. The issue is that my data is coming from disparate sources and there is no definitive standard for the presentation of their names (e.g. Forename Surname vs. Surname, Forename) and so to this end I have a table containing definitive names used throughout the rest of my system along with a separate table of aliases to match the varying styles up to each artist.

This is PostgreSQL but apart from the text type it's pretty standard. Substitute character varying if you prefer:

create table Artists (
     id serial primary key,
     name text,
     -- other stuff not  relevant
);

create table Aliases (
     artist integer references Artists(id) not null,
     name text not null
);

Now I'd like to be able to query both sets of names in a single query to obtain the appropriate id. Any way to do this? e.g.

select id from ??? where name = 'Bloggs, Joe';

I'm not interested in revising my schema's idea of what a "name" is to something more structured, e.g. separate forename and surname, since it's inappropriate for the application. Most of my sources don't structure the data, sometimes one or the other name isn't known, it may be a pseudonym, or sometimes the "artist" may be an entity such as a studio.

Upvotes: 0

Views: 54

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270843

I think you want:

select a.id
from artists a
where a.name = 'Bloggs, Joe' or
      exists (select 1
              from aliases aa
              where aa.artist = a.id and
                    aa.name = 'Bloggs, Joe'
             );

Actually, if you just want the id (and not other columns), then you can use:

select a.id
from artists a
where a.name = 'Bloggs, Joe'
union all   -- union if there could be duplicates
select aa.artist
from aliases aa
where aa.name = 'Bloggs, Joe';

Upvotes: 1

Related Questions