Databases
StackOverflow2013
Postsdbo
POMySQL returns incorrect data with use of join
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL returns incorrect data with use of join
    primarykey
    data
    text
    <p>I have join with a multiple tables. You can find the tables and MySQL query in this <a href="http://sqlfiddle.com/#!2/324ed/1" rel="nofollow" title="Sqlfiddle link for demo">demo</a>. </p> <p>These are the tables:</p> <ol> <li><p><strong>users</strong></p> <pre class="lang-none prettyprint-override"><code>id name 1 abc 2 xyz 3 pqr </code></pre></li> <li><p><strong>friend</strong></p> <pre class="lang-none prettyprint-override"><code>user_id friend_id 1 2 1 3 2 3 </code></pre></li> <li><p><strong>collection</strong></p> <pre class="lang-none prettyprint-override"><code>id user_id friend_id amount 1 1 2 100 2 2 1 -100 3 2 3 200 4 3 2 -200 5 1 3 300 6 3 1 -300 </code></pre></li> <li><p><strong>bill</strong></p> <pre class="lang-none prettyprint-override"><code>id use_id(bill_creator) 1 1 2 2 3 2 </code></pre></li> <li><p><strong>bill_person</strong></p> <pre class="lang-none prettyprint-override"><code>id bill_id user_id 1 1 1 2 1 2 3 1 3 4 2 2 5 2 3 6 3 2 6 3 1 </code></pre></li> </ol> <p>So far I have made this query:</p> <pre class="lang-sql prettyprint-override"><code>SELECT mf.id , mf.name , c.amount AS amount,count(bp.user_id) AS no_common_bills FROM ( SELECT fr.user_id AS user_id , fr.friend_id AS friend_id FROM friend fr JOIN users fru ON fru.id = fr.user_id WHERE fru.id IN (1) UNION SELECT fl.friend_id AS user_id , fl.user_id AS friend_id FROM friend fl JOIN users flf ON flf.id = fl.friend_id WHERE flf.id IN (1) ) f JOIN users mf ON mf.id = f.friend_id LEFT JOIN collection c ON c.friend_id = mf.id AND c.user_id = f.user_id LEFT JOIN bill_person bp ON bp.user_id=f.user_id AND c.friend_id = mf.id GROUP BY mf.id ORDER BY mf.id </code></pre> <p>and my <strong>output</strong> of this query is</p> <pre class="lang-none prettyprint-override"><code>id NAME AMOUNT NO_COMMON_BILLS 2 XYZ 100 2 3 PQR 300 2 </code></pre> <p>but I want this result:</p> <pre class="lang-none prettyprint-override"><code>id NAME AMOUNT NO_COMMON_BILLS 2 XYZ 100 2 3 PQR 300 1 </code></pre> <p>I am getting wrong output at <strong>NO_COMMON_BILLS</strong>. Other than that, all values are correct.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USdoubleDown
    1. USRaj Jagani
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. COPlease add schema and data here instead of just SQLFiddle link.
      singulars
      1. POMySQL returns incorrect data with use of join
      1. UShims056
    2. CO@hims056 : i have too many table so i have made SQLFiddle to make it easier.
      singulars
      1. POMySQL returns incorrect data with use of join
      1. USRaj Jagani
    3. COBut what if the sqlfiddle site is down?
      singulars
      1. POMySQL returns incorrect data with use of join
      1. UShims056
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload