Need help with assignemnt in Data & Information Management
The question I am struggling with is; Use INNER JOIN to create a query result. In this query result, list each vendor’s name and the name of each product the vendor has supplied to our company. You should use the Vendors, Product_Vendors, and the Products tables in the database to complete this task. And what I have is: SELECT products,productName, VendName, VendorID FROM ((products Inner Join VendName ON VendorID = vendors.VendorID) Inner Join Product_Vendors ON VendName.VendID = ProductName, ProductNumber); It keeps coming up with an error and do not know where I messed up; I am new to SQL and any assistance will be greatly appreciated, thanks.
From the first glance it looks like there are some errors in the query: 1. What is VendName? It looks like it is the column name of the table used Vendors, but the column is used as though it is a table (Inner Join **VendName** part) 2. Using parentheses with joins is ill advised. Usually, they appear when the attempts are made to use the graphical tool to construct the query. The tool will not teach you anything and will also add the whole bunch of parentheses which will force the joins to execute in specific order. There is no reason whatsoever to instruct the engine about the order of join execution. The engine is very smart, don't fight its abilities to figure out the best order of joins. 3. None of the columns in the select list specifies to which table they belong. If 2 tables have the same name then it creates ambiguity and causes the error to be raised. 4. What is VendID? Is cannot be a column in the Vendors table because it looks like the Vendors table has the column named VendorID, not VendID. 5. Products and Vendors cannot be joined directly. Each vendor may supply multiple products so the vendor cannot have a direct reference to products. Similarly, based on the table names in question, it appears that the products table does not have a direct reference to the vendors table. 6. The join conditions should use **AND** in most cases, sometimes **OR**, not the commas (like in **VendName.VendID = ProductName, ProductNumber** part, which is incorrect). I don't know the column names of the tables, but let's assume that there is a table named **Products** which uniquely identifies each record by its **ProductNumber** column. The table named **Vendors** uniquely identifies each record by **VendorID** column. The "table-in-the-middle", or lookup table named **Product\_Vendors** relates the products and vendors, so this table has both **ProductNumber** and **VendorID** columns, and while it is possible for each vendor to supply multiple products and potentially it might be possible for the same product to be supplied by the multiple vendors, the combination of **ProductNumber** and **VendorID** columns is unique in the lookup table. In order to marry products and vendors, the lookup table is needed so that products is joined with lookup based on the product number while the lookup is joined to vendors based on vendor ID. Though this is a homework, the effort to complete it has been shown, so I suppose it is OK to post the sample script which may need to be modified only if the column names which are not known but assumed happen to be incorrect. select vend.VendorID, vend.VendName, prod.ProductNumber, prod.ProductName from Vendors as vend inner join Product_Vendors as lookup on vend.VendorID = lookup.VendorID inner join Products prod on prod.ProductNumber = lookup.ProductNumber; Do not just take this query and submit it for your homework solution, but examine it a bit in order to understand it. It is not too difficult, but uses a common business/real world scenario. Hope this helps. Oleg