CODEWITHSUNDEEP
phpmysqldatabaseforeign-keys
Heis
Heis

Reputation: 568

foreign key how to use join with 3 tables

Im trying to make an list off all users registered to a specific cursus i've been looking into the join query but i cant figure out how it works and everything ill try is not working so far. this is what i have so far: 

      <?php
        $cursus2 = mysqli_fetch_array(mysqli_query($con, "SELECT * FROM cursus JOIN Cursist"));
        $result = mysqli_query($con, $cursus2);
        $amount = mysqli_affected_rows($con);

        for ($i = 0; $i < $amount; $i++) {
          $Cursist = mysqli_fetch_array($result);

        echo $Cursist['username']. "<br />";
      }?>

I want an output off all the usernames that are connected with the cursus in the bridge table.

DB structure:

**Cursist Table**     
-------------
id    username
-------------
1     name1
2     name2
3     name3
4     name4

**cursus Table**
-------------
id    name
-------------
1     PHP
2     JAVA
3     HTML
4     RUBY

**Bridge**
-------------
Cursus_cursusCode    Cursist_id
-------------
1     1
1     2
1     3
1     4
2     1
2     2
3     3
3     4
4     1
4     2

the query that was closed is this one i think:

SELECT * FROM cursus 
JOIN cursist ON cursist.id=cursus.cursus_cursusCode

Upvotes: 1

Views: 42

Answers (2)

Rodney Salcedo
Rodney Salcedo

Reputation: 1254

Try:

SELECT c2.username,c1.name 
FROM bridge b, cursus c1, Cursist c2
WHERE b.Cursus_cursusCode=c1.id AND b.Cursist_id=c2.id

You can check it here

Upvotes: 1

sagi
sagi

Reputation: 40481

You need two joins, not one :

SELECT *
FROM Cursist t
INNER JOIN Bridge b
 ON(t.id = b.cursist_id)
INNER JOIN Cursos s
 ON(b.Cursus_cursusCode = s.id)

If you are only intrested on the username, you don't need to select anything from cursus table, so you can do this:

SELECT t.username 
FROM Cursist t
INNER JOIN Bridge b
 ON(t.id = b.cursist_id)

Can also be done with EXISTS() :

SELECT t.user_name
FROM Cursist t
WHERE EXISTS(SELECT 1 FROM Bridge B
             WHERE t.id = b.cursist_id)

Upvotes: 2

Related Questions