CODEWITHSUNDEEP
mysql
Alko
Alko

Reputation: 1439

MySql, assign variable to non existent field (Hardcoding a value as a column value)

I'm not sure if this is possible or not, but for example in this case:

SELECT 
  i.name 
FROM
  items i 
  INNER JOIN users u 
    ON u.item_id = i.id 
  INNER JOIN sales s 
    ON s.item_id = i.id 
WHERE u.status = 1 
  AND s.status = 1

I would like to assign non existing field to "type", so if records belong to "users" table type = Users, otherwize type = Sales.

it should look something like:

|        name | type  |
|-------------|-------|
|  First Item | User  |
| Second Item | Sales |
| Second Item | User  |

Upvotes: 3

Views: 85

Answers (1)

Tin Tran
Tin Tran

Reputation: 6202

I think this might be what you're trying to do.

SELECT i.name,'User' as type FROM items i 
    INNER JOIN users u ON u.item_id = i.id WHERE u.status = 1
UNION
SELECT i.name,'Sales' as type FROM items i 
    INNER JOIN sales s ON s.item_id = i.id WHERE s.status = 1

http://sqlfiddle.com/#!9/c81bf/2

Inner Join items with users and hardcode 'User' as type

Inner Join items with sales and hardcode 'Sales' as type

Then UNION the 2 selects together.

Upvotes: 4

Related Questions