CODEWITHSUNDEEP
mysqlsql
user14067569
user14067569

Reputation:

How to derive boolean value via SQL

I can't update or insert; i can only select. What i have so far is

    SELECT
    m.id AS "Customer #", m.email AS "Email", p.purchased AS "Made Purchase"
    FROM customer.profile AS m
    LEFT JOIN customer.purchased AS p ON p.id = m.id
    WHERE p.status = "SUCCESS"
    GROUP BY m.id

customer.profile tables structure is:

id email
1 [email protected]
2 row
3 row
4 row
5 row

customer.purchased tables structure is:

purchase_id customer_id purchase_price purchase_status
1 1 $20.00 "SUCCESS"
2 2 $20.00 "PENDING"
3 3 $20.00 "SUCCESS"

Desired output of SELECT is:

| Customer #     | Email           | Made Purchase     |
|----------------|-----------------|-------------------|
| 1              | [email protected]     | 1                 |
| 2              | [email protected]     | 0                 |
| 3              | [email protected]     | 0                 |
| 4              | [email protected]     | 1                 |
| 5              | [email protected]     | 0                 |

customer.purchased will always have a subset of customer_id from customer.profile. How do i go about this using my original query?

Upvotes: 0

Views: 104

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270351

I suspect that you want:

SELECT m.id as customer_number, m.email AS email,
       (p.id IS NOT NULL) AS made_purchase
FROM customer.profile m LEFT JOIN
     customer.purchased p
     ON p.id = m.id AND p.status = 'SUCCESS'
GROUP BY m.id

Upvotes: 1

Related Questions