How do I stop duplicate rows on table join in SQL?

Trying to join this table, so that i can change the code if the tutor ID matches the session tutor ID. But it shows multiple results in the calendar that its generating.

Below is the current PHP code, although the entries are being duplicated due to having multiple tutor ID's within the table. i'm not sure how to change this.

<?php

    $sqlAssignments = "SELECT * FROM tbl_assignments LEFT JOIN tbl_tutorModules ON tbl_assignments.module_code = tbl_tutorModules.module_code"; // 

    $qryAssignments = mysqli_query($con, $sqlAssignments); // running the query



    while($rowAssignment = mysqli_fetch_assoc($qryAssignments)){

        if ($_SESSION["ID"] == $rowAssignment['tutor_id']) {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "', color: '#f1f1f1'},";

        } else {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "'},";



        }

    }

?>

The actual results at the moment is that when the tutorModules has multiple tutors, the output duplicates calendar results.

Thanks

Edit: Tables look like this with some example data

tbl_tutorModules

con_id   module_code   tutor_id

2        ISYS30025     1

3        ISYS30025     2

tbl_assignments

assignment_id

module_code

assignment_name

assignment_weight

set_date

hand_in_date

hand_in_method

assignment_type

This is the current output

The expected output is for these not to be duplicated.

edited Jan 2 at 21:19

asked Jan 2 at 14:19

Amna A

1

Table structure, sample data and expected results would be helpful...

– sgeddes
Jan 2 at 14:20

Provide some more details, like desired results, so that we can understand problem easily.

– Hamza Zafeer
Jan 2 at 14:23

Select the specific data you want (rather than everything - *) with a DISTINCT clause?

– CD001
Jan 2 at 14:26

thanks everyone, i have added the tables that im using.

– Amna A
Jan 2 at 14:28

As for the expected output i would like it so if the tutor id matches the session stored id the colour is different, otherwise it just displays normal.

– Amna A
Jan 2 at 14:29

|
show 6 more comments

Trying to join this table, so that i can change the code if the tutor ID matches the session tutor ID. But it shows multiple results in the calendar that its generating.

Below is the current PHP code, although the entries are being duplicated due to having multiple tutor ID's within the table. i'm not sure how to change this.

<?php

    $sqlAssignments = "SELECT * FROM tbl_assignments LEFT JOIN tbl_tutorModules ON tbl_assignments.module_code = tbl_tutorModules.module_code"; // 

    $qryAssignments = mysqli_query($con, $sqlAssignments); // running the query



    while($rowAssignment = mysqli_fetch_assoc($qryAssignments)){

        if ($_SESSION["ID"] == $rowAssignment['tutor_id']) {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "', color: '#f1f1f1'},";

        } else {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "'},";



        }

    }

?>

The actual results at the moment is that when the tutorModules has multiple tutors, the output duplicates calendar results.

Thanks

Edit: Tables look like this with some example data

tbl_tutorModules

con_id   module_code   tutor_id

2        ISYS30025     1

3        ISYS30025     2

tbl_assignments

assignment_id

module_code

assignment_name

assignment_weight

set_date

hand_in_date

hand_in_method

assignment_type

This is the current output

The expected output is for these not to be duplicated.

edited Jan 2 at 21:19

asked Jan 2 at 14:19

Amna A

1

Table structure, sample data and expected results would be helpful...

– sgeddes
Jan 2 at 14:20

Provide some more details, like desired results, so that we can understand problem easily.

– Hamza Zafeer
Jan 2 at 14:23

Select the specific data you want (rather than everything - *) with a DISTINCT clause?

– CD001
Jan 2 at 14:26

thanks everyone, i have added the tables that im using.

– Amna A
Jan 2 at 14:28

As for the expected output i would like it so if the tutor id matches the session stored id the colour is different, otherwise it just displays normal.

– Amna A
Jan 2 at 14:29

|
show 6 more comments

Trying to join this table, so that i can change the code if the tutor ID matches the session tutor ID. But it shows multiple results in the calendar that its generating.

Below is the current PHP code, although the entries are being duplicated due to having multiple tutor ID's within the table. i'm not sure how to change this.

<?php

    $sqlAssignments = "SELECT * FROM tbl_assignments LEFT JOIN tbl_tutorModules ON tbl_assignments.module_code = tbl_tutorModules.module_code"; // 

    $qryAssignments = mysqli_query($con, $sqlAssignments); // running the query



    while($rowAssignment = mysqli_fetch_assoc($qryAssignments)){

        if ($_SESSION["ID"] == $rowAssignment['tutor_id']) {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "', color: '#f1f1f1'},";

        } else {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "'},";



        }

    }

