CODEWITHSUNDEEP
mysql
Haider Abbas
Haider Abbas

Reputation: 133

Using data from same mysql table in the query

I have found a little strange problem. Here is the query:

SELECT *, @allaccts := TRIM( BOTH  ',' FROM owneridslinked4billing) as 
 targetids, ( select sum(price) from orderstable where 
   paymentstatus='unpaid' and ownid in (@allaccts) ) as amountdue FROM users

The problem is that the output of targetids is:

110909,110910,110911,110912

(exactly what is required)

But the query is outputting wrong results. It is not including all ids (110909,110910,110911,110912) in the query "and ownid in (@allaccts)". I guess it is taking all comma separated value as one instead of separate values so outputting wrong results.

I hope it makes sense.

All I want is to ask mysql to use the data from owneridslinked4billing field in the query and output the result where ownid is in owneridslinked4billing (which is a comma separated list of ids).

Upvotes: 1

Views: 26

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

Using FIND_IN_SET might offer an immediate fix here:

SELECT *, @allaccts := TRIM( BOTH ',' FROM owneridslinked4billing) as targetids,
    ( select sum(price) from orderstable
      where paymentstatus='unpaid' and
      FIND_IN_SET(ownid, @allaccts) > 0 ) as amountdue
FROM users;

This would work assuming that your intention is to find a ownid value in the CSV list of all accounts 110909,110910,110911,110912.

Moving forward, you should avoid FIND_IN_SET and CSV data in general, and normalize your data. Move each id to a separate row and do a join.

Upvotes: 1

Related Questions