sub query help. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sub query help.

Author

Topic

bob21
Starting Member

1 Post

Posted - 2012-05-02 : 06:29:33

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.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2012-05-02 : 06:36:46

The pipes are not T_SQL code. Are you using oracle or MySql? This forum is exclusively for MS SQL SERVER (Transact SQL).

These guys take care of a LOT of different systems: http://www.dbforums.com/

How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

vijays3
Constraint Violating Yak Guru

354 Posts

Posted - 2012-05-02 : 10:22:21

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;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-03 : 10:21:04

You have two aliases b1 and transaction_types for derived table

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-03 : 14:45:49

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/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-05-04 : 06:19:16

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/

Then the query should work fine in Mysql

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources