title: how to call columns from different table using sub-queries in mysql with certain condition
body: <p>//this is the question
  Find customer name, order number and order amount if order amount is greater than average
  sales amount of the customers and sort the record by customer name [Hint: use WHERE
  clause].</p>
  
  <pre><code>SELECT 
  name, orderno, amount
  FROM
  customerinfo,
  orderdetails
  WHERE
  amount &gt; (SELECT 
          AVG(amount)
      FROM
          orderdetails)
  
  ORDER BY name;      
  </code></pre>
  
  <p>//this the result <a href="https://i.stack.imgur.com/6wBs6.png" rel="nofollow noreferrer">the result are not what i look for</a></p>
  
  <p>this the table coding</p>
  
  <pre><code>CREATE TABLE customerinfo (
  customercode VARCHAR(10) NOT NULL UNIQUE,
  name VARCHAR(60) NOT NULL,
  address VARCHAR(60),
  poscode INT(5),
  city VARCHAR(30),
  state VARCHAR(30),
  hpno VARCHAR(20),
  email VARCHAR(50),
  salesmanid VARCHAR(10),
  PRIMARY KEY (customercode),
  FOREIGN KEY (salesmanid)
      REFERENCES salesman (salesmanid)
  );
  
  CREATE TABLE salesman (
  salesmanid VARCHAR(10) NOT NULL UNIQUE,
  salesmanname VARCHAR(60) NOT NULL,
  PRIMARY KEY (salesmaniD)
  );
  
  CREATE TABLE orderinfo (
  orderno INT(10) NOT NULL,
  ordertype VARCHAR(50),
  orderdate DATE,
  deliverydate DATE,
  PRIMARY KEY (orderno)
  );
  
  CREATE TABLE orderdetails (
  orderno INT(10) NOT NULL,
  itemcode VARCHAR(10) NOT NULL,
  quantity INT(5),
  amount DECIMAL(9 , 2 ),
  PRIMARY KEY (orderno , itemcode),
  FOREIGN KEY (orderno)
      REFERENCES orderinfo (orderno),
  FOREIGN KEY (itemcode)
      REFERENCES item (itemcode)
  );
  
  CREATE TABLE item (
  itemcode VARCHAR(10) NOT NULL UNIQUE,
  itemdesc VARCHAR(60),
  unit VARCHAR(10),
  sellingprice DECIMAL(9 , 2 ) NOT NULL,
  PRIMARY KEY (itemcode)
  );
  
  CREATE TABLE ordersalesmaninfo (
  salesmanid VARCHAR(10) NOT NULL,
  orderno INT(10) NOT NULL,
  PRIMARY KEY (salesmanid),
  FOREIGN KEY (salesmanid)
      REFERENCES salesman(salesmanid),
  FOREIGN KEY (orderno)
      REFERENCES orderinfo (orderno)
  );
  </code></pre>
  
  <p>i may have problem with the normalisation process but i think there will not cause any impact to the question .</p>