If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Left Join
Hi: There are three tables in my database. Table Main I use to give stars and
table Star Redemption is used to give prize. There is another table name Prize which is linked with table Star Redemption. In table Prize it shows how many points employee will get on the basis of the Prize. My question is how do i write a query so that I subtract total number of points from table Main to table Star Redemption. For example Acito Lisa has total stars in table main is 347 and Acito Lisa has total stars from table Star Redemption is 325. So i need the difference which will be (347-350) -3. In Table Star Redemption, the stars are define in table Prize. Also I need a left join in table Main so that if there is no record of any employee in table Star Redemption it still shows record from table main with total stars. Thanks. Main ---- ID Date Name #_Stars 1725 11/22/2005 Acito Lisa 1 2379 6/30/2006 Acito Lisa 6 2376 5/30/2006 Acito Lisa 25 2326 4/30/2006 Acito Lisa 25 2344 5/30/2006 Acito Lisa 10 1583 10/17/2005 Acito Lisa 1 2346 5/30/2006 Acito Lisa 5 2339 5/31/2006 Acito Lisa 2 1956 1/31/2006 Acito Lisa 5 1210 8/29/2005 Acito Lisa 5 2145 3/31/2006 Acito Lisa 10 1907 1/31/2006 Acito Lisa 10 2742 9/30/2006 Acito Lisa 4 1650 10/31/2005 Acito Lisa 5 1014 7/27/2005 Acito Lisa 10 2224 4/28/2006 Acito Lisa 5 2227 4/30/2006 Acito Lisa 10 962 6/30/2005 Acito Lisa 5 1619 10/26/2005 Acito Lisa 2 2253 4/30/2006 Acito Lisa 5 1034 7/27/2005 Acito Lisa 10 953 6/30/2005 Acito Lisa 3 1041 7/29/2005 Acito Lisa 1 1133 8/25/2005 Acito Lisa 2 929 6/30/2005 Acito Lisa 5 856 6/9/2005 Acito Lisa 1 908 6/29/2005 Acito Lisa 2 1599 10/21/2005 Acito Lisa 1 1849 12/30/2005 Acito Lisa 5 2178 3/31/2006 Acito Lisa 5 1123 8/25/2005 Acito Lisa 5 2771 9/30/2006 Acito Lisa 5 2304 5/31/2006 Acito Lisa 4 822 5/30/2005 Acito Lisa 5 2842 10/19/2006 Acito Lisa 1 1588 10/19/2005 Acito Lisa 10 940 6/30/2005 Acito Lisa 6 279 11/30/2004 Acito Lisa 2 1257 9/2/2005 Acito Lisa 3 1552 10/5/2005 Acito Lisa 5 390 1/25/2005 Acito Lisa 5 1818 12/29/2005 Acito Lisa 2 2816 9/30/2006 Acito Lisa 10 1522 9/23/2005 Acito Lisa 10 1251 8/31/2005 Acito Lisa 5 1332 9/2/2005 Acito Lisa 3 1524 10/4/2005 Acito Lisa 1 1492 9/30/2005 Acito Lisa 2 1490 9/30/2005 Acito Lisa 5 2614 8/31/2006 Acito Lisa 5 2701 9/29/2006 Acito Lisa 2 3039 11/22/2006 Acito Lisa 10 2692 8/30/2006 Acito Lisa 5 36 8/6/2004 Acito Lisa 2 1744 11/30/2005 Acito Lisa 2 2472 6/30/2006 Acito Lisa 25 1718 11/17/2005 Acito Lisa 2 2415 6/30/2006 Acito Lisa 5 1759 11/30/2005 Acito Lisa 5 1793 12/8/2005 Acito Lisa 3 2477 7/28/2006 Acito Lisa 6 1972 2/10/2006 Alfarone, Valerie 10 1984 2/13/2006 Alfarone, Valerie 5 1835 12/30/2005 Alfarone, Valerie 3 1644 11/4/2005 Alfarone, Valerie 2 1749 12/5/2005 Alfarone, Valerie 2 2107 3/17/2006 Alfarone, Valerie 2 2058 3/2/2006 Alfarone, Valerie 5 2101 3/16/2006 Alfarone, Valerie 5 2758 9/30/2006 Attanasio, Jennifer 4 2743 9/30/2006 Attanasio, Jennifer 6 2942 10/30/2006 Attanasio, Jennifer 4 2859 10/25/2006 Attanasio, Jennifer 5 Star Redemption --------------- ID Date Name Prize 308 11/27/2006 Acito Lisa Come in Late 249 11/24/2006 Acito Lisa $25 Amex Gift Check 191 8/10/2006 Acito Lisa $100 Amex Gift Check 192 8/10/2006 Acito Lisa $50 Amex Gift Check 193 8/10/2006 Acito Lisa $25 Amex Gift Check 147 12/28/2005 Acito Lisa $25 Amex Gift Check 98 10/12/2005 Acito Lisa $100 Amex Gift Check 144 12/28/2005 Barnes, Sylvia $25 Amex Gift Check 250 11/24/2006 Barnes, Sylvia $25 Amex Gift Check 66 7/20/2005 Barnes, Sylvia $50 Amex Gift Check 239 9/27/2006 Barnes, Sylvia $50 Amex Gift Check 86 10/12/2005 Barnes, Sylvia $25 Amex Gift Check Prize ---- ID Prize # Stars Dollars 1 Come in Late 25 $0.00 2 Extended Lunch 25 $0.00 3 Leave 1 hr Early 25 $0.00 4 $50 Amex Gift Check 50 $50.00 5 $100 Amex Gift Check 100 $100.00 6 $25 Amex Gift Check 25 $25.00 7 Dress Down Week 25 $0.00 8 $75 Amex Gift Check 75 $75.00 9 $125 Amex Gift Check 125 $125.00 10 $150 Amex Gift Check 150 $150.00 11 $175 Amex Gift Check 175 $175.00 12 $200 Amex Gift Check 200 $200.00 -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200611/1 |
Thread Tools | |
Display Modes | |
|
|