I was working on MySQL query that I finally got working, but although it returns the correct results, it seems fairly inefficient. I was wondering if there might be a better way to do this.

Here is the sample query:

Code:
SELECT DISTINCT `contact`.*
FROM `contact`
JOIN `contactSessions` ON `contactSessions`.`CUID`=`contact`.`CUID`
WHERE `contactSessions`.`SID`=51

UNION

SELECT `contact`.*
FROM `contact`
WHERE
`contact`.`CUID` IN
(
    SELECT DISTINCT `contactRelationship`.`superiorUID`
    FROM `contactRelationship`
    LEFT JOIN `contact` ON `contactRelationship`.`subordinateUID`=`contact`.`CUID`
    WHERE `contactRelationship`.`subordinateUID` IN 
        (
            SELECT DISTINCT `contact`.`CUID`
            FROM `contact`
            JOIN `contactSessions` ON `contactSessions`.`CUID`=`contact`.`CUID`
            WHERE `contactSessions`.`SID`=51
        )
)
The two `contactSessions`.`SID` WHERE clauses will always be identical. The SID number is the variable value of the whole query.

Basically, what I'm trying to do is select any contacts with a specific SID. Then, also select any parents/superiors when children/subordinates are found with a specific SID. There wouldn't necessarily be a record for the specific SID and the CUID in the `contactSessions` table for the parent/superior.

So, here's a longer explanation of the query:

What the first part of the query does is select all the `contact` records for any contacts that appear in the `contactSessions` table with a specific SID.

The second part (after the UNION) selects all the `contact` CUIDs for any CUIDs that appear in the `contactSessions` table with a specific SID. Then, when the CUID appears as a child (subordinateUID) in `contactRelationship` table, the parent's CUIDs (superiorUIDs) are returned. Then, the results from the `contact` table are returned when the superiorUID matches the CUID.


Below are the CREATE statements for the tables and sample data.

Code:
-- -----------------------------------------------------
-- Table `Contact`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `Contact` (
  `CUID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `firstName` VARCHAR(255) NULL,
  `lastName` VARCHAR(255) NULL,
  PRIMARY KEY (`CUID`),
  UNIQUE INDEX `UID_UNIQUE` (`CUID` ASC))
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `ContactRelationship`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ContactRelationship` (
  `superiorUID` BIGINT UNSIGNED NOT NULL,
  `subordinateUID` BIGINT UNSIGNED NOT NULL,
  PRIMARY KEY (`superiorUID`, `subordinateUID`),
  INDEX `fk_ContactRelationship_Contact_idx` (`superiorUID` ASC),
  INDEX `fk_ContactRelationship_Contact1_idx` (`subordinateUID` ASC),
  CONSTRAINT `fk_ContactRelationship_Contact`
    FOREIGN KEY (`superiorUID`)
    REFERENCES `Contact` (`CUID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_ContactRelationship_Contact1`
    FOREIGN KEY (`subordinateUID`)
    REFERENCES `Contact` (`CUID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;




-- -----------------------------------------------------
-- Table `ContactSessions`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `ContactSessions` (
  `CUID` BIGINT UNSIGNED NOT NULL,
  `SID` INT UNSIGNED NOT NULL,
  INDEX `fk_ContactSubscriptions_Contact1_idx` (`CUID` ASC),
  PRIMARY KEY (`CUID`, `SID`),
  INDEX `fk_ContactSessions_Sessions1_idx` (`SID` ASC),
  CONSTRAINT `fk_ContactSubscriptions_Contact1`
    FOREIGN KEY (`CUID`)
    REFERENCES `Contact` (`CUID`)
    ON DELETE CASCADE
    ON UPDATE NO ACTION)
ENGINE = InnoDB;


INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (1, 'John', 'Doe');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (2, 'Jane', 'Doe');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (3, 'Bob', 'Smith');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (4, 'Bill', 'Johnson');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (5, 'Little Kelly', 'Johnson');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (6, 'Little Jimmy', 'Doe');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (7, 'Little Jenny', 'Doe');
INSERT INTO `contact` (`CUID`, `firstName`, `lastName`) VALUES (8, 'Little Lizzy', 'Doe');


INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (1,6);
INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (1,7);
INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (1,8);
INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (2,6);
INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (2,7);
INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (2,8);
INSERT INTO `ContactRelationship` (`superiorUID`, `subordinateUID`) VALUES (4,5);


INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (8,50);
INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (8,51);
INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (8,52);
INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (8,53);
INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (6,51);
INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (6,52);
INSERT INTO `ContactSessions` (`CUID`, `SID`) VALUES (5,51);