CODEWITHSUNDEEP
sql
Ludwig Arcache
Ludwig Arcache

Reputation: 37

Remove duplicate records from query of 2 tables

I have the following 2 table:

Table 1 : invoices
invoiceID clientName
| 1            | Tony
| 2            | Ludwig
| 3            | Ralph

Table 2 : invoice_items
invoiceID itemDescription
| 1            | Flat monitor LED etc...
| 1            | HP Printer Inkjet etc...
| 2            | Laptop wahtever etc...

I'm trying to query only the invoices from Table "invoices", but allow the user the search the item descriptions.

I have written 

$query = "SELECT i.*, ii.invoiceID, ii.itemDescription from invoices i, invoice_items ii, WHERE ii.itemDescription like "%'.$_GET["s"].'%" AND i.invoiceID = ii.invoiceID";

but this will results in duplicates. any ideas ?

Upvotes: 0

Views: 23

Answers (1)

ScaisEdge
ScaisEdge

Reputation: 133380

if you want you could call distinct .. but in this case you must select the column you need explicitally

$query = "SELECT distinct i.col1, i.col2, i.col3, ii.invoiceID, ii.itemDescription 
      from invoices i, invoice_items ii, 
      WHERE ii.itemDescription like "%'.$_GET["s"].'%" 
      AND i.invoiceID = ii.invoiceID";

yyou can obtaion distinct this way (if you don't need descriptio in your result)

$query = "SELECT distinct i.col1, i.col2, i.col3
      from invoices i, invoice_items ii, 
      WHERE ii.itemDescription like "%'.$_GET["s"].'%" 
      AND i.invoiceID = ii.invoiceID";

Upvotes: 1

Related Questions