Select column(s) from subquery

Question

This is what i have now:

SELECT id,type,value
FROM cart
WHERE Delete IS NULL
    AND id IN (
    SELECT id
        from old.cart
        WHERE delete is null
            AND b_id = 15
        )

I want to get "Name" column from "old.cart" like this:

SELECT id,type,value,Name
FROM cart
WHERE Delete IS NULL
        AND id IN (
        SELECT id,Name
        from old.cart
        WHERE delete is null
            AND b_id = 15
        )

How can I achive that ?

You can left join both tables and then add the where condition — MaD, Mar 16 '17 at 15:53
Possible duplicate of [How to join two tables mysql?](http://stackoverflow.com/questions/3536283/how-to-join-two-tables-mysql) — Liam, Mar 16 '17 at 15:53
I don't understand the question. You're not doing anything with the `Name` column, why do you need to get it? — Barmar, Mar 16 '17 at 16:04
@Barmar The multi-part identifier "old.cart.Name" could not be bound. — T2Admin, Mar 16 '17 at 16:21
That's because you didn't use a `JOIN`, so you can't access fields from the subquery. — Barmar, Mar 16 '17 at 16:22

Stephen · Accepted Answer · 2017-03-16T16:18:42.683

2

You don't need a subquery for this, just include the table in a join"

SELECT c.id, c.type, c.value, o.Name
FROM cart c
LEFT JOIN old.cart o ON c.id = o.id
AND o.b_id = 15
AND o.Delete IS NULL
WHERE c.Delete IS NULL

edited Mar 16 '17 at 16:18

answered Mar 16 '17 at 15:56

Stephen

1,532
1
9
17

Using `COALESCE` like this will work, but it's kind of confusing. Maybe write `WHERE c.Delete IS NULL AND o.Delete IS NULL` to be clearer? – Barmar Mar 16 '17 at 16:13
It's not working on inner join, it gives me no result – T2Admin Mar 16 '17 at 16:14
If I run only this part: SELECT c.id, c.type, c.value FROM cart c It's working well. But as soon as I join the column from one table to other it gives me none result – T2Admin Mar 16 '17 at 16:17
@T2Admin use a `LEFT JOIN` then. – Stephen Mar 16 '17 at 16:19
@Stephen now it's only showing me columns from "cart" and not the "Name" from "old.cart" is "Null" – T2Admin Mar 16 '17 at 16:25
1

@T2Admin then it means `old.cart` does not have any records that fit the criteria. – Stephen Mar 16 '17 at 16:26
@Stephen This was the right answer, my records didn't fit the criteria – T2Admin Mar 17 '17 at 06:46

Select column(s) from subquery

1 Answers1