CODEWITHSUNDEEP
phpmysqlsql
Beacze
Beacze

Reputation: 534

SQL query with join shows only one result

I have sql query which should shows all records from table swt_modules, but it shows only first row.

$query1 = mysql_query ("SELECT swt_modules.id, swt_modules.name, swt_exam_regs.name AS exam_regs
                        FROM `swt_modules`
                        JOIN `swt_exam_regs`
                        USING ( `id` )
                        WHERE swt_exam_regs.id = swt_modules.exam_regulation
                        ORDER BY swt_modules.name DESC , swt_modules.id DESC
                        LIMIT " . $limit . "");
while ($fetch1 = mysql_fetch_array ($query1))
{
...
}

I have in this table (swt_modules) 3 rows and in each of them value of field "exam_regulation" is 1. In table swt_exam_regs I have only 1 row with 2 columns - id and name. Swt_modules.id stores id number. Which join I should use to be able to see all records?

result after join records in swt_modules table

Upvotes: 0

Views: 132

Answers (2)

Rahul
Rahul

Reputation: 77866

You need to use LEFT JOIN instead of INNER JOIN. Change your query as below. Notice that, I have removed LIMIT since you are trying to fetch all rows.

SELECT swt_modules.id, swt_modules.name, swt_exam_regs.name AS exam_regs
                    FROM `swt_modules`
                    LEFT JOIN `swt_exam_regs`
                    ON swt_exam_regs.id = swt_modules.exam_regulation
                    ORDER BY swt_modules.name DESC , swt_modules.id DESC

Upvotes: 1

Kodlee Yin
Kodlee Yin

Reputation: 1089

I would also suggest using mysqli or pdo instead of the now deprecated mysql.

$query1 = mysql_query ("
    SELECT 
        swt_modules.id, 
        swt_modules.name, 
        swt_exam_regs.name AS exam_regs
    FROM swt_modules
    LEFT JOIN swt_exam_regs on swt_exam_regs.id = swt_modules.exam_regulation
    ORDER BY 
        swt_modules.name DESC, 
        swt_modules.id DESC
    LIMIT $limit");

Upvotes: 1

Related Questions