INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Counting with 3 tables

Counting with 3 tables

(OP)
I have the below query with an inner join that works like I want. But, I now need to join a third table and get a count of a specific field.

CODE --> SQL

SELECT

TABLE1.FIELD1
,TABLE2.FIELD1
,TABLE2.FIELD2
,TABLE1.FIELD2
,COUNT(TABLE1.FIELD3) AS QTY

FROM            

TABLE2

INNER JOIN
TABLE1
ON TABLE2.FIELD = TABLE1.FIELD 

I need to add a third table and count a field.

any help would be appreciated

thanks

RE: Counting with 3 tables

How does TABLE3 relate to TABLE1 and/or TABLE2. You can continue your INNER JOIN in a manner similar to this:

SELECT

TABLE1.FIELD1
,TABLE2.FIELD1
,TABLE2.FIELD2
,TABLE1.FIELD2
,COUNT(TABLE1.FIELD3) AS QTY
,COUNT(TABLE3.FIELDx) AS THE_COUNT
FROM TABLE2

INNER JOIN
TABLE1
ON TABLE2.FIELD = TABLE1.FIELD

INNER JOIN TABLE3
ON TABLE3.FIELD = TABLE1.FIELD (or however TABLE3 is related to the others)

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Counting with 3 tables

(OP)
Thanks,

when I do that it's counting all the records within those joins

RE: Counting with 3 tables

You need to tell us more about exactly what it is you're trying to get. How do the tables relate? Which records are you trying to count?

Tamar

RE: Counting with 3 tables

dvannoy,
Are you hoping to count distinct values?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Counting with 3 tables

Even in the original query COUNT(TABLE1.FIELD3) AS QTY does not count distinct field3 values, it counts nonnull values, also duplicates.

Also, COUNT always acts on the whole result, not on the separate tables. If you want to join a count done on the separate table3 you could do a subquery SELECT FOREIGNKEY, COUNT(FIELD) FROM TABLE3 GROUP BY FOREIGNKEY as SUBQUERY and JOIN SUBQUERY on the foreign key.

For example you could

CODE

SELECT ORDERS.*, ORDERITEMCOUNT.ItemCount 
FROM ORDERS 
LEFT JOIN (SELECT ORDERID, COUNT(*) as ItemCount FROM ORDERITEMS GROUP BY ORDERID) ORDERITEMCOUNT 
ON ORDERS.ID = ORDERITEMCOUNT.ORDERID 

The more usual way would be to simply join ORDERITEMS and apply grouping on the overall result.

So, what do you expect to count?

Edit: To once more use the orders and order items as example, a query like the following will count as the result field names advertise

CODE

SELECT COUNT(DISTINCT ORDERS.ID) as OrderCount
COUNT(ORDERS.ID) as OrderstimesOrderitemsCount
COUNT(*) as OrderstimesOrderitemsCount
FROM ORDERS 
LEFT JOIN ORDERITEMS
ON ORDERS.ID = ORDERITEMS.ORDERID 

As ORDERS.ID is never null, you have no difference between COUNT(*) and COUNT(ORDERS.ID) here. I think you expect COUNT(expression) to act as the DISTINCT count but that needs COUNT(DISTINCT expression).
It's unclear whether you want the single rows that will be added, when you join a table3 or whether you only want to join it for a count, then you might use the subquery approach to first compute the count and join only that as 1:1 related data not growing your overall result row count. COUNT(DISTINCT ORDERS.ID) then also is not a solution, it still multiplies all ORDERS rows as many times as each order has order items. Just like the join copies all ORDERS data of the same order id as many times as there are orderitems to be able to add orderitem detail data, the COUNT(DISTINCT ORDERS.ID) is also repeated orderitems times.

I think you're not very familiar with SQL, neither with how JOINS act, how COUNT acts and how to GROUP BY. So as Tamar asked already, what are you trying to achieve?

Bye, Olaf.

RE: Counting with 3 tables

(OP)
so, here is what I have so far

CODE --> sql

SELECT document_number,  
       qty = (SELECT COUNT(O.document_number)
	   FROM TABLE1 O 
	   WHERE O.document_number= C.document_number)
  FROM TABLE2 C 


This gets me the counts I'm looking for but I now need to be able to select more fields from TABLE1.
I've tried using a left join within the query after "FROM TABLE2 C" but keep getting expression and aggregate errors.

RE: Counting with 3 tables

How about simply this:

SELECT C.document_number,
qty = (SELECT COUNT(O.document_number)
FROM TABLE1 O
WHERE O.document_number= C.document_number),
C.next_field,
C.last_field
FROM TABLE2 C

==================================
advanced cognitive capabilities and other marketing buzzwords explained with sarcastic simplicity


RE: Counting with 3 tables

How about this, quite as you had it initially:

CODE

SELECT C.document_number,  
       qty = (SELECT COUNT(O.document_number)
	   FROM TABLE1 O 
	   WHERE O.document_number= C.document_number),
       D.*
  FROM TABLE2 C 
  INNER JOIN TABLE1 D
  ON C.FIELD = D.FIELD 

Notice: Once you give a table an alias name like C, you have to use C to address it, also in WHERE, ON, GROUP BY clauses...

Bye, Olaf.

RE: Counting with 3 tables

(OP)
Olaf, that worked. thank you so much. appreciate it

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close