Solved

MYSQL query syntax, subquery joins alias

Posted on 2008-10-07
21
896 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
Comment Utility
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
Comment Utility
No difference in the error message.
0
 

Author Comment

by:techport13
Comment Utility
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
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
That's closer, but it lists the same value for all of the stock prices.
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
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
Comment Utility
Point of that was to illustrate that the prices are still showing as 50.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
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
Comment Utility
Copy/Paste that into a text editor.  Experts-Exchange doesn't like tabs in their code it would seem.
0
 

Author Comment

by:techport13
Comment Utility
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
Comment Utility
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
Comment Utility
I think it must be stock_history.sh_company = stock_company.sc_id
0
 
LVL 39

Expert Comment

by:Pratima Pharande
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
have your problem get solved ?
0
 

Author Comment

by:techport13
Comment Utility
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

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now