locked
Change table name based on record value? RRS feed

  • Question

  • It's a little difficult to explain in title, so here goes.

    I have the following script. It simply returns data from several tables, including [Users]. Now let's suppose that ATEntity.Name (ie. a3.Name) is "Seller" instead of "User". In that case, I would query the table [Seller] instead of the table [Users].

    Is this possible?

    Thanks.

    SELECT a3.name, u2.Username as Entity, a2.Name as Action, 
      u.Name, a1.CreatedOn as Date
      FROM   AT a1  
      JOIN ATAction a2 ON a1.ActionID = a2.ActionID  
      JOIN ATEntity a3 ON a2.EntityID = a3.EntityID  
      JOIN Users u ON a1.OperatorID = u.UserID   
      JOIN Users u2 ON a1.EntityKey = u2.UserID  
    Thursday, September 13, 2012 8:19 PM

Answers

  • Do something like:

    SELECT 
    	a3.name, 
    	u2.Username as Entity, 
    	a2.Name as Action, 
    	Name = CASE WHEN u.UserID IS NOT NULL THEN u.Name ELSE s.Name END, 
    	a1.CreatedOn as Date
      FROM   AT a1  
      INNER JOIN ATAction a2 ON a1.ActionID = a2.ActionID  
      INNER JOIN ATEntity a3 ON a2.EntityID = a3.EntityID  
      LEFT OUTER JOIN Users u ON a3.type='User' AND a1.OperatorID = u.UserID   
      LEFT OUTER JOIN Users u2 ON a1.EntityKey = u2.UserID  
      LEFT OUTER JOIN Seller s ON a3.type='Seller' AND a1.OperatorID = s.SellerID
      

    • Proposed as answer by Naomi N Thursday, September 13, 2012 10:59 PM
    • Marked as answer by Iric Wen Monday, September 24, 2012 1:19 AM
    Thursday, September 13, 2012 9:11 PM

All replies

  • You can do a couple of things.  You could join on both tables and put a condition in the result set so that you only get results from the one you want - so:

    CASE WHEN ATEntity.Name = 'Seller' then Seller.Name else User.Name end AS Name

    Or you could use a CTE first to combine the common columns from each table into one big table:

    WITH allPeople AS (

    SELECT name, ID, 'Seller' from SELLERS

    UNION

    SELECT name, ID, 'User' from USERS

    )

    Then you can join on allPeople and use an extra condition in the join to select 'Seller' or 'User' as necessary.

    Hope this helps.

    Regards

    James


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    Thursday, September 13, 2012 9:09 PM
  • Do something like:

    SELECT 
    	a3.name, 
    	u2.Username as Entity, 
    	a2.Name as Action, 
    	Name = CASE WHEN u.UserID IS NOT NULL THEN u.Name ELSE s.Name END, 
    	a1.CreatedOn as Date
      FROM   AT a1  
      INNER JOIN ATAction a2 ON a1.ActionID = a2.ActionID  
      INNER JOIN ATEntity a3 ON a2.EntityID = a3.EntityID  
      LEFT OUTER JOIN Users u ON a3.type='User' AND a1.OperatorID = u.UserID   
      LEFT OUTER JOIN Users u2 ON a1.EntityKey = u2.UserID  
      LEFT OUTER JOIN Seller s ON a3.type='Seller' AND a1.OperatorID = s.SellerID
      

    • Proposed as answer by Naomi N Thursday, September 13, 2012 10:59 PM
    • Marked as answer by Iric Wen Monday, September 24, 2012 1:19 AM
    Thursday, September 13, 2012 9:11 PM
  • No. This design flaw is usually made by OO programmers who do not understand RDBMS. You have mixed meta data (column and table names) with schema data. entity_key and entity_id make no sense in RDBMS. 

    You can kludge it with dynamic SQL and worse, but it will only produce a pile of garbage that runs like glue. 


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 14, 2012 2:33 AM