CODEWITHSUNDEEP
mysqlsqlselectjoin
nirosha rathnayaka
nirosha rathnayaka

Reputation: 198

Join two mysql tables and display data order by date

I have two mysql tables tblSharesRegistry, tblSharesAccount. I want to join both tables and show rows order by date. But it show data incorrectly. Any help please.

1. tblSharesRegistry
    ID  EMPNumber   NoOfShares  Amount  Deposit    Date 
    1   1111         100         10000     1    2012-07-20      
    2   1234         50           5000     1    2012-08-12      
    3   1001         10           1000     1    2012-09-25      
    4   1001         10           1000     1    2012-10-25      
    5   1001         10           1000     1    2012-11-25
    6   1001         -5           -500     0    2012-11-28      

2. tblSharesAccount
    ID  EMPNumber   Balance Interest    Rate    Date    
    1   1111      10000   1000           10 2012-11-29
    2   1234       5000    500           10 2012-11-29
    3   1001       2500    250           10 2012-11-29

And the output should be for the EMPNumber: 1001

Date         Description    #Shares   Amount   Interest   Total
2012-09-25   Shares bought     10      1000       -        1000
2012-10-25   Shares bought     10      1000       -        2000
2012-11-25   Shares bought     10      1000       -        3000
2012-11-28   Shares sold       -5      -500       -        2500
2012-01-29   Interest          25      2500      250       2750

My current code for join the two tables

SELECT tblSharesRegistry.Date, tblSharesRegistry.NoOfShares, tblSharesRegistry.Amount, tblSharesAccount.Interest, tblSharesAccount.Date FROM tblSharesAccount INNER JOIN tblSharesRegistry ON tblSharesAccount.EMPNumber = tblSharesRegistry.EMPNumber WHERE tblSharesRegistry.EMPNumber = '1001' ORDER  BY Date DESC

VB.NET Error: enter image description here

Upvotes: 2

Views: 2389

Answers (2)

Saharsh Shah
Saharsh Shah

Reputation: 29071

Try this: 

SELECT * FROM 
(SELECT sr.Date, IF(sr.NoOfShares > 0, 'Shares bought', 'Shares sold') Description, 
         sr.NoOfShares, sr.Amount, '-' Interest, sr.Amount Total 
FROM tblSharesRegistry sr WHERE sr.EMPNumber = '1001' 
UNION 
SELECT sa.Date, 'Interest' Description, (SELECT SUM(sr.NoOfShares) FROM tblSharesRegistry sr  WHERE sa.EMPNumber = se.EMPNumber) NoOfShares, 
         sa.Balance, sa.Interest, (sa.Balance + sa.Interest) Total 
FROM tblSharesAccount sa WHERE sa.EMPNumber = '1001' ) AS A 
ORDER BY A.date

Check this link for better idea on if else statement MySQL IF ELSE

Upvotes: 1

Sashi Kant
Sashi Kant

Reputation: 13465

Try this ::

SELECT 
tblSharesRegistry.Date, 
tblSharesRegistry.NoOfShares, 
tblSharesRegistry.Amount, 
tblSharesAccount.Interest, 
tblSharesAccount.Date 

FROM tblSharesAccount 
INNER JOIN tblSharesRegistry ON tblSharesAccount.EMPNumber = tblSharesRegistry.EMPNumber 
WHERE tblSharesRegistry.EMPNumber = '1001' ORDER  BY tblSharesRegistry.Date DESC

Upvotes: 0

Related Questions