MySQL Simple Nested Select Statement

Question

I have one table reports, which contains all info and read reports which just has Report ID and ID (of owner of the report)

I'm trying to do this statment (correct me if theres better out there) so it gets all the reports ID from read reports which match ID 1, and pick out all the details from reports for it. (Report ID's are the same on reports and read reports)

But this statement is giving me back no rows:

SELECT a.* 
 FROM `Reports` AS a, 
      (SELECT `Report ID` FROM `Read Reports` WHERE `Id` = 1) AS b
      WHERE a.`Report ID` = b.`Report ID`;

Whats wrong with it/how can I improve it?

Thanks,

EDIT: My bad, it works fine!! Id 1 had no reports. Close this. :L

EDIT2: Still post if you have improvements though :P

EDIT: My bad, it works fine!! Id 1 had no reports. Close this. :L — James Andrew
– James Andrew, Commented Feb 28, 2012 at 16:24

blaff · Accepted Answer · 2012-02-28 16:20:11Z

2

Try this:

SELECT a.*, (SELECT `Report ID` FROM `Read Reports` WHERE `Id` = 1) AS b_report_id
FROM `Reports` AS a
HAVING a.`Report ID` = b_report_id;

edited Feb 28, 2012 at 16:20

answered Feb 28, 2012 at 16:09

blaff

3041 silver badge5 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

James Andrew Over a year ago

#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 'WHERE a.Report ID = b_report_id

blaff Over a year ago

Try it now. I didn't remove the last comma after "From Reports AS a".

James Andrew Over a year ago

Still no :( #1054 - Unknown column 'b_report_id' in 'where clause'

Abhay · Accepted Answer · 2012-02-28 16:34:19Z

1

There seems to be nothing wrong with your query and it should return the records unless there are no matching records. But if you say that there do exist matching records, I'd suggest that you re-read your query to confirm that you are using the right column names, i.e. not replaced "Id" with "Report ID"?

Can you give a snapshot of your data in your post?

By the way, the below query should be better because it does not involve derived table:

SELECT `a`.*
FROM `Reports` AS `a`
INNER JOIN `Read Reports` AS `b` ON `a`.`Report ID` = `b`.`Report ID`
WHERE `b`.`Id` = 1;

answered Feb 28, 2012 at 16:34

Abhay

6,6852 gold badges26 silver badges17 bronze badges

Collectives™ on Stack Overflow

MySQL Simple Nested Select Statement

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related