CODEWITHSUNDEEP
mysqlsqljoin
ak85
ak85

Reputation: 4264

mysql Join two additional columns into query

I have the below sql.

    CREATE TABLE states (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255)
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    CREATE TABLE teams (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255),
        stateid INT
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    CREATE TABLE debates (
        id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
        debatedate DATE NOT NULL,
        hostid INT,
        visitid INT,
        winnerid INT
    ) DEFAULT CHARACTER SET utf8 ENGINE=InnoDB;

    INSERT INTO states (id, name) VALUES
    (1,'New York'),
    (2,'Pennsylvania'),
    (3,'Ohio');

    INSERT INTO teams (id, name, stateid) VALUES
    (1,'team one',1),
    (2,'team two',2),
    (3,'team three',2),
    (4,'team four',1),
    (5,'team five',2),
    (6,'team six',3);

    INSERT INTO debates (id, debatedate,hostid, visitid, winnerid ) VALUES
    (1,'2012-01-11', 1,2,1),
    (2,'2012-01-11', 3,4,4),
    (3,'2012-02-11', 5,6,5),
    (4,'2012-02-11', 1,4,1),
    (5,'2012-02-11', 2,5,5),
    (6,'2012-02-11', 3,6,3),
    (7,'2012-03-11', 6,1,1),
    (8,'2012-03-11', 5,2,5),
    (9,'2012-03-11', 3,4,4);

    SELECT deb.id, t1.name as Visitor , t2.name as Host
    FROM debates AS deb
    INNER JOIN teams t1 ON t1.id = deb.visitid
    INNER JOIN teams t2 ON t2.id = deb.hostid
    GROUP BY id
    LIMIT 1 --Just have limit 1 to save space in the question

Which returns

    -----------------------------
    |ID |VISITOR    |HOST       |
    |1  |team two   |team one   |
    -----------------------------

I also want to return what state the debate was held in. The answer to this will be the state of the hostid. In the teams table there is a column for stateid, but I am having trouble using it.

I have tried to add in the join 

INNER JOIN teams t3 ON t3.id = states.id

However I get the error 

Unknown column 'states.id' in 'on clause'

How would I go about getting this information.

I want to return

    -----------------------------------------
    |ID |VISITOR    |HOST       |State      |
    |1  |team two   |team one   |New York   |
    -----------------------------------------

see fiddle

Upvotes: 0

Views: 66

Answers (1)

Jim Garrison
Jim Garrison

Reputation: 86754

You want

SELECT deb.id, t1.name as Visitor , t2.name as Host, s.name
FROM debates AS deb
INNER JOIN teams t1 ON t1.id = deb.visitid
INNER JOIN teams t2 ON t2.id = deb.hostid
inner join states s on t2.stateid = s.id
GROUP BY id

Upvotes: 1

Related Questions