Any way I can do this? Should I do this?

[Jeff Uchtman]

I'd like to combine (if possible these two statements drawing from a total
of 3 tables.

sql = "SELECT p.Sub_ID, p.Cat_ID, p.Sub_Name, COUNT(c.Sub_ID) as ct FROM
Subs p LEFT JOIN Links c ON p.Sub_Id = c.Sub_Id GROUP BY p.Sub_ID, p.Cat_ID,
p.Sub_Name ORDER BY p.Sub_Name"

sql = "select * from Subs inner join Cats on Cats.Cat_ID=Sub s.Cat_ID ORDER
BY Sub_Name"

Is it possible? Am I totaly screwed up for even trying? The goal is to
count the relations ships in the Subs and Links table based on Sub_ID, to
display needed data from the Subs table, and to display the Cat_name table
from the Cats table in relationship to the Cat_ID in both the Cats and Subs
Table.

Thanks
Jeff

[Bob Barrows]

Jeff Uchtman wrote:

I'd like to combine (if possible these two statements drawing from a
total of 3 tables.

sql = "SELECT p.Sub_ID, p.Cat_ID, p.Sub_Name, COUNT(c.Sub_ID) as ct
FROM Subs p LEFT JOIN Links c ON p.Sub_Id = c.Sub_Id GROUP BY
p.Sub_ID, p.Cat_ID, p.Sub_Name ORDER BY p.Sub_Name"

sql = "select * from Subs inner join Cats on Cats.Cat_ID=Sub s.Cat_ID
ORDER BY Sub_Name"

Is it possible? Am I totaly screwed up for even trying? The goal is
to count the relations ships in the Subs and Links table based on
Sub_ID, to display needed data from the Subs table, and to display
the Cat_name table from the Cats table in relationship to the Cat_ID
in both the Cats and Subs Table.

It's always easiest to use an example to get your idea across. Show us some
sample data in tabular format, and then show us the results you wish to
achieve, also using a tabular format (queries return data in tabular form,
so that really helps us understand what you want).

And don't forget to tell us what kind of database you are using, as well as
its version.

Bob Barrows

[Jeff Uchtman]

My bad, it was late and I was frustrated.

The DB is Access 2000. 3 tables named Cats, Subs, and Links. Tables in
Cats are Cat_Id, Cat_Name, and Cat_Date. Tables in Subs are Sub_Id, Cat_ID
(relationship to table Cats), Sub_Name, and Sub_Date. Tables in Links are
Link_ID, Cat_Id, Sub_Id (relationship to table Subs), Link_Name, Link_Disc,
Link_Url, and Link_Date. The main info I need to draw from the subs table
needing the Sub_Id, Cat_Id and Sub_Name. I need the relationship in Cats
table on Cat_Id pulling the Cat_Name, and I need the number of relationships
between the Subs table and Links table on the Sub_Id in each of those
tables. Hope this makes sense.

Thanks
Jeff
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

Jeff Uchtman wrote:

I'd like to combine (if possible these two statements drawing from a
total of 3 tables.

sql = "SELECT p.Sub_ID, p.Cat_ID, p.Sub_Name, COUNT(c.Sub_ID) as ct
FROM Subs p LEFT JOIN Links c ON p.Sub_Id = c.Sub_Id GROUP BY
p.Sub_ID, p.Cat_ID, p.Sub_Name ORDER BY p.Sub_Name"

sql = "select * from Subs inner join Cats on Cats.Cat_ID=Sub s.Cat_ID
ORDER BY Sub_Name"

Is it possible? Am I totaly screwed up for even trying? The goal is
to count the relations ships in the Subs and Links table based on
Sub_ID, to display needed data from the Subs table, and to display
the Cat_name table from the Cats table in relationship to the Cat_ID
in both the Cats and Subs Table.
It's always easiest to use an example to get your idea across. Show us

some sample data in tabular format, and then show us the results you wish to
achieve, also using a tabular format (queries return data in tabular form,
so that really helps us understand what you want).

And don't forget to tell us what kind of database you are using, as well as its version.

Bob Barrows

[Bob Barrows]

I'm still having trouble understanding. Please provide sample data in
tabular format:

table name
col1 col2 col3 ...
A 52 23 ...
B 78 62 ...
And show the results you wish to obtain from that sample data in the same
format:

results
col1 col2 col3 ...
row1
row2

Thx,
Bob Barrows

Jeff Uchtman wrote:

My bad, it was late and I was frustrated.

The DB is Access 2000. 3 tables named Cats, Subs, and Links. Tables
in Cats are Cat_Id, Cat_Name, and Cat_Date. Tables in Subs are
Sub_Id, Cat_ID (relationship to table Cats), Sub_Name, and Sub_Date.
Tables in Links are Link_ID, Cat_Id, Sub_Id (relationship to table
Subs), Link_Name, Link_Disc, Link_Url, and Link_Date. The main info
I need to draw from the subs table needing the Sub_Id, Cat_Id and
Sub_Name. I need the relationship in Cats table on Cat_Id pulling
the Cat_Name, and I need the number of relationships between the Subs
table and Links table on the Sub_Id in each of those tables. Hope
this makes sense.

