table alias may help:
select
tbl_cadidate.candidate_name,
count(fist.first_candidate_id) as first,
count(second.first_candidate_id) as second,
count(third.first_candidate_id) as second
from
tbl_cadidate,
tbl_vote as first,
tbl_vote as second,
tbl_vote as third
where
tbl_cadidate.candidate_id = first.first_candidate_id and
tbl_cadidate.candidate_id = second.second_candidate_id and
tbl_cadidate.candidate_id = third.third_candidate_id
+----------------------------------------------------+
| VeriPay mPOS � Anywhere, Anytime |
| Puts money in your bank while you�re on the road |
| Secure Credit Card Transactions via SMS messaging |
| From Xilo Online www.xilo.com/mpos |
+----------------------------------------------------+
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED] Behalf Of
> James Silva
> Sent: Monday, 21 July 2003 4:09 PM
> To: CFAussie Mailing List
> Subject: [cfaussie] SQL fun
>
>
> sorry for the OT post.. but this is my last resort.
>
> I've got 2 tables structured as follows:
>
> tbl_cadidate
> ------------------
> candidate_id
> candidate_name
>
>
> tbl_vote
> ------------------
> vote_id
> first_candidate_id
> second_candidate_id
> third_candidate_id
>
>
> Lets say tbl_candidate contains 3 records. The tbl_vote table
> has several
> hundred.
>
> I need a query that will return a table like so:
>
> ---------------------------------------------------------
> candidate_name | first_count | second_count | third_count
> ---------------------------------------------------------
> moe | 150 | 100 | 50
> larry | 100 | 50 | 150
> curly | 50 | 150 | 50
> ---------------------------------------------------------
>
> e.g: first_count represents the COUNT() of each candidate in the
> vote.first_candidate_id field.
>
> I know the db structure has not been normalised (i did not
> design it, nor can i
> change it now).
>
> Just want to know if this is possible with a single query. If
> not, i'll need to
> nest a query inside the output of another query (i hate doing
> this as it hammers
> the db).
>
> oh, and the kicker?... needs to work using MS Access :(
>
> TIA,
> _______________________
> James Silva
> Web Production
> Gruden Pty Ltd
>
> Tel: +61 02 9956 6388
> Fax: +61 02 9956 8433
> Email: [EMAIL PROTECTED]
> Web: www.gruden.com
> _______________________
>
>
> ---
> You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
> To unsubscribe send a blank email to
> [EMAIL PROTECTED]
>
> MX Downunder AsiaPac DevCon - http://mxdu.com/
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MX Downunder AsiaPac DevCon - http://mxdu.com/
