I have the following sql querry: SELECT invoices.invoicenumber AS [Invoice Numbe
ID: 3769941 • Letter: I
Question
I have the following sql querry:
SELECT invoices.invoicenumber AS [Invoice Number], InvoiceLineItems.InvoiceLineItemDescription AS Description, vendors.VendorName AS Vendor,
InvoiceLineItems.InvoiceLineItemAmount AS amount, invoices.InvoiceDate AS [Invoice Date]
FROM invoices, InvoiceLineItems, vendors
WHERE invoices.invoicenumber = 'QP58872' OR invoices.invoicenumber = 'Q545443' OR invoices.invoicenumber = 'MABO1489' ;
Where the response i got was:
This is a very dangerous query because you don't have a join. You need to join the 3 tables, invoices,invoicelineitems, and vendors, then have the where clause to pull back the rows you want to see.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I'm not sure exactly what that is asking for. How would I "Join" those correctly. Or what does that mean... Can someone correct my file?
Explanation / Answer
In the specified query, data has been retrieving from three different tables. While writing a select query on more than one table, a join has to be performed between the tables based on key columns.
For example, Consider writing select query on three different tables: Emp(eid,ename,did), Dept(did,dname,lid), Loc(lid,lname):
A join condition has to be specified as:
Select e.ename as "Employee Name", d.dname as "Department Name", l.lname as "Location Name" from Emp e, Dept d, Loc l
where
e.did = d.did and d.lid = l.lid
In this query, join condition is "e.did = d.did and d.lid = l.lid". Column did is common in tables Emp and Dept and column lid is common in tables Dept and Loc.
Note:
1. If join condition is omitted in the query, the result will be a cartesian Product. Cartesian Product will take a single record from first table and attaches with all the records in the second table then takes second row from first table and attaches with all the rows in second table and this process continues till the end of the rows in first table which can be a dangerous situation if there are large number of rows in the table.
2. For a select query with 'n' number of tables there will be 'n-1' join conditions.
The tables mentioned in your query doesn't hold complete information regarding all columns present in the specified tables. You can modify the query based on the example specified above.