CODEWITHSUNDEEP
phpmysql
Vasco
Vasco

Reputation: 93

how to get all rows except with specific values

Hello is there a way to only get the survey_name that the organisation has not used yet?

`survey table`
+-----------+-------------+
| survey_id | survey_name |
+-----------+-------------+
|         1 | name1       |
|         2 | name2       |
|         3 | name3       |
|         4 | name4       |
|         5 | name5       |
+-----------+-------------+

`link table`
+---------+-------------+-----------------+
| link_id | survey_link | organisation_id |
+---------+-------------+-----------------+
|       1 | 1(survey_id)|        1        |
|       2 | 2           |        1        |
|       3 | 2           |        2        |
|       3 | 3           |        2        |
|       3 | 6           |        2        |
+---------+-------------+-----------------+

In this database structure you would see this for each organisation:

available surveys organisation1:

available surveys organisation1:

I have tried using WHERE NOT (survey_id= $row['survey_id'])

//get all survey's that are in use
$sqlCheckLink = "SELECT `survey_id` FROM `link_info` WHERE `organisation_id`=".$_SESSION['organisation_id']."";
    $resultCheck = mysqli_query($conn, $sqlCheckLink);
    if ($resultCheck ->num_rows > 0) {
        while ($id = $resultCheck -> fetch_assoc()) {
            //show all surveys that are available
            $sqlGetSurveys = "SELECT * FROM `survey_info` WHERE NOT (survey_id = ".$id['survey_id'].")";
            $resultAllSurveys = mysqli_query($conn, $sqlGetSurveys);

            if ($resultAllSurveys ->num_rows > 0) {
                while ($row = $resultAllSurveys-> fetch_assoc()) {
                   //echo content
                }
            }
        }
    }

from here But it does not seem to work... With this method I get also the surveys that are in use.

If someone could help it would be really appreciated!

Upvotes: 0

Views: 70

Answers (1)

juanram0n
juanram0n

Reputation: 356

Just using LEFT JOIN:

SELECT t.survey_name
FROM survey_table as t
LEFT JOIN link_table AS l ON l.survey_link = t.survey_id
WHERE l.link_id IS NULL

Not tested, but you can get the idea. I hope this help you.

Upvotes: 1

Related Questions