JOIN in DataSets – Good idea or bad idea?

Very often we programmers are needed to use JOIN statements in our code.  We use JOINs to configure DataSource controls, populate Datasets, etc.

SELECT statements would be used to join 2,3 or more tables. So far so good. But problem arises when using UPDATE, DELETE, INSERT statements. It is not possible to update 2 or more tables in one statement. Also UPDATE, INSERT, DELETE statements would not be automatically generated when configuring the data source. The programmer has to code these statements.

To avoid these difficulties and to make our lives easy, it is better to use subqueries in SELECT statements. When configuring the data source, there is an easy way to make our lives better:

1. Use a SELECT statement on one table.

2. Click ‘Advanced’ tab and Check ‘Automatically Generate INSERT, UPDATE, DELETE statements’.

Now Configure the same datasource again. Now use 1or more SELECT subqueries within the original subquery.

This would give us the same desired result like using JOINs. Also, INSERT, UPDATE, DELETE statements aer automatically generated for us.

SQL QUERY USING JOINS:

USE NORTHWIND

SELECT TOP 5 PDT.PRODUCTNAME, CAT.CATEGORYNAME, SUP.COMPANYNAME FROM CATEGORIES CAT
INNER JOIN PRODUCTS PDT ON CAT.CATEGORYID=PDT.CATEGORYID
INNER JOIN SUPPLIERS SUP ON SUP.SUPPLIERID = PDT.SUPPLIERID

Now the same result can be produced using the below SQL query:

USE NORTHWIND

SELECT TOP 5 PRODUCTNAME,
(SELECT CATEGORYNAME FROM CATEGORIES WHERE CATEGORIES.CATEGORYID=PRODUCTS.CATEGORYID) AS CATEGORYNAME,
(SELECT COMPANYNAME FROM SUPPLIERS WHERE SUPPLIERS.SUPPLIERID = PRODUCTS.SUPPLIERID) AS SUPPLIERNAME
FROM PRODUCTS

Reference: Scott Mitchell’s Data Access Tutorials in www.asp.net

Tags: dataset, JOIN, subquery