SportsDB Home

Documentation

Specification

Samples

Query Library

SportsCaster Web Service

Hosted on SourceForge
and licensed under
Creative Commons GNU GPL.

SourceForge.net Logo

CC-GNU GPL

Query Library

Chris Hartjes submitted this query on: March 31, 2008 3:59pm EDT
TITLE Find player keys for the same players from different publishers
SPORTS ALL SPORTS
QUERY SELECT 
     p_rs.person_key, 
     p_rs.publisher_id, 
     dn_rs.full_name,
     p_tsn.person_key, 
     p_tsn.publisher_id,
     dn_tsn.full_name
     from 
     persons as p_rs,
     persons as p_tsn,
     display_names as dn_rs,
     display_names as dn_tsn
     where 
     dn_rs.full_name = dn_tsn.full_name

     and dn_rs.entity_type = 'persons'
     and dn_rs.entity_id = p_rs.id
     and p_rs.publisher_id = '2'

     and dn_tsn.entity_type = 'persons'
     and dn_tsn.entity_id = p_tsn.id
     and p_tsn.publisher_id = '1'

     order by dn_rs.full_name
DESCRIPTION Uses aliases to the same table to find all matching records
DBs TESTED MySQL
ADDITIONAL NOTES