I get am empty set when I enter this SQL statement, could someone please tell me why?
select ayr,mods.mid,mtitle,credits
from mods,comp
where mods,mid = comp.mid and ayr = 2001/02;
It displays the right thing when omitting 'ayr = 2001/02'.
Asked
Active
Viewed 54 times
0
Shadow
- 33,525
- 10
- 51
- 64
michelle85201
- 9
- 6
-
Even if you remove `ayr = ..`, your query is syntactically wrong. It should be `where mods.mid = ..` you have a comma instead. – Madhur Bhaiya Dec 02 '18 at 07:06
-
Hi michelle. Welcome to StackOverflow!. you have to enclose date values in single quotes, and also format your date values like so: `2001-02` – Ahmad Dec 02 '18 at 07:06
-
1Please don't use Old comma based Implicit joins and use Modern [Explicit `Join` based syntax](https://stackoverflow.com/q/5654278/2469308) – Madhur Bhaiya Dec 02 '18 at 07:06
-
What does your DB have for `ayr`? Also what do you intend for `2001/02` to mean? Perhaps `and ayr regexp '^200[12]$'` is what you mean – user3783243 Dec 02 '18 at 07:08
-
the comma was a mistake I made while typing the post, sorry. Ahmad could you please elaborate? – michelle85201 Dec 02 '18 at 07:13
-
ayr stands for academic year, and yes it is in the DB, 2001/02 is the academic year – michelle85201 Dec 02 '18 at 07:15
-
try ayr = '2001/02' – travgm Dec 02 '18 at 07:18
2 Answers
1
You need quotes around the date
select ayr,mods.mid,mtitle,credits
from mods,comp
where mods,mid = comp.mid and ayr = '2001/02';
travgm
- 141
- 7
0
Are you sure that after the where clause it should be mods,mid instead of mods.mid, table name is not entered in the correct way after SELECT statement
As per my suggestion you should enter mods.mid
Try this Syntax:-
select ayr,mods,mtitle,credits from mods,comp where mods.mid = comp.mid and ayr = 2001/02;
Post the output
Harshit Bhardwaj
- 21
- 5
-
-
Yes you're right, date/time and timestamp datatypes require quotation marks, just like strings. My mistake I didn't notice it at the first sight – Harshit Bhardwaj Dec 02 '18 at 07:26