CODEWITHSUNDEEP
phpsqlphpmyadmin
Sunny0101
Sunny0101

Reputation: 476

SQL query not bringing intended results

I essentially need to pull out a leadership board if you like of memberID, their first names, last names and their result and order them into highest first.

So far I have the first names and surnames appearing and then results however my query seems to be incorrect as it's showing all possible names, with all possible results as seen below (I know I need a join, not sure how though and which one):

Incorrect Results

Below I have actually posted what the results look like within the table:

What phpMyAdmin shows for results

The info and tables needed below are:

So far I have this, but it's not quite right as you can see on my first screenshot.

<div class="grid-2"> 
    <p><b>LEADERSHIP BOARD</b></p>
    <?php
        $query = "SELECT `tblMember`.`fldFName`, `tblMember`.`fldSName`, `tblMembEntComp`.`fldResult` FROM `tblMember`, `tblMembEntComp`";

        $result = $conn -> query($query);

        while($row = $result -> fetch_assoc()) 
            {
                echo $row['fldFName']." ".$row['fldSName']." ".$row['fldResult']."<br>";
            } 
    ?>
</div>

Upvotes: 0

Views: 45

Answers (2)

Jordi Castillo
Jordi Castillo

Reputation: 693

Use for example LEFT JOIN

SELECT T1.*, T2.*
FROM tblMember T1
LEFT JOIN tblMembEntComp T2
ON T1.UniqueCommonField = T2.UniqueCommonField;

Upvotes: 1

Barmar
Barmar

Reputation: 781726

You need to specify the columns that relate the two tables:

SELECT m.fldFName, m.fldSName, e.fldResult
FROM tblMember AS m
JOIN tblMembEntComp as e ON m.fldMemberID = e.fldMemberID

Otherwise you get a full cross product between the two tables.

Upvotes: 1

Related Questions