Thanks
Jeff
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

Jeff Uchtman wrote:

I'd like to combine (if possible these two statements drawing from a
total of 3 tables.

sql = "SELECT p.Sub_ID, p.Cat_ID, p.Sub_Name, COUNT(c.Sub_ID) as ct
FROM Subs p LEFT JOIN Links c ON p.Sub_Id = c.Sub_Id GROUP BY
p.Sub_ID, p.Cat_ID, p.Sub_Name ORDER BY p.Sub_Name"

sql = "select * from Subs inner join Cats on Cats.Cat_ID=Sub s.Cat_ID
ORDER BY Sub_Name"

Is it possible? Am I totaly screwed up for even trying? The goal
is to count the relations ships in the Subs and Links table based on
Sub_ID, to display needed data from the Subs table, and to display
the Cat_name table from the Cats table in relationship to the Cat_ID
in both the Cats and Subs Table.

It's always easiest to use an example to get your idea across. Show
us some sample data in tabular format, and then show us the results
you wish to achieve, also using a tabular format (queries return
data in tabular form, so that really helps us understand what you
want).

And don't forget to tell us what kind of database you are using, as
well as its version.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Uchtman]

OK, here go's;

Cats
Cat_ID Cat_Name
1 Internet
2 PC
3 Sports

Subs
Sub_ID Cat_ID Sub_Name
2 1 Software
3 2 Microsoft
3 3 Football

Links
Link_ID Sub_ID Cat_ID Link_Desc
1 2 1 Netscape
2 3 2 Microsoft
3 3 3 NCAA

Table relationship between Cats Cat_ID and Subs Cat_ID. Table relationship
between Subs Sub_ID and Links Sub_ID.

Needing from Subs draw Sub_ID, Cat_ID, Sub_Name
Needing from Cats draw Cat_Name
Needing from Links draw count number of Link_ID that fall under each Sub_ID

Results

Cat_ID, Cat_Name, Sub_ID, Sub_Name, count of links ID under each Sub_ID.

Hope this makes sense.

Thanks
Jeff
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:ul******** *****@TK2MSFTNG P10.phx.gbl...

I'm still having trouble understanding. Please provide sample data in
tabular format:

table name
col1 col2 col3 ...
A 52 23 ...
B 78 62 ...
And show the results you wish to obtain from that sample data in the same
format:

results
col1 col2 col3 ...
row1
row2

Thx,
Bob Barrows

Jeff Uchtman wrote:

My bad, it was late and I was frustrated.

The DB is Access 2000. 3 tables named Cats, Subs, and Links. Tables
in Cats are Cat_Id, Cat_Name, and Cat_Date. Tables in Subs are
Sub_Id, Cat_ID (relationship to table Cats), Sub_Name, and Sub_Date.
Tables in Links are Link_ID, Cat_Id, Sub_Id (relationship to table
Subs), Link_Name, Link_Disc, Link_Url, and Link_Date. The main info
I need to draw from the subs table needing the Sub_Id, Cat_Id and
Sub_Name. I need the relationship in Cats table on Cat_Id pulling
the Cat_Name, and I need the number of relationships between the Subs
table and Links table on the Sub_Id in each of those tables. Hope
this makes sense.

Thanks
Jeff
"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:%2******** ********@TK2MSF TNGP09.phx.gbl. ..

Jeff Uchtman wrote:
I'd like to combine (if possible these two statements drawing from a
total of 3 tables.

sql = "SELECT p.Sub_ID, p.Cat_ID, p.Sub_Name, COUNT(c.Sub_ID) as ct
FROM Subs p LEFT JOIN Links c ON p.Sub_Id = c.Sub_Id GROUP BY
p.Sub_ID, p.Cat_ID, p.Sub_Name ORDER BY p.Sub_Name"

sql = "select * from Subs inner join Cats on Cats.Cat_ID=Sub s.Cat_ID
ORDER BY Sub_Name"

Is it possible? Am I totaly screwed up for even trying? The goal
is to count the relations ships in the Subs and Links table based on
Sub_ID, to display needed data from the Subs table, and to display
the Cat_name table from the Cats table in relationship to the Cat_ID
in both the Cats and Subs Table.

It's always easiest to use an example to get your idea across. Show
us some sample data in tabular format, and then show us the results
you wish to achieve, also using a tabular format (queries return
data in tabular form, so that really helps us understand what you
want).

And don't forget to tell us what kind of database you are using, as
well as its version.

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Uchtman]

Typo corrected below~

