Databases
StackOverflow2013
Postsdbo
POSub query Multiple row
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSub query Multiple row
    primarykey
    data
    text
    <p>If I add distinct to sub-query then would it bring distinct row or multiple row..??</p> <p>I have a query and I getting error</p> <pre><code>Subquery returns more than 1 row </code></pre> <p>and query is this</p> <pre><code>SELECT DISTINCT(o.order_id), (SELECT DISTINCT op.vendor_id FROM order_product op WHERE op.order_id = o.order_id) AS vendor_id, CONCAT(o.firstname, ' ', o.lastname) AS customer, CONCAT(o.shipping_address_1, o.shipping_address_2, o.shipping_city,',', o.shipping_zone,',',o.shipping_country,'-',o.shipping_postcode) AS address, (SELECT os.name FROM order_status os WHERE os.order_status_id = o.order_status_id AND os.language_id = '1') AS status, (SELECT delivery_boy_name FROM delivery_boys db WHERE o.delivery_boy_id = db.delivery_boy_id) as delivery_boy, o.delivery_boy_id, o.order_status_id, o.total, (SELECT SUM(op.total) FROM order_product op WHERE op.order_id = o.order_id AND op.vendor_id = '37') AS total, (SELECT os.cost FROM order_shipping os WHERE os.order_id = o.order_id AND os.vendor_id = '37') AS shipping_cost, o.currency_code, o.currency_value, o.date_added, o.date_modified FROM `order` o LEFT JOIN `order_product` op ON (o.order_id = op.order_id) WHERE o.order_status_id &gt; '0' AND op.vendor_id = '37' AND DATE(o.date_added) = DATE('2012-10-11') AND o.order_status_id NOT IN (6,7) ORDER BY o.date_added DESC </code></pre> <p>Sorry it is too long query..the error I am getting is because the second sub-query which is this</p> <pre><code>(SELECT DISTINCT op.vendor_id FROM order_product op WHERE op.order_id = o.order_id) AS vendor_id </code></pre> <p>In above sub-query my target is to get vendor_id from order_product table and one order_id will have same vendor_id thats why I have added distinct but still mysql is giving error of multiple row. </p> <p>How can I change this query..?? Thanks in advance.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USPankaj
    1. USPankaj
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COcan your orders be served by different vendors? if yes, then you will need to rewrite your query from the scratch (join instead of subqueries is always a good idea). If you could live with "the first vendor found"... you could replace your distinct with an evil "LIMIT 1" at the end of the subquery for vendor-id...
      singulars
      1. POSub query Multiple row
      1. USNajzero
    2. COA number of subqueries there don't use 'DISTINCT'...
      singulars
      1. POSub query Multiple row
      1. USblackcatweb
    3. COdistinct does not mean "one" row. It simply means all rows are different in at least one column. having to rely upon a DISTINCT usually points to a sloppy data model.
      singulars
      1. POSub query Multiple row
      1. USsrini.venigalla
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload