SQL query compare with 3 tables

Question

i have a question about sql query.

here i have 3 table which are:

Table : elaun

1. elaun_kod (pk)
2. Jenis_elaun
3. peratus_elaun

table 2 : staff_elaun

1. staff_elaunID (pk)
2. staff_ID  (fk)
3. elaun_kod  (fk)

table 3 : staff

1. staff_ID (pk)

So here, i want to select the 'jenis_elaun'and 'peratus_elaun' from elaun table where their 'jenis_kod' equal to 'jenis_kod' in staff_elaun table. And then from staff_elaun table, i want to compare staff_ID with staff_ID in staff table.

so here is my query but its didnt display anything

$sql1 = mysql_query("
    SELECT elaun.*
    FROM elaun, staff_elaun, staff
    WHERE
        elaun.elaun_kod = staff_elaun.elaun_kod
        AND staff_elaun.staff_ID = staff.staff_ID
        AND staff.staff_ID = '$data[staff_ID]'
    ");

a), you don't need staff table because staff_ID is in staff_elaun. b) Use a `JOIN` syntax for readability - ` FROM elaun JOIN staff_elaun ON elaun.elaun_kod= staff_elaun.elaun_kod JOIN staff ON staff_elaun.staff_ID=staff.staff_ID WHERE`... c) Did you check for PHP / MySQL errors? d) [How do i prevent SQL injection in PHP](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) — danblack, Mar 14 '19 at 02:02
i have checked ,but there is no error .just cannot display. already try your query but its not working — Alia Azmee, Mar 14 '19 at 02:32

bignose · Answer 1 · 2019-03-14T02:43:51.327

You are describing a series of joins between tables. Each join has a condition for which rows correspond in each table.

In SQL, that is specified with a JOIN clause.

SELECT
    elaun.*
FROM elaun
    INNER JOIN staff_elaun USING (elaun_kod)
    INNER JOIN staff USING (staff_ID)

Then, apply the restriction using a WHERE clause.

SELECT
    elaun.*
FROM elaun
    INNER JOIN staff_elaun USING (elaun_kod)
    INNER JOIN staff USING (staff_ID)
WHERE
    staff.staff_ID = '… the ID value you want here …'

Of course, you should not be directly injecting values into the query; instead, use query parameters. See How can I prevent SQL injection in PHP?

SQL query compare with 3 tables

1 Answers1