Solved

MYSQL query syntax, subquery joins alias

Posted on 2008-10-07
21
901 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 59

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
Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

 
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 500 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.

830 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