CODEWITHSUNDEEP
mysqldatabaseselect
user1480742
user1480742

Reputation: 163

SELECT query from one table linked with foreign key to multiple tables

My database consists of these tables 

    products(
    id INT(10) AUTO_INCREMENT,
    name VARCHAR(16) NOT NULL, 
    PRIMARY KEY (id)
);

/*      ^
    One |
       To
        | Many
        v
*/
versions(
        id INT(10) AUTO_INCREMENT,
        product_id INT(10)
        name VARCHAR(16) NOT NULL, 
        PRIMARY KEY (id),
        FOREIGN KEY (product_id) REFERENCES products(id)
    );


 /*      ^
        One |
           To
            | Many
            v
    */
    subversions(
                id INT(10) AUTO_INCREMENT,
                version_id INT(10)
                name VARCHAR(16) NOT NULL, 
                PRIMARY KEY (id),
                FOREIGN KEY (version_id) REFERENCES versions(id)
            );
/*      ^
    Many|
       To
        | Many
        v
*/
users(
                id INT(10) AUTO_INCREMENT,
                name VARCHAR(16) NOT NULL, 
                password VARCHAR(32) NOT NULL,
                PRIMARY KEY (id),
            );

And then i have

subversions_users_conjuction(
                    id INT(10) AUTO_INCREMENT,
                    subversion_id VARCHAR(16) NOT NULL, 
                    user_id VARCHAR(32) NOT NULL,
                    PRIMARY KEY (id),
                    FOREIGN KEY (subversion_id) REFERENCES subversions(id),
                    FOREIGN KEY (user_id) REFERENCES users(id)

);

What i'm struggling with is a query to get products.name (and id..or whichever filed) based on the current user. So, if i have a user with id = 1, i need to search table subversions_users to get all the subversions matching, then from that all the versions matching and then all the products matching and all that without duplicates, so DISTINCT. And i really have no idea where to start

Upvotes: 1

Views: 64

Answers (1)

Luke
Luke

Reputation: 1724

Something such as the following, perhaps?

SELECT DISTINCT p.name
FROM products AS p
    INNER JOIN versions AS v ON p.id = v.product_id
    INNER JOIN subversions AS s ON v.id = s.version_id
    INNER JOIN subversions_users_conjuction AS c ON s.id = c.subversion_id
WHERE c.user_id = <<<ID>>>;

Upvotes: 2

Related Questions