Using LIKE in mySQL WHERE Clause

Question

I am trying to SELECT data WHERE the Name contains Christina using the following query, but it is returning all data, not only Christina.

SELECT a.id_transaksi, a.total_harga, (
        SELECT c.id_transfer
        FROM tbl_transfer c
        WHERE c.id_transaksi = a.id_transaksi
        ) AS id_transfer, (
        SELECT d.nama
        FROM tbl_costumer d
        WHERE d.username = a.username
        ) AS nama_costumer
FROM tbl_faktur a, tbl_transfer c, tbl_costumer d
WHERE a.konfirm_user = 1
    AND d.nama LIKE '%christina%'
GROUP BY a.id_transaksi
ORDER BY a.id_transaksi DESC

You need join conditions between the tables. Simple rule: Never use a comma in the from clause; always use explicit JOIN syntax. — Gordon Linoff
– Gordon Linoff, Commented Oct 19, 2015 at 1:40

Vamsi Prabhala · Accepted Answer · 2015-10-19 01:41:13Z

2

You don't need subqueries. join the tables with the appropriate conditions and it should work.

SELECT
a.id_transaksi, a.total_harga,  
c.id_transfer id_transfer,
d.nama as nama_costumer
FROM tbl_faktur a 
JOIN tbl_transfer c on c.id_transaksi = a.id_transaksi
JOIN tbl_costumer d on d.username = a.username
WHERE a.konfirm_user = 1 
AND d.nama LIKE '%christina%'
ORDER BY a.id_transaksi DESC

answered Oct 19, 2015 at 1:41

Vamsi Prabhala

49.4k4 gold badges41 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Alex · Accepted Answer · 2015-10-19 01:41:33Z

0

Your only filter on the 'top-level' query is c.id_transaksi = a.id_transaksi. You'll need to join against tbl_costumer, then filter on that to see the result you're expecting. I don't know the relationships between your customer/transfer table, but there's likely a customer_id on tbl_transfer, which should get you to the costumer table.

answered Oct 19, 2015 at 1:41

Alex

35.5k5 gold badges81 silver badges94 bronze badges

1 Comment

Bayu Anggara Over a year ago

yes, i tried it befor, but it was returning all data, but in the column nama_costumer only display Christina, and others where NULL. how to not display the nama_costumer which has NULL value ?

Bayu Anggara · Accepted Answer · 2015-10-19 22:42:29Z

I have found the answer, just adding EXSIST to WHERE clause...

SELECT a.id_transaksi, a.total_harga, a.total_berat, a.kurir, a.servis, a.resi, a.konfirm_user, a.konfirm_admin, a.date_user, a.ongkir, (   
                SELECT  c.id_transfer
                FROM tbl_transfer c
                WHERE c.id_transaksi = a.id_transaksi
                ) AS id_transfer, (

                SELECT  d.nama
                FROM tbl_costumer d
                WHERE d.username = a.username
                ) AS nama_costumer, (

                SELECT  d.provinsi_asal
                FROM tbl_costumer d
                WHERE d.username = a.username
                ) AS provinsi_asal, (

                SELECT  d.kota_asal
                FROM tbl_costumer d
                WHERE d.username = a.username
                ) AS kota_asal, (

                SELECT  d.alamat
                FROM tbl_costumer d
                WHERE d.username = a.username
                ) AS alamat, (

                SELECT  d.tlp
                FROM tbl_costumer d
                WHERE d.username = a.username
                ) AS tlp

            FROM tbl_faktur a,  tbl_transfer c, tbl_costumer d
            WHERE a.konfirm_user=1 AND exists (SELECT d.nama from tbl_costumer d  where a.username=d.username and d.nama like '%$keyword%') 
            GROUP BY a.id_transaksi
            ORDER BY a.id_transaksi DESC

Collectives™ on Stack Overflow

Using LIKE in mySQL WHERE Clause

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related