SELECT b.first_name ||' '|| b.last_name AS "BROKER"
FROM  brokers b  INNER JOIN trades t ON b.broker_id=t.broker_id
  INNER JOIN 
      (SELECT t.broker_id, shs.share_holder_id     
      FROM share_holder_shares shs   
  INNER JOIN trades t ON shs.share_holder_id = t.share_holder_id 
  INNER JOIN brokers b ON b.broker_id = t.broker_id 
  WHERE shs.amount > 500000
  GROUP BY shs.share_holder_id) b1
 transaction_types ON b1.type_id = transaction_types.type_id;

quote:

---

Originally posted by bob21

i am write a single query which displays all brokers who allowed
shareholders to buy or sell more than 50,000 shares per trade

This is what i have done so far using a correlated sub query

SELECT b.first_name ||' '|| b.last_name AS "BROKER"
FROM  brokers b  INNER JOIN trades t ON b.broker_id=t.broker_id
  INNER JOIN 
      (SELECT t.broker_id, shs.share_holder_id     
      FROM share_holder_shares shs   
  INNER JOIN trades t ON shs.share_holder_id = t.share_holder_id 
  INNER JOIN brokers b ON b.broker_id = t.broker_id 
  WHERE shs.amount > 500000
  GROUP BY shs.share_holder_id) b1
 transaction_types ON b1.type_id = transaction_types.type_id;

i can 't seem to display the correct info as i keep getting the error 'missing keyword'?? :/

also how does the insert image work on the forum, i have an image of my er diagram that i want to show.

---

If it is Sql server Try this .

SELECT b.first_name +' '+ b.last_name AS "BROKER"
FROM  brokers b  INNER JOIN trades t ON b.broker_id=t.broker_id
  INNER JOIN 
      (SELECT t.broker_id, shs.share_holder_id     
      FROM share_holder_shares shs   
  INNER JOIN trades t ON shs.share_holder_id = t.share_holder_id 
  INNER JOIN brokers b ON b.broker_id = t.broker_id 
  WHERE shs.amount > 500000
  GROUP BY shs.share_holder_id) b1
 transaction_types ON b1.type_id = transaction_types.type_id;

quote:

---

Originally posted by madhivanan

You have two aliases b1 and transaction_types for derived table

Madhivanan

Failing to plan is Planning to fail

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by madhivanan

You have two aliases b1 and transaction_types for derived table

Madhivanan

Failing to plan is Planning to fail

---

I think its a copy paste typo from original query

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

---