Subquery from returned query value in sql?

Question

I have three tables- sales, sales_details, medicine. From sales table, I'll collect the sale_id and with that I'll get details of a particular sale from sales_details. Along with that, in the sales_details, there is one cell for medicine id (mid). Using that mid, I would also like to get the medicine name and return all of the data in one query.

Here is what I have so far-

        SELECT `mid`, `qty`, `rate`, `total_price`, `discount`, `total_discount`, (SELECT `medicine`.`product_name` FROM `medicine` WHERE `sales_details`.`mid` = `medicine`.`product_id`) AS 'medicine_name'
        FROM `sales_details`
        WHERE `sale_id` IN (SELECT `sale_id` FROM `sales` WHERE `invoice_no` = '$invoiceID;

I get all the data including mid but medicine_name is null in each row. What is wrong in the query plz?

You need to INNER JOIN your 3 related tables on their common columns. — Alex K.
– Alex K., Commented Mar 2, 2018 at 12:32

ScaisEdge · Accepted Answer · 2018-03-02 12:42:32Z

1

You coudl avoid subquery using inner join

  SELECT s.`mid`
  , s.`qty`
  , s.`rate`
  , s.`total_price`
  , s.`discount`
  , s.`total_discount`
  , m.`product_name` medicine_name
  FROM `sales_details` d
  INNER JOIN `medicine` d.mid = m.product_id 
  INNER JOIN sales  s on s.sale_id = d.sale_id AND $invoiceID

but should not use php var in sql you are at risk for sqlinjection .. for this take a look at your sql driver for binding param

answered Mar 2, 2018 at 12:42

ScaisEdge

133k10 gold badges98 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Subquery from returned query value in sql?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related