473,320 Members | 2,027 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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

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=Subs.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
Jul 19 '05 #1
20 2865
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=Subs.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
Jul 19 '05 #2
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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.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=Subs.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

Jul 19 '05 #3
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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.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=Subs.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.
Jul 19 '05 #4
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******@NOyahoo.SPAMcom> wrote in message
news:ul*************@TK2MSFTNGP10.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******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP09.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=Subs.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.

Jul 19 '05 #5
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


Jul 19 '05 #6
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_Name, 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.
Jul 19 '05 #7
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******@NOyahoo.SPAMcom> wrote in message
news:uy**************@tk2msftngp13.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_Name, 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.

Jul 19 '05 #8
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.
Jul 19 '05 #9
Close, till getting this error:
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression 'count(*) LinksCount'.

Jeff

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:u6*************@TK2MSFTNGP09.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.

Jul 19 '05 #10
You should be testing this query in Access, using the query builder (switch
to SQL View), before you even think of trying to execute it from asp. You'll
probably get a better error message.
As a guess, either of the following changes should help:

Try this first:
count(*) As LinksCount

then, if that still causes an error:
count(l.*) As LinksCount

FWIW, you should be using the native Jet OLEDB provider in your asp
connection string, instead of the obsolete ODBC driver. See
www.connectionstrings.com for an example.

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

Jeff

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:u6*************@TK2MSFTNGP09.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.
Jul 19 '05 #11
Thanks Bob. I guess I don't have enough knowledge on Access or ASP to do
this. I do get the same error in the page as query in Access. Tried your
suggestions and now get [Microsoft][ODBC Microsoft Access Driver] Extra ) in
query expression 'count(1*) LinksCount'. Will look at your suggestion on
the connection string.

Jeff
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:e5**************@TK2MSFTNGP10.phx.gbl...
You should be testing this query in Access, using the query builder (switch
to SQL View), before you even think of trying to execute it from asp. You'll
probably get a better error message.
As a guess, either of the following changes should help:

Try this first:
count(*) As LinksCount

then, if that still causes an error:
count(l.*) As LinksCount

FWIW, you should be using the native Jet OLEDB provider in your asp
connection string, instead of the obsolete ODBC driver. See
www.connectionstrings.com for an example.

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

Jeff

"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:u6*************@TK2MSFTNGP09.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.

Jul 19 '05 #12
I forgot: Access (Jet) is very picky about multiple joins. there has to be
parentheses around each subjoin. The correct query looks like this:

Select c.Cat_ID,First(c.Cat_Name) AS [Cat_Name], s.Sub_ID,
count(*) AS LinkCount
FROM (Cats c INNER JOIN Subs s ON c.Cat_ID = s.Cat_ID)
INNER JOIN Links l ON
s.Cat_ID = l.Cat_ID AND s.Sub_ID = l.Sub_ID
Group By c.Cat_ID, s.Sub_ID

The best way to get these groupings right is to use the Access Query Builder
in Design View and use the GUI to create the joins. Switch to SQL View to
see the corresponding SQL statement.

HTH,
Bob Barrows
Jeff Uchtman wrote:
Thanks Bob. I guess I don't have enough knowledge on Access or ASP
to do this. I do get the same error in the page as query in Access.
Tried your suggestions and now get [Microsoft][ODBC Microsoft Access
Driver] Extra ) in query expression 'count(1*) LinksCount'. Will
look at your suggestion on the connection string.


--
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.
Jul 19 '05 #13
YAHOOOOOOOO That is EXACTLY what I want. I was just doing the same (like I
knew what I was doing) in query analyzer. Thanks your the asp session.
Where do I send the tuition check.

Jeff
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:u1**************@tk2msftngp13.phx.gbl...
I forgot: Access (Jet) is very picky about multiple joins. there has to be
parentheses around each subjoin. The correct query looks like this:

Select c.Cat_ID,First(c.Cat_Name) AS [Cat_Name], s.Sub_ID,
count(*) AS LinkCount
FROM (Cats c INNER JOIN Subs s ON c.Cat_ID = s.Cat_ID)
INNER JOIN Links l ON
s.Cat_ID = l.Cat_ID AND s.Sub_ID = l.Sub_ID
Group By c.Cat_ID, s.Sub_ID