?>

The actual results at the moment is that when the tutorModules has multiple tutors, the output duplicates calendar results.

Thanks

Edit: Tables look like this with some example data

tbl_tutorModules

con_id   module_code   tutor_id

2        ISYS30025     1

3        ISYS30025     2

tbl_assignments

assignment_id

module_code

assignment_name

assignment_weight

set_date

hand_in_date

hand_in_method

assignment_type

This is the current output

The expected output is for these not to be duplicated.

edited Jan 2 at 21:19

asked Jan 2 at 14:19

Amna A

Trying to join this table, so that i can change the code if the tutor ID matches the session tutor ID. But it shows multiple results in the calendar that its generating.

Below is the current PHP code, although the entries are being duplicated due to having multiple tutor ID's within the table. i'm not sure how to change this.

<?php

    $sqlAssignments = "SELECT * FROM tbl_assignments LEFT JOIN tbl_tutorModules ON tbl_assignments.module_code = tbl_tutorModules.module_code"; // 

    $qryAssignments = mysqli_query($con, $sqlAssignments); // running the query



    while($rowAssignment = mysqli_fetch_assoc($qryAssignments)){

        if ($_SESSION["ID"] == $rowAssignment['tutor_id']) {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "', color: '#f1f1f1'},";

        } else {

            echo "{ title: '" . $rowAssignment['assignment_name'] . "', start: '" . $rowAssignment['hand_in_date'] . "', end: '" . $rowAssignment['hand_in_date'] . "', url: 'view/assignments.php?id=" . $rowAssignment['assignment_id'] . "'},";



        }

    }

?>

The actual results at the moment is that when the tutorModules has multiple tutors, the output duplicates calendar results.

Thanks

Edit: Tables look like this with some example data

tbl_tutorModules

con_id   module_code   tutor_id

2        ISYS30025     1

3        ISYS30025     2

tbl_assignments

assignment_id

module_code

assignment_name

assignment_weight

set_date

hand_in_date

hand_in_method

assignment_type

This is the current output

The expected output is for these not to be duplicated.

php sql mysqli

edited Jan 2 at 21:19

asked Jan 2 at 14:19

Amna A

edited Jan 2 at 21:19

asked Jan 2 at 14:19

Amna A

edited Jan 2 at 21:19

asked Jan 2 at 14:19

Amna A

asked Jan 2 at 14:19

Amna A

asked Jan 2 at 14:19

Amna A

1

Table structure, sample data and expected results would be helpful...

– sgeddes
Jan 2 at 14:20

Provide some more details, like desired results, so that we can understand problem easily.

– Hamza Zafeer
Jan 2 at 14:23

Select the specific data you want (rather than everything - *) with a DISTINCT clause?

– CD001
Jan 2 at 14:26

thanks everyone, i have added the tables that im using.

– Amna A
Jan 2 at 14:28

As for the expected output i would like it so if the tutor id matches the session stored id the colour is different, otherwise it just displays normal.

– Amna A
Jan 2 at 14:29

|
show 6 more comments

1

Table structure, sample data and expected results would be helpful...

– sgeddes
Jan 2 at 14:20

Provide some more details, like desired results, so that we can understand problem easily.

– Hamza Zafeer
Jan 2 at 14:23

Select the specific data you want (rather than everything - *) with a DISTINCT clause?

– CD001
Jan 2 at 14:26

thanks everyone, i have added the tables that im using.

– Amna A
Jan 2 at 14:28

As for the expected output i would like it so if the tutor id matches the session stored id the colour is different, otherwise it just displays normal.

– Amna A
Jan 2 at 14:29

Table structure, sample data and expected results would be helpful...

– sgeddes
Jan 2 at 14:20

Provide some more details, like desired results, so that we can understand problem easily.

– Hamza Zafeer
Jan 2 at 14:23

Select the specific data you want (rather than everything - *) with a DISTINCT clause?

– CD001
Jan 2 at 14:26

thanks everyone, i have added the tables that im using.

– Amna A
Jan 2 at 14:28

As for the expected output i would like it so if the tutor id matches the session stored id the colour is different, otherwise it just displays normal.

– Amna A
Jan 2 at 14:29

|
show 6 more comments

1 Answer
1

active

oldest

votes

You want to know whether a certain tutor is involved in an assignment. So pass the tutor ID to the DBMS in order to let it find out in a query.

