Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MYSQL query syntax, subquery joins alias

Posted on 2008-10-07
21
Medium Priority
?
907 Views
Last Modified: 2008-10-07
The following query returns an error saying that I have invalid syntax near ' ' at line 1.  The basics of this is:

I have a stock_history table that stores historical stock prices, a stock company table that stores the company name and symbol, and a stock transaction table that stores each buy/sell transaction.

I'm writing the query to display someones portfolio - so it will show the company names, the number of shares the person owns and the most recent price of each company's shares, ie: google - 200 - $12.54

If i remove the subquery in the where clause, the query executes just fine.

I will also have to eventually modify this query so that it will only use use qty rows from the stock_transaction table when the date of the transaction is greater than the date the company was activated.
SELECT b.sc_name AS company, sum( a.st_qty ) AS 'shares', (
  SELECT c.sh_value
  FROM stock_history c
  WHERE c.sh_id = b.sc_id
  ORDER BY c.sh_date DESC
  LIMIT 1
) AS 'value'
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
WHERE a.st_owner =1
GROUP BY b.sc_name ASC

Open in new window

0
Comment
Question by:techport13
  • 11
  • 9
21 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 22666202
Try removing single quotes (') with ` for MySql column names or just not quote at all.  'text' denotes a string literal.
SELECT b.sc_name AS company, sum( a.st_qty ) AS `shares`, (
  SELECT c.sh_value
  FROM stock_history c
  WHERE c.sh_id = b.sc_id
  ORDER BY c.sh_date DESC
  LIMIT 1
) AS `value`
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
WHERE a.st_owner =1
GROUP BY b.sc_name ASC

Open in new window

0
 

Author Comment

by:techport13
ID: 22666205
No difference in the error message.
0
 

Author Comment

by:techport13
ID: 22666210
Should also note that the full text of the error (according to phpMyAdmin) is:
Error
 
SQL query: Edit
 
SHOW KEYS FROM
 
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666307
SELECT b.sc_name AS company, sum( a.st_qty ) AS shares, (
  SELECT c.sh_value
  FROM stock_history c
  WHERE c.sh_id = b.sc_id
  ORDER BY c.sh_date DESC
  LIMIT 1
) AS value
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
WHERE a.st_owner =1
GROUP BY b.sc_name ASC
0
 

Author Comment

by:techport13
ID: 22666319
Same error as before.  I've tried it without the aliases, with double quoted, single quoted, not quoted, and backticked aliases.  Each attempt gives me the same error.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666402
SELECT b.sc_name AS company, sum( a.st_qty ) AS shares, c.sh_value AS value
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
INNER JOIN stock_history c ON c.sh_id = b.sc_id  AND c.sh_value = (  SELECT c.sh_value
  FROM stock_history c
  WHERE c.sh_id = b.sc_id
  ORDER BY c.sh_date DESC
  LIMIT 1)
WHERE a.st_owner = 1  
GROUP BY b.sc_name ASC
0
 

Author Comment

by:techport13
ID: 22666410
That's closer, but it lists the same value for all of the stock prices.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666437
SELECT b.sc_name AS company, sum( a.st_qty ) AS shares, c.sh_value AS value
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
INNER JOIN stock_history c ON c.sh_id = b.sc_id  AND c.sh_value = (  SELECT c1.sh_value
  FROM stock_history c1
  WHERE c1.sh_id = b.sc_id
  ORDER BY c1.sh_date DESC
  LIMIT 1)
WHERE a.st_owner = 1  
GROUP BY b.sc_name ASC
0
 

Author Comment

by:techport13
ID: 22666453
Allied Chemical        -239983        50
American Can       -127979       50
American Smelting       -140850       50
American Sugar       5200       50
American Tobacco B       -197946       50
Atlantic Refining       -3809       50
Bethlehem Steel       -195191       50
Chrysler       -125209       50
Curtis-Wright       -310823       50
General Electric Company       -157273       50
General Foods       -210019       50
General Motors Corporation       -1234       50
General Railway Signal       -239069       50
Goodrich       -217041       50
0
 

Author Comment

by:techport13
ID: 22666454
Point of that was to illustrate that the prices are still showing as 50.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666470
Can you check in the databae .. is the values are differnet ?

Can you sahre your indiviual table stucture and ample values here
0
 

Author Comment

by:techport13
ID: 22666515
The values are most definitely different.  Table structure and sample data below:
stock_company
sc_id 	sc_symbol 	sc_name 									sc_active 	sc_activated 	sc_deac
1 			CHEM 				Allied Chemical 					1 					1223442000 		0
8 			CHR 				Chrysler 									1 					1223442000		0
15 			IHRV 				International Harvester 	1 					1223442000		0
20 			NCR 				National Cash Register 		1 					1223442000		0
23 			SRC 				Sears Roebuck & Company 	1 					1223442000		0
29 			WE 					Westinghouse Electric 		1 					1223442000		0   
 
stock_history 
sh_id		sh_company	sh_date			sh_value                                                                        
104662  23  				1223445300  18                                                                                   
104663 	29 					1223445300 	20                                                                         
104658 	1 					1223445300 	26                                                                         
104659 	8 					1223445300 	12                                                                         
104660 	15 					1223445300 	12                                                                         
104661 	20 					1223445300 	32
104664 	1 					1223445600 	30
104665 	8 					1223445600 	11
104666 	15 					1223445600 	16
104667 	20 					1223445600 	32
104668 	23 					1223445600 	18
 
stock_transaction
st_id 	st_company 	st_date 		st_owner 	st_qty 	st_value
444630 	8 					1223446152 	5 				-34 	11
444660 	15 					1223446153 	9 				-86 	16
444689 	23 					1223446153 	3 				-56 	18
444695 	23 					1223446162 	3 				-117 	18
444709 	29 					1223446165 	7 				-170 	17
444715 	29 					1223446168 	6 				-188 	17
444717 	29 					1223446168 	8 				-149 	17
444637 	8 					1223446170 	7 				-193 	11

Open in new window

0
 

Author Comment

by:techport13
ID: 22666518
Copy/Paste that into a text editor.  Experts-Exchange doesn't like tabs in their code it would seem.
0
 

Author Comment

by:techport13
ID: 22666527
Hopefully better spaced this time:
stock_company
sc_id  sc_symbol  sc_name                  sc_active  sc_activated  sc_deac
1      CHEM       Allied Chemical          1          1223442000    0
8      CHR        Chrysler                 1          1223442000    0
15     IHRV       International Harvester  1          1223442000    0
20     NCR        National Cash Register   1          1223442000    0
23     SRC        Sears Roebuck & Company  1          1223442000    0
29     WE         Westinghouse Electric    1          1223442000    0   
 
stock_history 
sh_id   sh_company  sh_date     sh_value                                                                        
104662  23          1223445300  18                                                                                   
104663  29          1223445300  20                                                                         
104658  1           1223445300  26                                                                         
104659  8           1223445300  12                                                                         
104660  15          1223445300  12                                                                         
104661  20          1223445300  32
104664  1           1223445600  30
104665  8           1223445600  11
104666  15          1223445600  16
104667  20          1223445600  32
104668  23          1223445600  18
 
stock_transaction
st_id   st_company  st_date     st_owner  st_qty  st_value
444630  8           1223446152  5         -34     11
444660  15          1223446153  9         -86     16
444689  23          1223446153  3         -56     18
444695  23          1223446162  3         -117    18
444709  29          1223446165  7         -170    17
444715  29          1223446168  6         -188    17
444717  29          1223446168  8         -149    17
444637  8           1223446170  7         -193    11

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666531
Fist Question  ?

Is anything common for  stock_history.sh_id = stock_company.sc_id
Can you recheck
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666538
I think it must be stock_history.sh_company = stock_company.sc_id
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666567
SELECT b.sc_name AS company, sum( a.st_qty ) AS shares, c.sh_value AS value
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
INNER JOIN stock_history c ON c.sh_company = b.sc_id  AND c.sh_value = (  SELECT c1.sh_value
  FROM stock_history c1
  WHERE c1.sh_company = b.sc_id
  ORDER BY c1.sh_date DESC
  LIMIT 1)
WHERE a.st_owner = 1  
GROUP BY b.sc_name ASC
0
 
LVL 39

Accepted Solution

by:
Pratima Pharande earned 2000 total points
ID: 22666573
SELECT b.sc_name AS company, sum( a.st_qty ) AS shares, c.sh_value AS value
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
INNER JOIN stock_history c ON c.sh_company = b.sc_id  AND c.sh_id = (  SELECT c1.sh_id
  FROM stock_history c1
  WHERE c1.sh_company = b.sc_id
  ORDER BY c1.sh_date DESC
  LIMIT 1)
WHERE a.st_owner = 1  
GROUP BY b.sc_name ASC
0
 

Author Comment

by:techport13
ID: 22666583
it should have been sh_company instead of sh_id, my apologies for not catching that initially, must have been an old copy.  The latest query is taking ages to return.  Still waiting.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 22666600
have your problem get solved ?
0
 

Author Comment

by:techport13
ID: 22666605
The most recent query works just like i'd expect it to.  Thank you very much for your time and assistance Pratima!

SELECT b.sc_name AS company, sum( a.st_qty ) AS shares, c.sh_value AS value
FROM stock_transaction a
INNER JOIN stock_company b ON a.st_company = b.sc_id
INNER JOIN stock_history c ON c.sh_company = b.sc_id  AND c.sh_id = (  SELECT c1.sh_id
  FROM stock_history c1
  WHERE c1.sh_company = b.sc_id
  ORDER BY c1.sh_date DESC
  LIMIT 1)
WHERE a.st_owner = 1  
GROUP BY b.sc_name ASC
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question