CODEWITHSUNDEEP
sqljoin
starydynamo
starydynamo

Reputation: 49

Adding columns from a query

I have two tables (tblBestsellers, tblAllsellers).

The only link between them is their productID.

Is it possible to add a column with the title Bestseller at runtime? Using a query in which will show if that book was a best seller or not such as 

ProductID   - Product Name  - Bestseller   
----------------------------------------
324234        Harry Potter        Y
382932        LOTHR               Y
234292        SQL Guide           N

and so on.

I suspect there is an IF statement there somewhere??

SELECT *
FROM tblAllsellers, tblbestsellers

Upvotes: 1

Views: 74

Answers (2)

Vikdor
Vikdor

Reputation: 24134

You should perform a LEFT JOIN of tblAllSellers with tblBestSellers on ProductID. If there is a record in the tblBestSellers for a given ProductID, it means that is a best seller and otherwise not. So, you can use this column in the CASE statement for that last column, BestSeller.

SELECT 
    A.*, 
    CASE 
        WHEN B.ProductId IS NULL THEN 'N' 
        ELSE 'Y' 
    END 'BestSeller'
FROM 
    tblAllSellers A
    LEFT JOIN tblBestSellers B
    ON A.ProductId = B.ProductID

You would use this in another query with JOINs as follows:

 SELECT A.* 
 FROM 
     AnotherTable X
     JOIN 
     (
        SELECT 
            A.*, 
            CASE 
                WHEN B.ProductId IS NULL THEN 'N' 
                ELSE 'Y' 
            END 'BestSeller'
        FROM 
            tblAllSellers A
            LEFT JOIN tblBestSellers B
            ON A.ProductId = B.ProductID
     ) AS Y
     ON X.ProductId = Y.ProductID

If you want to join the results of this query with those from another query, then you would do it as follows:

 SELECT A.ProductId, A.COlumn1, A.Column2 ...
 FROM 
     AnotherTable X

 UNION 
 SELECT Y.ProductId, Y.Column1, Y.Column2..
 FROM
 (
 SELECT 
      A.*, 
      CASE 
           WHEN B.ProductId IS NULL THEN 'N' 
           ELSE 'Y' 
      END 'BestSeller'
 FROM 
      tblAllSellers A
      LEFT JOIN tblBestSellers B
      ON A.ProductId = B.ProductID
 ) AS Y

For the UNION scenario, make sure the number, data type of the columns in both the SELECT statements are the same.

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172468

You can better use a Case statement.

 SELECT A.*, CASE WHEN B.ProductId IS NULL THEN 'N' ELSE 'Y' END 'BestSeller'
 FROM 
tblAllSellers A, tblBestSellers B
where A.ProductId = B.ProductID

Upvotes: 0

Related Questions