Cats
Cat_ID Cat_Name
1 Internet
2 PC
3 Sports

Subs
Sub_ID Cat_ID Sub_Name
2 1 Software
3 2 Microsoft
4 3 Football

Links
Link_ID Sub_ID Cat_ID Link_Desc
1 2 1 Netscape
2 3 2 Microsoft
3 4 3 NCAA

Table relationship between Cats Cat_ID and Subs Cat_ID. Table relationship
between Subs Sub_ID and Links Sub_ID.

Needing from Subs draw Sub_ID, Cat_ID, Sub_Name
Needing from Cats draw Cat_Name
Needing from Links draw count number of Link_ID that fall under each Sub_ID

Results

Cat_ID, Cat_Name, Sub_ID, Sub_Name, count of links ID under each Sub_ID.

Hope this makes sense.

Thanks
Jeff

[Bob Barrows]

Jeff Uchtman wrote:

OK, here go's;

Cats
Cat_ID Cat_Name
1 Internet
2 PC
3 Sports

Subs
Sub_ID Cat_ID Sub_Name
2 1 Software
3 2 Microsoft
3 3 Football

Links
Link_ID Sub_ID Cat_ID Link_Desc
1 2 1 Netscape
2 3 2 Microsoft
3 3 3 NCAA

Table relationship between Cats Cat_ID and Subs Cat_ID. Table
relationship between Subs Sub_ID and Links Sub_ID.

Needing from Subs draw Sub_ID, Cat_ID, Sub_Name
Needing from Cats draw Cat_Name
Needing from Links draw count number of Link_ID that fall under each
Sub_ID

Results

Cat_ID, Cat_Name, Sub_ID, Sub_Name, count of links ID under each
Sub_ID.

So, using your sample data, the results you want would be as follows?

CatID Cat_Name Sub_ID Sub_Name LinksCount
1 Internet 2 Software 1
2 PC 3 Microsoft 1
3 Sports 3 Football 1

Select c.CatID,c.Cat_N ame, s.Sub__ID, count(*) LinksCount
FROM Cats c Inner Join Subs s ON c.Cat_ID = s.Cat_ID
Inner Join Links l ON s.SubID = l.Sub_ID AND s.Cat_ID = l.Cat_ID
Group By c.CatID, s.Sub__ID

Something seems strange with this design, especially that Subs table: why
does the Sub_Name depend on both the Sub_ID AND the Cat_ID?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Uchtman]

Subs name should not be dependent on anything (I think)
Relationship on Cats table and Subs table is between the Cats_ID.

Jeff

"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:uy******** ******@tk2msftn gp13.phx.gbl...
Jeff Uchtman wrote:

OK, here go's;

Cats
Cat_ID Cat_Name
1 Internet
2 PC
3 Sports

Subs
Sub_ID Cat_ID Sub_Name
2 1 Software
3 2 Microsoft
3 3 Football

Links
Link_ID Sub_ID Cat_ID Link_Desc
1 2 1 Netscape
2 3 2 Microsoft
3 3 3 NCAA

Table relationship between Cats Cat_ID and Subs Cat_ID. Table
relationship between Subs Sub_ID and Links Sub_ID.

Needing from Subs draw Sub_ID, Cat_ID, Sub_Name
Needing from Cats draw Cat_Name
Needing from Links draw count number of Link_ID that fall under each
Sub_ID

Results

Cat_ID, Cat_Name, Sub_ID, Sub_Name, count of links ID under each
Sub_ID.

So, using your sample data, the results you want would be as follows?

CatID Cat_Name Sub_ID Sub_Name LinksCount
1 Internet 2 Software 1
2 PC 3 Microsoft 1
3 Sports 3 Football 1

Select c.CatID,c.Cat_N ame, s.Sub__ID, count(*) LinksCount
FROM Cats c Inner Join Subs s ON c.Cat_ID = s.Cat_ID
Inner Join Links l ON s.SubID = l.Sub_ID AND s.Cat_ID = l.Cat_ID
Group By c.CatID, s.Sub__ID

Something seems strange with this design, especially that Subs table: why
does the Sub_Name depend on both the Sub_ID AND the Cat_ID?
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Bob Barrows]

Jeff Uchtman wrote:

Subs name should not be dependent on anything (I think)
Relationship on Cats table and Subs table is between the Cats_ID.

So does my suggested query give you what you want?

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

[Jeff Uchtman]

Close, till getting this error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'count(*) LinksCount'.

Jeff

"Bob Barrows" <re******@NOyah oo.SPAMcom> wrote in message
news:u6******** *****@TK2MSFTNG P09.phx.gbl...
Jeff Uchtman wrote:

Subs name should not be dependent on anything (I think)
Relationship on Cats table and Subs table is between the Cats_ID.

So does my suggested query give you what you want?

Bob Barrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.