CODEWITHSUNDEEP
mysqlsql
lakmal
lakmal

Reputation: 25

How to use Like in left outer join

SELECT *
FROM pay_invoice_list p
LEFT outer JOIN paimentDetl d
on p.invoice_no= d.invoiceNo and p.invoice_no LIKE ' %$temp%'
ORDER BY iid DESC

This is my query. But it doesn't work correctly. The tow tables are not join properly. as well as the "Like" didn't work.

Upvotes: 0

Views: 76

Answers (5)

Sathish
Sathish

Reputation: 4487

try like this

SELECT * 
FROM pay_invoice_list p 
    LEFT outer JOIN paimentDetl d ON p.invoice_no = d.invoiceNo 
WHERE p.invoice_no LIKE ' %$temp%' 
ORDER BY iid DESC

Upvotes: 0

SQLSam
SQLSam

Reputation: 537

Put p.invoice_no in the WHERE clause.

SELECT *
FROM pay_invoice_list p
LEFT outer JOIN paimentDetl d on p.invoice_no= d.invoiceNo 
WHERE p.invoice_no LIKE ' %$temp%'
ORDER BY iid DESC

This will return all records from pay_invoice_list where invoice_no LIKE ' %$temp%' and all records from paimentDetl where the invoice number matches.

Upvotes: 0

Loetn
Loetn

Reputation: 4040

You should only filter in your join statement when you want to filter the right table of your left outer join (here paimentDetl). When you want to filter your left table (here pay_invoice_list), you should add your filter clause in the where clause.

SELECT *
FROM pay_invoice_list p
LEFT outer JOIN paimentDetl d ON p.invoice_no= d.invoiceNo
WHERE p.invoice_no LIKE '%$temp%'
ORDER BY iid DESC

Upvotes: 0

jeroen_de_schutter
jeroen_de_schutter

Reputation: 1883

Try this

SELECT *
FROM pay_invoice_list p
LEFT outer JOIN paimentDetl d
on p.invoice_no= d.invoiceNo
where p.invoice_no LIKE ' %$temp%'
ORDER BY iid DESC

Upvotes: 0

SEB BINFIELD
SEB BINFIELD

Reputation: 410

SELECT *
FROM pay_invoice_list p
LEFT outer JOIN paimentDetl d
on p.invoice_no= d.invoiceNo 
Where p.invoice_no LIKE ' %$temp%'
ORDER BY iid DESC

All you needed to do is add WHERE to your query.

Upvotes: 2

Related Questions