The best way to get these groupings right is to use the Access Query Builder
in Design View and use the GUI to create the joins. Switch to SQL View to
see the corresponding SQL statement.

HTH,
Bob Barrows
Jeff Uchtman wrote:
Thanks Bob. I guess I don't have enough knowledge on Access or ASP
to do this. I do get the same error in the page as query in Access.
Tried your suggestions and now get [Microsoft][ODBC Microsoft Access
Driver] Extra ) in query expression 'count(1*) LinksCount'. Will
look at your suggestion on the connection string.


--
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.

Jul 19 '05 #14
OK Bob, you got me this far. I made a few changes in the Access query and
added the Sub_Name. Can I get this to display all fields even if this is no
present relationship, i.e all the sub_names and Id's? Her is what I
changed;

SELECT c.Cat_ID, First(c.Cat_Name) AS Cat_Name, s.Sub_ID, s.Sub_Name,
count(*) AS ct
FROM (Cats AS c INNER JOIN Subs AS s ON c.Cat_ID=s.Cat_ID) INNER JOIN Links
AS l ON (s.Sub_ID=l.Sub_ID) AND (s.Cat_ID=l.Cat_ID)
GROUP BY c.Cat_ID, s.Sub_ID, s.Sub_Name;

Thanks again
Jeff
"Jeff Uchtman" <uc*****@megavision.com> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
YAHOOOOOOOO That is EXACTLY what I want. I was just doing the same (like I
knew what I was doing) in query analyzer. Thanks your the asp session.
Where do I send the tuition check.

Jeff
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:u1**************@tk2msftngp13.phx.gbl...
I forgot: Access (Jet) is very picky about multiple joins. there has to be
parentheses around each subjoin. The correct query looks like this:

Select c.Cat_ID,First(c.Cat_Name) AS [Cat_Name], s.Sub_ID,
count(*) AS LinkCount
FROM (Cats c INNER JOIN Subs s ON c.Cat_ID = s.Cat_ID)
INNER JOIN Links l ON
s.Cat_ID = l.Cat_ID AND s.Sub_ID = l.Sub_ID
Group By c.Cat_ID, s.Sub_ID

The best way to get these groupings right is to use the Access Query Builder
in Design View and use the GUI to create the joins. Switch to SQL View to
see the corresponding SQL statement.

HTH,
Bob Barrows
Jeff Uchtman wrote:
Thanks Bob. I guess I don't have enough knowledge on Access or ASP
to do this. I do get the same error in the page as query in Access.
Tried your suggestions and now get [Microsoft][ODBC Microsoft Access
Driver] Extra ) in query expression 'count(1*) LinksCount'. Will
look at your suggestion on the connection string.


--
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.


Jul 19 '05 #15
I'm confused. What does this query show you? What do you want it to show
you? Tabular format please.

Bob Barrows

Jeff Uchtman wrote:
OK Bob, you got me this far. I made a few changes in the Access
query and added the Sub_Name. Can I get this to display all fields
even if this is no present relationship, i.e all the sub_names and
Id's? Her is what I changed;

SELECT c.Cat_ID, First(c.Cat_Name) AS Cat_Name, s.Sub_ID, s.Sub_Name,
count(*) AS ct
FROM (Cats AS c INNER JOIN Subs AS s ON c.Cat_ID=s.Cat_ID) INNER JOIN
Links AS l ON (s.Sub_ID=l.Sub_ID) AND (s.Cat_ID=l.Cat_ID)
GROUP BY c.Cat_ID, s.Sub_ID, s.Sub_Name;


--
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.
Jul 19 '05 #16
It show only the subs that have a related link below them. If there is no
relating link they do not show up.
Jeff
"Bob Barrows" <re******@NOyahoo.SPAMcom> wrote in message
news:uh**************@TK2MSFTNGP09.phx.gbl...
I'm confused. What does this query show you? What do you want it to show
you? Tabular format please.

Bob Barrows

Jeff Uchtman wrote:
OK Bob, you got me this far. I made a few changes in the Access
query and added the Sub_Name. Can I get this to display all fields
even if this is no present relationship, i.e all the sub_names and
Id's? Her is what I changed;

