[SOLVED] Mysql Using Foreign key to produce results of the joined tables

[jkkenzie]

Hi!,

  I have two tables, one with persons details(members table) and the other with the person's transactions (Shares table). An identifaction number (Primary key) in members table is used in Shares table to hold the transactions of an individual record/members from members table.

 

What i wanted to do is SUM  the total_shares in shares table for each members in members table As i display the results.

 

This involves SELECTING DISTINCT identifaction in shares table, then using the identification one by one to get member's details in members table and getting all transactions (as i SUM) with the same indentification in shares table .

 

This looks simple but when i tried with php Do.....While , it didn't finish the output because the records are in thousands i.e they are very many and thus the page displays few results and gives an error like timeout or server request time exceeded. So i want Mysql to get the results before i display using php.

 

Thanks in advance

[kickstart]

Hi

 

You can just do it in SQL. Something like this:-

 

SELECT PersonId, SUM(total_shares)

FROM PersonDetails

LEFT OUTER JOIN  ShareDetails

ON PersonDetails.id = ShareDetails.id

GROUP BY PersonId

 

All the best

 

Keith

[jkkenzie]

You got the point.

though First, i want to pick All Ids from the Shares table to avoid picking from members table whos Ids dont appear on shares table.

 

Therefore i have  something like :

 

SELECT DISTINCT Id From Shares.

 

Then Use the Ids to pick persons details from members table AND to SUM up the transctions on shares table.

 

I think you SQL was the other way round.

 

I hope this gives you the clear picture.

 

To say it like i have said above looks easy BUT i want a single SQL line of code that does that magic.

 

thanks

[kickstart]

Hi

 

Think you need to do a join against a subselect. Something like this:-

 

SELECT a.PersonId, a.PersonName, b.ShareValue
FROM PersonDetails a
INNER JOIN (SELECT PersonId, SUM(total_shares) AS ShareValue
FROM ShareDetails
GROUP BY PersonId) b

 

All the best

 

Keith

[jkkenzie]

Its Not working.

I think your first SQL code you posted was heading to the right direction except it was doing the opposite.

 

this one is giving me wrong results.

[kickstart]

Hi

 

Ooops . Forgot to paste in the ON clause:-

 

SELECT a.PersonId, a.PersonName, b.ShareValue
FROM PersonDetails a
INNER JOIN (SELECT PersonId, SUM(total_shares) AS ShareValue
FROM ShareDetails
GROUP BY PersonId) b
ON a.PersonId = b.PersonId

 

All the best

 

Keith

[jkkenzie]

It works good, Thank you very much.