Adding columns from a query

Question

I have two tables (tblBestsellers, tblAllsellers).

tblAllsellers includes all the books in the shop
tblBestsellers includes only the ones that have sold over their quota.

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

Which database system do you use (SQLServer, MySQL, Oracle, etc.)? — Reporter
– Reporter, Commented Dec 3, 2012 at 14:56

Vikdor · Accepted Answer · 2012-12-03 17:00:13Z

2

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.

edited Dec 3, 2012 at 17:00

answered Dec 3, 2012 at 14:55

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

starydynamo Over a year ago

Thats great, so how can I combine this query to another query that has lots of inner joins? I tried UNION but I get the error message 'all queries combined using UNION, INTERSECT or EXCEPT must have an equal number of expressions in their target lines'

Vikdor Over a year ago

Enclose this query in parenthesis and give an alias like (<the above query) AS A and JOIN it with other tables appropriately. Then you can refer to the columns returned by the query as A.<columnName>. E.g. A.BestSeller.

starydynamo Over a year ago

see above update to the code (still getting an error). Thanks in advance.

Rahul Tripathi · Accepted Answer · 2012-12-03 14:56:28Z

0

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

answered Dec 3, 2012 at 14:56

Rahul Tripathi

173k33 gold badges292 silver badges341 bronze badges

1 Comment

starydynamo Over a year ago

Many thanks for all your help but have noticed that the CASE statement is either giving all 'N's or all 'Y's and not correctly comparing the two table columns. Would it make a difference if the coloumns in different tables have different names at all?

Collectives™ on Stack Overflow

Adding columns from a query

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related