Ludwig Arcache Ludwig Arcache - 3 months ago 11
SQL Question

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 ?

Answer

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";