SELECT c.Cat_ID, First(c.Cat_Name) AS Cat_Name, s.Sub_ID, s.Sub_Name,
count(*) AS ct
FROM (Cats AS c INNER JOIN Subs AS s ON c.Cat_ID=s.Cat_ID) INNER JOIN
Links AS l ON (s.Sub_ID=l.Sub_ID) AND (s.Cat_ID=l.Cat_ID)
GROUP BY c.Cat_ID, s.Sub_ID, s.Sub_Name;


--
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.


Jul 19 '05 #17
Here is what I am seeing: I'd like to see all the SUB_ID and SUB_NAME in
this query with the assocaiation to the Cat_ID and CAT_NAME giving me the ct
for links even if 0.

Jeff
Description:
SQL Command:

SELECT
c.CAT_ID,
FIRST(c.CAT_NAME) AS
Cat_Name,
s.SUB_ID,
s.SUB_NAME,
COUNT(*) AS ct
FROM
(Cats c

INNER JOIN (Subs s
INNER JOIN Links l ON
s.CAT_ID = l.CAT_ID AND

s.SUB_ID = l.SUB_ID) ON
c.CAT_ID = s.CAT_ID)
GROUP BY
c.CAT_ID,

c.CAT_NAME,
s.SUB_ID,
s.SUB_NAME
QUERY: TEST \ New Query COUNT: 6 record(s)

CAT_ID Cat_Name SUB_ID SUB_NAME ct
1 Arts & Humanities 1 Literature 4
1 Arts & Humanities 2 Photography 6
2 Business & Economy 4 Shopping 1
4 Education 7 College & University 1
4 Education 8 K-12 1
8 News & Media 16 TV 1
10 Reference 20 Libraries 1

Jul 19 '05 #18
OK, very close. Getting count of 1 on relationship even if nothing is here.
Other counts are correct. The only problem is the count of 1 even if not
there. Here is what I have;

SELECT
c.CAT_ID,
FIRST(c.CAT_NAME) AS Cat_Name,
s.SUB_ID,
s.SUB_NAME,
COUNT(*) AS ct
FROM
(Cats c
INNER JOIN (Subs s
LEFT JOIN Links l ON
s.SUB_ID = l.SUB_ID AND
s.CAT_ID = l.CAT_ID) ON
c.CAT_ID = s.CAT_ID)
GROUP BY
c.CAT_ID,
c.CAT_NAME,
s.SUB_ID,
s.SUB_NAME
ORDER BY
s.SUB_NAME

"Jeff Uchtman" <uc*****@megavision.com> wrote in message
news:uc**************@TK2MSFTNGP10.phx.gbl...
Here is what I am seeing: I'd like to see all the SUB_ID and SUB_NAME in
this query with the assocaiation to the Cat_ID and CAT_NAME giving me the ct for links even if 0.

Jeff
Description:
SQL Command:

SELECT
c.CAT_ID,
FIRST(c.CAT_NAME) AS
Cat_Name,
s.SUB_ID,
s.SUB_NAME,
COUNT(*) AS ct
FROM
(Cats c

INNER JOIN (Subs s
INNER JOIN Links l ON
s.CAT_ID = l.CAT_ID AND

s.SUB_ID = l.SUB_ID) ON
c.CAT_ID = s.CAT_ID)
GROUP BY
c.CAT_ID,

c.CAT_NAME,
s.SUB_ID,
s.SUB_NAME
QUERY: TEST \ New Query COUNT: 6 record(s)

CAT_ID Cat_Name SUB_ID SUB_NAME ct
1 Arts & Humanities 1 Literature 4
1 Arts & Humanities 2 Photography 6
2 Business & Economy 4 Shopping 1
4 Education 7 College & University 1
4 Education 8 K-12 1
8 News & Media 16 TV 1
10 Reference 20 Libraries 1

Jul 19 '05 #19
GOT IT!! All is well. Changed COUNT(*) to COUNT(l.Cat_Id). Thanks Bob for
your input, help guidance, and for all around being a nice guy!

Jeff
"Jeff Uchtman" <uc*****@megavision.com> wrote in message
news:ua**************@TK2MSFTNGP12.phx.gbl...
OK, very close. Getting count of 1 on relationship even if nothing is here. Other counts are correct. The only problem is the count of 1 even if not
there. Here is what I have;