SELECT

  assignment_id, assignment_name, hand_in_date,

  case when module_code in (SELECT module_code FROM tbl_tutorModules WHERE tutor_id = ?)

    then 'yes' else 'no'

  end as tutor_involved

FROM tbl_assignments

ORDER BY assignment_id;

As you can see, I don't join the tables, because I'm not interested in the joined result. I merely want to look up a record in tbl_tutorModules. We use IN or EXISTS in SQL to look up records in another table.

See here how to pass parameters to the DBMS in mysqli: http://php.net/manual/en/mysqli.prepare.php

edited Jan 2 at 14:57

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

Thank you so much, it worked.

– Amna A
Jan 2 at 15:00

@AmnaA The answer should be accepted then. I don't get the edit you made to the question. Are you trying to ask a new question?

– Funk Forty Niner
Jan 2 at 21:21

add a comment |

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54007964%2fhow-do-i-stop-duplicate-rows-on-table-join-in-sql%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

1 Answer
1

active

oldest

votes

1 Answer
1

active

oldest

votes

You want to know whether a certain tutor is involved in an assignment. So pass the tutor ID to the DBMS in order to let it find out in a query.

SELECT

  assignment_id, assignment_name, hand_in_date,

  case when module_code in (SELECT module_code FROM tbl_tutorModules WHERE tutor_id = ?)

    then 'yes' else 'no'

  end as tutor_involved

FROM tbl_assignments

ORDER BY assignment_id;

See here how to pass parameters to the DBMS in mysqli: http://php.net/manual/en/mysqli.prepare.php

edited Jan 2 at 14:57

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

Thank you so much, it worked.

– Amna A
Jan 2 at 15:00

@AmnaA The answer should be accepted then. I don't get the edit you made to the question. Are you trying to ask a new question?

– Funk Forty Niner
Jan 2 at 21:21

add a comment |

You want to know whether a certain tutor is involved in an assignment. So pass the tutor ID to the DBMS in order to let it find out in a query.

SELECT

  assignment_id, assignment_name, hand_in_date,

  case when module_code in (SELECT module_code FROM tbl_tutorModules WHERE tutor_id = ?)

    then 'yes' else 'no'

  end as tutor_involved

FROM tbl_assignments

ORDER BY assignment_id;

See here how to pass parameters to the DBMS in mysqli: http://php.net/manual/en/mysqli.prepare.php

edited Jan 2 at 14:57

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

Thank you so much, it worked.

– Amna A
Jan 2 at 15:00

@AmnaA The answer should be accepted then. I don't get the edit you made to the question. Are you trying to ask a new question?

– Funk Forty Niner
Jan 2 at 21:21

add a comment |

You want to know whether a certain tutor is involved in an assignment. So pass the tutor ID to the DBMS in order to let it find out in a query.

SELECT

  assignment_id, assignment_name, hand_in_date,

  case when module_code in (SELECT module_code FROM tbl_tutorModules WHERE tutor_id = ?)

    then 'yes' else 'no'

  end as tutor_involved

FROM tbl_assignments

ORDER BY assignment_id;

See here how to pass parameters to the DBMS in mysqli: http://php.net/manual/en/mysqli.prepare.php

edited Jan 2 at 14:57

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

You want to know whether a certain tutor is involved in an assignment. So pass the tutor ID to the DBMS in order to let it find out in a query.

SELECT

  assignment_id, assignment_name, hand_in_date,

  case when module_code in (SELECT module_code FROM tbl_tutorModules WHERE tutor_id = ?)

    then 'yes' else 'no'

  end as tutor_involved

FROM tbl_assignments

ORDER BY assignment_id;

See here how to pass parameters to the DBMS in mysqli: http://php.net/manual/en/mysqli.prepare.php

edited Jan 2 at 14:57

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

edited Jan 2 at 14:57

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

answered Jan 2 at 14:51

Thorsten Kettner

52.9k32643

Thank you so much, it worked.

– Amna A
Jan 2 at 15:00

@AmnaA The answer should be accepted then. I don't get the edit you made to the question. Are you trying to ask a new question?

– Funk Forty Niner
Jan 2 at 21:21

add a comment |

Thank you so much, it worked.

– Amna A
Jan 2 at 15:00

@AmnaA The answer should be accepted then. I don't get the edit you made to the question. Are you trying to ask a new question?

– Funk Forty Niner
Jan 2 at 21:21

Thank you so much, it worked.

– Amna A
Jan 2 at 15:00

@AmnaA The answer should be accepted then. I don't get the edit you made to the question. Are you trying to ask a new question?

– Funk Forty Niner
Jan 2 at 21:21

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

Search This Blog

Ufyukyu