Solved

MYSQL query syntax, subquery joins alias

Posted on 2008-10-07
21
897 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
 
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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
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 …
A company’s greatest vulnerability is their email. CEO fraud, ransomware and spear phishing attacks are the no1 threat to a company’s security. Cybercrime is responsible for the largest loss of money to companies today with losses projected to r…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

911 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

23 Experts available now in Live!

Get 1:1 Help Now