SELECT
c.CAT_ID,
FIRST(c.CAT_NAME) AS Cat_Name,
s.SUB_ID,
s.SUB_NAME,
COUNT(*) AS ct
FROM
(Cats c
INNER JOIN (Subs s
LEFT JOIN Links l ON
s.SUB_ID = l.SUB_ID AND
s.CAT_ID = l.CAT_ID) ON
c.CAT_ID = s.CAT_ID)
GROUP BY
c.CAT_ID,
c.CAT_NAME,
s.SUB_ID,
s.SUB_NAME
ORDER BY
s.SUB_NAME

"Jeff Uchtman" <uc*****@megavision.com> wrote in message
news:uc**************@TK2MSFTNGP10.phx.gbl...
Here is what I am seeing: I'd like to see all the SUB_ID and SUB_NAME in this query with the assocaiation to the Cat_ID and CAT_NAME giving me
the ct
for links even if 0.

Jeff
Description:
SQL Command:

SELECT
c.CAT_ID,
FIRST(c.CAT_NAME) AS
Cat_Name,
s.SUB_ID,
s.SUB_NAME,
COUNT(*) AS ct
FROM
(Cats c

INNER JOIN (Subs s
INNER JOIN Links l ON
s.CAT_ID = l.CAT_ID AND

s.SUB_ID = l.SUB_ID) ON
c.CAT_ID = s.CAT_ID)
GROUP BY
c.CAT_ID,

c.CAT_NAME,
s.SUB_ID,
s.SUB_NAME
QUERY: TEST \ New Query COUNT: 6 record(s)

CAT_ID Cat_Name SUB_ID SUB_NAME ct
1 Arts & Humanities 1 Literature 4
1 Arts & Humanities 2 Photography 6
2 Business & Economy 4 Shopping 1
4 Education 7 College & University 1
4 Education 8 K-12 1
8 News & Media 16 TV 1
10 Reference 20 Libraries 1


Jul 19 '05 #20
Jeff Uchtman wrote:
GOT IT!! All is well. Changed COUNT(*) to COUNT(l.Cat_Id). Thanks
Bob for your input, help guidance, and for all around being a nice
guy!


Glad to hear it!

I hope you now see the point of creating a tabular representaion of your
desired results. Not only does it help convey the requirements to somebody
else, it also may help you solve the problem yourself by forcing you to see
the output the way the query engine would.

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often
Jul 19 '05 #21

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: James | last post by:
I have a from with 2 fields: Company & Name Depening which is completed, one of the following queries will be run: if($Company){ $query = "Select C* From tblsample Where ID = $Company...
5
by: Scott D | last post by:
I am trying to check and see if a field is posted or not, if not posted then assign $location which is a session variable to $location_other. If it is posted then just assign it to...
2
by: Nick | last post by:
Can someone please tell me how to access elements from a multiple selection list? From what ive read on other posts, this is correct. I keep getting an "Undefined variable" error though... Form...
2
by: Alexander Ross | last post by:
I have a variable ($x) that can have 50 different (string) values. I want to check for 7 of those values and do something based on it ... as I see it I have 2 options: 1) if (($x=="one") ||...
0
by: Dan Foley | last post by:
This script runs fine, but I'd like to know why it's so slow.. Thanks for any help out there on how i can make it faster (it might take up to 5 min to write these 3 export files whith 15 records...
5
by: Lee Redeem | last post by:
Hi there I've created abd uploaded this basic PHP script: <html> <head> <title>PHP Test</title> </head> <body> <H1 align="center">
5
by: christopher vogt | last post by:
Hi, i'm wondering if there is something like $this-> to call a method inside another method of the same class without using the classname in front. I actually use class TEST { function...
6
by: Phil Powell | last post by:
Ok guys, here we go again! SELECT s.nnet_produkt_storrelse_navn FROM nnet_produkt_storrelse s, nnet_produkt_varegruppe v, nnet_storrelse_varegruppe_assoc sv, nnet_produkt p WHERE...
1
by: Michel | last post by:
a site like this http://www.dvdzone2.com/dvd Can you make it in PHP and MySQL within 6 weeks? If so, send me your price 2 a r a (at) p a n d o r a . b e
11
by: Maciej Nadolski | last post by:
Hi! I can`t understand what php wants from me:( So: Cannot send session cache limiter - headers already sent (output started at /home/krecik/public_html/silnik.php:208) in...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.