By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,327 Members | 1,789 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,327 IT Pros & Developers. It's quick & easy.

Needs help in query

P: n/a
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

Nov 27 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
I don't understand why you would ever have a negative value such as -3:
In this scenario, you assigned a prize without the person having
reached the required number of points.

co*********@gmail.com wrote:
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
Nov 27 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.