CODEWITHSUNDEEP
mysqlsql
Michelle
Michelle

Reputation: 2235

MySQL WHERE subquery with comparison operator

Im trying to retrieve a list of customer and invoice ID's from our orders table where the customer has only one order. The follow SQL returns zero records, there should be dozens. The subquery seems to work fine on its own if I substitute a valid userid into it. I must be doing something very wrong, any help?

SELECT tblclients.id AS clientid, tblinvoices.id AS invoiceid
            FROM tblorders
            join tblinvoices ON tblorders.invoiceid = tblinvoices.id
            join tblclients ON tblorders.userid = tblclients.id
            WHERE (SELECT COUNT(*) FROM tblorders WHERE userid = tblorders.userid) = 1;

Upvotes: 0

Views: 431

Answers (1)

Gonzalo.-
Gonzalo.-

Reputation: 12672

I think you got a problem with the alias. try add the T1 alias:

SELECT tblclients.id AS clientid, tblinvoices.id AS invoiceid
            FROM tblorders T1
            join tblinvoices ON T1.invoiceid = tblinvoices.id
            join tblclients ON T1.userid = tblclients.id
            WHERE (SELECT COUNT(*) FROM tblorders WHERE userid = T1.userid) = 1;

Upvotes: 1

Related Questions