SQL Query Join with Table

JOIN two SELECT statement results

SELECT t1.ks, t1.[# Tasks], COALESCE(t2.[# Late], 0) AS [# Late]
FROM 
    (SELECT ks, COUNT(*) AS '# Tasks' FROM Table GROUP BY ks) t1
LEFT JOIN
    (SELECT ks, COUNT(*) AS '# Late' FROM Table WHERE Age > Palt GROUP BY ks) t2
ON (t1.ks = t2.ks);

Joining 2 SQL SELECT result sets into one

Use JOIN to join the subqueries and use ON to say where the rows from each subquery must match:

SELECT T1.col_a, T1.col_b, T2.col_c
FROM (SELECT col_a, col_b, ...etc...) AS T1
JOIN (SELECT col_a, col_c, ...etc...) AS T2
ON T1.col_a = T2.col_a

If there are some values of col_a that are in T1 but not in T2, you can use a LEFT OUTER JOIN instead.

how can join a query result set with an existing table?

SELECT Product_Name, Total FROM ProductTable x
LEFT OUTER JOIN (SELECT SUM(qty) as Total, ProductID FROM InventoryTable 
    GROUP BY ProductID) y
ON x.ProductID = y.ProductID

How to Join table to pull all records from first table in SQL Query

You should use LEFT JOIN. I dont know why you did not get correct result uaing LEFT JOIN but use following query for desired result.

Select stm.Name,stm.ID,sd.DESC,sd.URL 
  from StockMaster stm 
  LEFT JOIN StockDetail sd on stm.ISIN=sd.ISIN 
 Where stm.isin = 78734

LEFT JOIN gives you all data from left table and matching data from right table. So put the table from which you need all the data on left of the syntax LEFT JOIN and other table from where you just want matching data on right side of it.

All_data_from_this_table LEFT JOIN matching_data_from_this_table ON ..

Join Two Tables based on a third table's data

If you don't need any of the contents of the Discount table, use the exists() funtion to execute a sub query in the where clause. This will give you the fastest results.

SELECT * 
FROM Product as a
   left join Price as b on a.Ref# = b.Ref#
WHERE EXISTS (
      SELECT *
      FROM Discount as c
      WHERE c.ProductID = a.ProductID
   )

If however you do need one or more of the columns of Discount, do an inner join between Product and Discount, joining them on the ProductID. This will result in only the products that have discount, and then do another left join to Price to get the columns from Price into the resultset too. Do be aware though that in case multiple rows exist in Discount for the one Product row, this will result in the same product shown on multiple rows.

SELECT * 
FROM Product as a
   inner join Discount as c on c.ProductID = a.ProductID
   left join Price as b on a.Ref# = b.Ref#

Dynamically looping multiple tables to inner join clause

Since you are adding your static beginning string to the variable each time, you are getting your current results. Add another variable to store the moving target, and move the static bit out of the WHILE loop.

    DECLARE @max int = 20
    DECLARE @SQL VARCHAR(MAX) 
    DECLARE @TableName VARCHAR(50)
    DECLARE @id int = 1 
    Declare @Param1 varchar(20) ='p1', @param2 varchar(20) ='p2'
    DECLARE @qry NVARCHAR(MAX)
    DECLARE @SQL2 VARCHAR(MAX) = ' '
    Declare @strcolumns nvarchar(max) = 'select c1, c2, c3, ...c30 from primarytable '  
---------------  
select @max = MAX(Id) from @Table
WHILE (@id <= @max)
BEGIN
SELECT @TableName = TableName FROM @Table WHERE Id = @id
SET @SQL =      'INNER JOIN  '+ @TableName + ' ON p.' + @Param1 + ' = ' + @TableName + '.'+ @param1 + ' AND p.' +  @Param2 + ' = ' + @TableName + '.'+ @param2 + ' '
SET @id = @id +1
SET @SQL2 = @SQL2 + @SQL
END 
SET @strcolumns = @strcolumns + @SQL2
PRINT (@strcolumns)

---

Related Topics