| Author |
Topic |
|
man889
Starting Member
25 Posts |
 Posted - 2011-07-28 : 00:10:45
|
I have two tables as following
(Table)OtherFeeType
____________
(Field)OtherFeeTypeId, OtherFeeTypeDes
1, ParkFee
2, RoadFee
3, RegisterFee
4, CheckFee
5, OverWeightFee
(Table)JobItemOtherFee
_________________
(Field)JobItemOtherFeeID, JobItemNo, OtherFeeTypeId, OtherFeePrice
1, 1, 1, 20
2, 1, 2, 30
3, 2, 1, 10
4, 2, 4, 50
(*No duplicate OtherFeeTypeId for the same JobItemNo)
How can I write the query and have the following output?
where JobItemNo = 1
Output
_________________
OtherFeeTypeId, OtherFeeTypeDes, JobItemOtherFeeID, JobItemNo, OtherFeeTypeId, OtherFeePrice
1, ParkFee, 1, 1, 1, 20
2, RoadFee, 2, 1, 2, 30
3, RegisterFee, , , ,
4, CheckFee, , , ,
5, OverWeightFee, , , ,
where JobItemNo = 2
Output
_________________
OtherFeeTypeId, OtherFeeTypeDes, JobItemOtherFeeID, JobItemNo, OtherFeeTypeId, OtherFeePrice
1, ParkFee, 3, 2, 1, 10
2, RoadFee, , , ,
3, RegisterFee, , , ,
4, CheckFee, 4, 2, 4, 50
5, OverWeightFee, , , ,
Thank. |
|
|
man889
Starting Member
25 Posts |
Posted - 2011-07-28 : 02:48:28
|
I solved it by using derived tables
Thank.
SELECT OtherFeeType.OtherFeeTypeId, OtherFeeType.OtherFeeTypeDes, t1.InvNo, t1.JobId, t1.OtherFeePrice, t1.OtherFeeRemark
FROM OtherFeeType LEFT JOIN [select JobItemOtherFee.InvNo, JobItemOtherFee.JobId, JobItemOtherFee.OtherFeeTypeId,
JobItemOtherFee.OtherFeePrice, JobItemOtherFee.OtherFeeRemark
from JobItemOtherFee
WHERE ((JobItemOtherFee.JobId)=16)]. AS t1 ON OtherFeeType.OtherFeeTypeId= t1.OtherFeeTypeId; |
 |
|
|
|
|
|
subquery problem