asked on

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

Kevin Cross

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

techport13

ASKER

No difference in the error message.

techport13

ASKER

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

Pratima

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

techport13

ASKER

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.

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

techport13

ASKER

That's closer, but it lists the same value for all of the stock prices.

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

techport13

ASKER

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

techport13

ASKER

Point of that was to illustrate that the prices are still showing as 50.

Pratima

Can you check in the databae .. is the values are differnet ?

Can you sahre your indiviual table stucture and ample values here

techport13

ASKER

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

techport13

ASKER

Copy/Paste that into a text editor. Experts-Exchange doesn't like tabs in their code it would seem.

techport13

ASKER

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

Pratima

Fist Question ?

Is anything common for stock_history.sh_id = stock_company.sc_id
Can you recheck

Pratima

I think it must be stock_history.sh_company = stock_company.sc_id

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

ASKER CERTIFIED SOLUTION

Pratima

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

techport13

ASKER

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.

Pratima

have your problem get solved ?

techport13

ASKER

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