Mombu the Microsoft Forum sponsored links

Go Back   Mombu the Microsoft Forum > Microsoft > SUBSELECT COUNTING ISSUE
User Name
Password
REGISTER NOW! Mark Forums Read

sponsored links


Reply
 
1 23rd April 11:18
dan fields
External User
 
Posts: 1
Default SUBSELECT COUNTING ISSUE


I have 3 tables

AUTHORS
BOOKS
SW_BOOK_AUTHOR_JUNCTION this is a junction table joining authors to books
as this is a many to many relationship

what i want to do is list my authors and a count of how many books they have
each authored.
author ! count of books
--------!-----------------
j.smith ! 24
d.jones ! 3
d.lee ! 7

and so on

what i end up with is this

author ! count of books
--------!-----------------
j.smith ! 34
d.jones ! 34
d.lee ! 34

i cant seem to get the subselect to count books for only that author.
f****ve me but im very new at sql any help is appreciated. my sql is below
im using ms sql server 2000

SELECT
"AUTHORS"."LAST_NAME",
"AUTHORS"."FIRST_NAME",
"AUTHORS"."AU_STATUS",
"AUTHORS"."AUTHOR_ID",
"BOOKS"."BOOK_ID",
({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')},
RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL,

(SELECT COUNT("BOOKS"."BOOK_ID")
FROM
("daniellukefield"."danie3686"."AUTHORS" "AUTHORS"
LEFT OUTER JOIN "daniellukefield"."dbo"."SW_BOOK_AUTHOR_JUNCTI ON"
"SW_BOOK_AUTHOR_JUNCTION"
ON "AUTHORS"."AUTHOR_ID"="SW_BOOK_AUTHOR_JUNCTION"."A UTHOR_ID")
INNER JOIN "daniellukefield"."danie3686"."BOOKS" "BOOKS"
ON "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ ID"
WHERE "AUTHORS"."AU_STATUS"='ACTIVE' AND "AUTHORS"."AUTHOR_ID" =
"AUTHORS"."AUTHOR_ID"
) AS TOTAL_BOOKS

FROM
("daniellukefield"."danie3686"."AUTHORS" "AUTHORS"
LEFT OUTER JOIN "daniellukefield"."dbo"."SW_BOOK_AUTHOR_JUNCTI ON"
"SW_BOOK_AUTHOR_JUNCTION"
ON "AUTHORS"."AUTHOR_ID"="SW_BOOK_AUTHOR_JUNCTION"."A UTHOR_ID")
INNER JOIN "daniellukefield"."danie3686"."BOOKS" "BOOKS"
ON "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ ID"

WHERE "AUTHORS"."AU_STATUS"='ACTIVE'
ORDER BY {FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')},
RTRIM("AUTHORS"."FIRST_NAME"))}
  Reply With Quote


  sponsored links


2 23rd April 11:18
vishal parkar
External User
 
Posts: 1
Default SUBSELECT COUNTING ISSUE


Dan,

(untested) , pls check the syntaxes and commas.
SELECT
"AUTHORS"."LAST_NAME",
"AUTHORS"."FIRST_NAME",
"AUTHORS"."AU_STATUS",
"AUTHORS"."AUTHOR_ID",
"BOOKS"."BOOK_ID",
({FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')},
RTRIM("AUTHORS"."FIRST_NAME"))}) AS AUTHOR_FULL,
(select count(*)
from "daniellukefield"."danie3686"."BOOKS" "BOOKS"
where "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ ID") as total_books
FROM
("daniellukefield"."danie3686"."AUTHORS" "AUTHORS"
LEFT OUTER JOIN "daniellukefield"."dbo"."SW_BOOK_AUTHOR_JUNCTI ON"
"SW_BOOK_AUTHOR_JUNCTION"
ON "AUTHORS"."AUTHOR_ID"="SW_BOOK_AUTHOR_JUNCTION"."A UTHOR_ID")
INNER JOIN "daniellukefield"."danie3686"."BOOKS" "BOOKS"
ON "SW_BOOK_AUTHOR_JUNCTION"."BOOK_ID"="BOOKS"."BOOK_ ID"
WHERE "AUTHORS"."AU_STATUS"='ACTIVE'
ORDER BY {FN CONCAT({FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')},
RTRIM("AUTHORS"."FIRST_NAME"))}

If this doesn't meet the required pls post ddl/sample records.

--
- Vishal
  Reply With Quote
3 23rd April 11:18
dan fields
External User
 
Posts: 1
Default SUBSELECT COUNTING ISSUE


Vishal

first off i want to say thank you for the time you spent to assist me it is
greatly appreciated.

your solution is real close
here is a sample of what that gives me

author ! count of books
--------!-----------------
d.jones ! 1
d.jones ! 1
d.jones ! 1
d.lee ! 1
d.lee ! 1
d.lee ! 1
d.lee ! 1
d.lee ! 1
d.lee ! 1
d.lee ! 1
d.lee ! 1


before i put your statement edit in

author ! count of books
--------!-----------------
d.jones ! 10
d.lee ! 10

what im trying to do

author ! count of books
--------!-----------------
d.jones ! 3
d.lee ! 7


now i ended up switching the main select to a distinct so i only got one
author but i still get the count of 1. this may be because of the 3 table
junction?

id post a DLL/Sample but im really new to this and don't know how that is
done?

you've written there is no
  Reply With Quote
4 23rd April 11:18
dan fields
External User
 
Posts: 1
Default SUBSELECT COUNTING ISSUE


what i finally ended up with was much simpler that what i started with


SELECT
{FN CONCAT(
{FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}
, RTRIM("AUTHORS"."FIRST_NAME"))}
AS AUTHOR_FULL

, count(*) AS TheCount
FROM "daniellukefield"."danie3686"."AUTHORS" "AUTHORS"
JOIN
"daniellukefield"."dbo"."SW_BOOK_AUTHOR_JUNCTI ON" "SW_BOOK_AUTHOR_JUNCTION"
ON "AUTHORS".AUTHOR_ID
=
"SW_BOOK_AUTHOR_JUNCTION"."AUTHOR_ID"
WHERE "AUTHORS"."AU_STATUS" = 'ACTIVE'
GROUP BY
{FN CONCAT(
{FN CONCAT(RTRIM("AUTHORS"."LAST_NAME"), ', ')}
, RTRIM("AUTHORS"."FIRST_NAME"))}


and this works thanks for everyones help.

Dan


you've written there is no
  Reply With Quote
5 23rd April 11:18
vishal parkar
External User
 
Posts: 1
Default SUBSELECT COUNTING ISSUE


Dan,
Excellent, As i've told you in earlier mail as well that there is no need to have unncessary joins.
This seems to be tidy and hope it solves your problem.

--
- Vishal
  Reply With Quote
Reply


Thread Tools
Display Modes




Copyright 2006 SmartyDevil.com - Dies Mies Jeschet Boenedoesef Douvema Enitemaus -
666