CODEWITHSUNDEEP
mysqlselect
James Andrew
James Andrew

Reputation: 7243

MySQL Simple Nested Select Statement

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

Upvotes: 1

Views: 204

Answers (2)

Abhay
Abhay

Reputation: 6645

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;

Upvotes: 1

blaff
blaff

Reputation: 304

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;

Upvotes: 2

Related Questions