| Author |
Topic |
|
jamie
Aged Yak Warrior
542 Posts |
 Posted - 2004-04-26 : 06:14:04
|
| hello,I am runnnig a query and incorrect data is shown, duplicate lines are being displayed when I add an extra table.what happens is a client makes a payment(transaction) to a placement I want to show which payments have been made for each placement, but if a client has multiple placements then the transactoin is showing up twice. once for the first placement and once for the second.my query is this :SELECT dbo.ClientDetail.ID, dbo.ClientDetail.Name, dbo.TransactionDetail.TransactionDate, dbo.TransactionDetail.TransactionValue, dbo.Placement.DateFrom, dbo.Placement.DateTo, dbo.TransactionDetail.ID AS tranIDdbo.Placement.ID as placementidFROM dbo.ClientDetail INNER JOIN dbo.TransactionAccount ON dbo.ClientDetail.ID = dbo.TransactionAccount.ClientID INNER JOIN dbo.TransactionDetail ON dbo.TransactionAccount.ID = dbo.TransactionDetail.TransactionAccountID INNER JOIN dbo.Placement ON dbo.ClientDetail.ID = dbo.Placement.ClientID INNER JOIN dbo.TransactionType ON dbo.TransactionDetail.TransactionTypeID = dbo.TransactionType.ID INNER JOIN dbo.TransactionBatch ON dbo.TransactionDetail.BatchID = dbo.TransactionBatch.ID WHERE (dbo.TransactionDetail.TransactionDate BETWEEN dbo.Placement.DateFrom AND dbo.Placement.DateTo)the data is like :ID, Name, TranDate, Value, DateFrom, DateTo, tranID placementid1 Jamie 22/03/2002 50 01/03/2002 01/04/2002 2 11 Jamie 22/03/2002 50 01/01/2000 01/01/2005 2 2 how can I only show the relevent payment for the placement ?hope that all makes sense .thank you or any help. |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-26 : 07:13:48
|
| Your results are two seperate placements?!? What exactly are you after? If you are only after the latest placement, just add a max() to the placementId... |
 |
|
|
TSQLMan
Posting Yak Master
160 Posts |
Posted - 2004-04-26 : 07:40:51
|
| Replace ThisINNER JOIN dbo.Placement ON dbo.ClientDetail.ID = dbo.Placement.ClientIDWith ThisINNER JOIN dbo.Placement ON dbo.ClientDetail.ID = dbo.Placement.ClientIDINNER JOIN (SELECT MAX(Placementid) AS Placementid, ClientID FROM placement GROUP By ClientID) DT ON dbo.Placement.ClientID = dt.clientid AND dbo.Placement.Placementid = dt.placementidAlso add dt.PlacementID to the Select line of the query. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-04-27 : 04:10:35
|
| hello, thanks for that, it all looks correct, but I think theres a major design fault with the db, doing this brings back results but actually I don't think it is correct as there is no way of showing which payments are for which placement.this assumes the payment is for the most recent placement. that actually isn't true. !so I don't think what I want to do is possible.thank you anyway. |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-04-27 : 04:48:51
|
| I don't think this query works correctly,this seems to discard clients with multiple placements.? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-27 : 05:00:28
|
quote:
Originally posted by jamie
I don't think this query works correctly,this seems to discard clients with multiple placements.?
That is what you would expect from a max()... |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-04-27 : 05:24:11
|
| If someone has 2 placements the payment is for 1 of the placements, I want to show the 1 playment against 1 placement. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-27 : 05:45:20
|
| Is there a field in placement or payment which would define which placement a payment is for? |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-04-27 : 05:59:19
|
| unfortunately not.so I think this is impossible isn't it ? |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-27 : 06:10:22
|
| How do they print invoices if there is no link between which placement the person was on and which they've paid for?If there is no link anywhere, then it is impossible, but surely no-one would design a db like this in the first place?!?! |
|
|
|
jamie
Aged Yak Warrior
542 Posts |
Posted - 2004-04-27 : 06:59:14
|
| there is an invoice table, problem is I need to pull data off that doesn't exist in the invoice table. never mind, I am now trying to relink the tables using the invoicenumber, that might work.doubt it though !thanks |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-04-27 : 07:08:30
|
quote:
Originally posted by jamie
there is an invoice table, problem is I need to pull data off that doesn't exist in the invoice table. never mind, I am now trying to relink the tables using the invoicenumber, that might work.doubt it though !thanks
Sounds more likely to work if both tables are populated with the correct invoice numbers... |
|
|
|
|
incorrect data shown when adding table