MySQL - use WHERE IN clause to JOIN 2 tables

Question

I am trying to join 2 tables using WHERE and IN, instead of JOIN/LEFT JOIN.

This is the SQL I wrote which failed when I run it:

SELECT 
      product.price as price,
      product.date as date,
      product.status as status,
      a.Title as title
FROM MarketingDb.Product product
WHERE product.Id
IN (
    SELECT a.ListingId, a.Title FROM MarketingDb.Status a
)
LIMIT 15;

Error message mentioned: Unknown column 'a.Title' in 'field list'

From my understanding, it appears that the error happened because the SQL I wrote only mentioned table MarketingDb.Status a inside the subquery, and it's not recognized in the outer SELECT clause.

I would like to ask: how the SQL should be amended such that I can select a.Title successfully? Thx a lot for your kind help!

Edit1:

May I ask if instead of just 2 tables, now I wanna join 3rd/4th/... more tables using IN, what should be the right way/syntax to do so?

This is what I tried but failed:

SELECT 
      product.price as price,
      product.date as date,
      product.status as status,
      (SELECT s.Title FROM MarketingDb.Status s WHERE s.ListingId = product.Id) 
      AS title
FROM MarketingDb.Product product
WHERE product.Id
IN (
    SELECT DISTINCT a.ListingId, a.Title FROM MarketingDb.Status a
)
AND IN (
    SELECT DISTINCT b.Id FROM MArketingDb.Price b
)
LIMIT 15;

Answer 1

If you want to do this without a join, then use a scalar correlated subquery. Assuming that status(listingid) relates to product(id):

select 
    p.price,
    p.date,
    p.status,
    (select s.title from marketingdb.status s where s.listingid = p.id) as title
from marketingdb.product p

It should be highlighted that this will error if there is more than one row in status that matches on a single product(id) - if you have such situation, then either limit the number of rows in the subquery... or use a join, which allows multiple matches..