MULTIPLE TABLE QUERY!

I am trying to retrieve data based on data in three tables.
Not quite sure how to proceed.
//Logic
// retreve cost item data, category_name assocociated with
cost_item.category_id in category table where category_id is associated with
category_group.category_group_id = 3
SELECT
cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM
cost_item , category, category_group " &_
WHERE
cost_item.cost_item_category = category.category_id " &_
Above is my feeble attempt.
If anyone can help me figure this out..it would be appreciated.
AJAnthony,
It's difficult to know how to help you write your query without DDL and
sample data. Please see this article:
http://www.aspfaq.com/etiquette.asp?id=5006
I am also curious as to why your column names appear to be changing from
table to table? It's really best to keep things consistent. A category_id
in one table is still a category_id in another table -- it's confusing to
re-name things in different tables.
"Anthony Judd" <adam.jknight@.optusnet.com.au> wrote in message
news:%23Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl...
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
> cost_item.category_id in category table where category_id is associated
with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
> WHERE
> cost_item.cost_item_category = category.category_id " &_
> Above is my feeble attempt.
> If anyone can help me figure this out..it would be appreciated.
> AJ
>|||In message <#Vj$ZieqEHA.1988@.TK2MSFTNGP09.phx.gbl>, Anthony Judd
<adam.jknight@.optusnet.com.au> writes
> I am trying to retrieve data based on data in three tables.
> Not quite sure how to proceed.
> //Logic
> // retreve cost item data, category_name assocociated with
>cost_item.category_id in category table where category_id is associated with
> category_group.category_group_id = 3
> SELECT
> cost_item.cost_item_name,
> cost_item.cost_item_amount,
> category.category_name,
> cost_item.cost_item_id,
> category_group.category_group_name
> FROM
> cost_item , category, category_group " &_
>WHERE
> cost_item.cost_item_category = category.category_id " &_
>Above is my feeble attempt.
>If anyone can help me figure this out..it would be appreciated.
>AJ
>
For starters, you are using the OLD way of doing things and NOT ANSI
compliant code. This makes things a lot harder to read and therefore
find errors. The obvious error with the above code is you are INNER
JOINing three tables BUT only making a reference to two of them in your
WHERE statement.
The ANSI way of doing things would have made this mistake clearer. As
you have not published the DDL for your tables you will have to check
the 2nd INNER JOIN is relating the correct fields.
SELECT cost_item.cost_item_name,
cost_item.cost_item_amount,
category.category_name,
cost_item.cost_item_id,
category_group.category_group_name
FROM cost_item
INNER JOIN category ON cost_item.cost_item_category =category.category_id
INNER JOIN category_group ON cost_item.cost_item_category =category_group.category_id
--
Andrew D. Newbould E-Mail: newsgroups@.NOSPAMzadsoft.com
ZAD Software Systems Web : www.zadsoft.com