Report: Artists that may be persons shoudn't show obvious groups

XMLWordPrintable

      Current query pick up many obvious groups. I think we should use query that exclude all groups that have members from results. Here is modified query

      WITH bands AS (
      SELECT DISTINCT artist.id, artist.gid, artist.type, artist.name
      FROM
      artist
      JOIN l_artist_artist ON l_artist_artist.entity1=artist.id
      JOIN link ON link.id=l_artist_artist.link
      JOIN link_type ON link_type.id=link.link_type
      WHERE
      artist.type = 2 AND
      link_type.name = 'member of band'
      ), artist AS (
      SELECT DISTINCT artist.id, artist.gid, artist.type, artist.name
      FROM
      artist
      JOIN l_artist_artist ON l_artist_artist.entity0=artist.id
      JOIN link ON link.id=l_artist_artist.link
      JOIN link_type ON link_type.id=link.link_type
      WHERE
      (artist.type = 2 OR artist.type IS NULL) AND
      link_type.name NOT IN ('collaboration')
      EXCEPT
      SELECT * FROM bands
      )
      SELECT DISTINCT ON(name.name, artist.id) artist.gid, name.name, artist.type
      FROM
      artist
      JOIN artist_name AS name ON artist.name=name.id
      ORDER BY name.name, artist.id

            Assignee:
            Ian McEwen
            Reporter:
            Antti Jokipii
            Votes:
            0 Vote for this issue
            Watchers:
            0 Start watching this issue

              Created:
              Updated:
              Resolved:

                Version Package
                Schema change, 2012-10-15