Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MYSQL query syntax, subquery joins alias

Posted on 2008-10-07
21
Medium Priority
?
906 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Implementing Azure Infrastructure Exam 70-533

This course is designed to familiarize and instruct students in the content that is covered by Microsoft Exam 70-533, Implementing Microsoft Azure Solutions. It focuses on all the November 2016 objective domain topics.

 
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

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

715 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