• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 910
  • Last Modified:

MYSQL query syntax, subquery joins alias

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
techport13
Asked:
techport13
  • 11
  • 9
1 Solution
 
Kevin CrossChief Technology OfficerCommented:
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
 
techport13Author Commented:
No difference in the error message.
0
 
techport13Author Commented:
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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Pratima PharandeCommented:
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
 
techport13Author Commented:
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
 
Pratima PharandeCommented:
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
 
techport13Author Commented:
That's closer, but it lists the same value for all of the stock prices.
0
 
Pratima PharandeCommented:
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
 
techport13Author Commented:
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
 
techport13Author Commented:
Point of that was to illustrate that the prices are still showing as 50.
0
 
Pratima PharandeCommented:
Can you check in the databae .. is the values are differnet ?

Can you sahre your indiviual table stucture and ample values here
0
 
techport13Author Commented:
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
 
techport13Author Commented:
Copy/Paste that into a text editor.  Experts-Exchange doesn't like tabs in their code it would seem.
0
 
techport13Author Commented:
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
 
Pratima PharandeCommented:
Fist Question  ?

Is anything common for  stock_history.sh_id = stock_company.sc_id
Can you recheck
0
 
Pratima PharandeCommented:
I think it must be stock_history.sh_company = stock_company.sc_id
0
 
Pratima PharandeCommented:
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
 
Pratima PharandeCommented:
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
 
techport13Author Commented:
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
 
Pratima PharandeCommented:
have your problem get solved ?
0
 
techport13Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now