Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Problems andthe Cartesian Product

Posted on 1999-08-18
31
Medium Priority
?
266 Views
Last Modified: 2011-09-20
I have three tables.  
Table one has one field with one record.  

Table two has two fields, one field relates to table one.   There is one record in this table.

Table three has two fields, one field relates to table one.  There are two records in this table.

I need ONE SQL statement that returns one record.  It would group by table one's record and return the count of records from table one (it should return 1) and the count of records from table two (it should return 2).

The output would look like this:
Tbl1 Fld 1...Tbl2 Fld 2(count)... Tbl 2 Fld 2 (count)
Test..1.................1.......................2

This is what I came up with
Tbl1 Fld1....Tbl2 Fld2(count)...Tbl2 Fld2 (count)
Test...1.............2.......................2
Test...1.............2.......................2
0
Comment
Question by:kkirt1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 7
  • +3
31 Comments
 

Author Comment

by:kkirt1
ID: 2005102
Edited text of question.
0
 
LVL 2

Expert Comment

by:afpcos
ID: 2005103
SELECT table1.field1,Count(table2.field1),Count(table3.field1)
FROM table1, OUTER table2, OUTER table3
WHERE table1.field1 = table2.field1
AND tablet.field1 = table3.field1
GROUP BY table1.field1

You need the outer joins to insure a count if the other table does not have any relations.
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005104
Too late! BTW, you don't need joins here:
SELECT
  Tbl1.Fld1
  , COUNT(1) as TBL1_Count
  , (SELECT COUNT(1) FROM TBL2 WHERE TBL2.FldRef1 = Tbl1.Fld1) as TBL2_Count
  , (SELECT COUNT(1) FROM TBL3 WHERE TBL3.FldRef1 = Tbl1.Fld1) as TBL3_Count
FROM Tbl1
GROUP BY Tbl1.Fld1;


0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
LVL 7

Expert Comment

by:lmerrell
ID: 2005105
This will do it for you, kkirt1:

SELECT TableA.Field1, DCount("Field2","TableB","Field1 = " & [TableA].[Field1]) AS [Count of TableB], DCount("Field3","TableC","Field1 = " & [TableA].[Field1]) AS [Count of TableC]
FROM TableA
GROUP BY TableA.Field1, DCount("Field2","TableB","Field1 = " & [TableA].[Field1]), DCount("Field3","TableC","Field1 = " & [TableA].[Field1]);


lmerrell
0
 
LVL 2

Expert Comment

by:sets
ID: 2005106
kkirt1,

Your requirement cannot be met through one select sql.

one Select SQL,  is mainly to select information from one main table only.  However,  if the main table is related to other subset tables,  you could get information from subset tables using the relation field.

In your scenario,
you have three tables.
t1 , t2 , t3.

through your texts,  I understand,  the relation however exists like this.
       t1--t2
        \  /
         t3

By your requirement,  you seem to read number of records in t1, t2 and t3 without much usage of the relationship.  I donot understand,  where the picture of group by fits in.

The easy way to do is execute three seperate queries.  

Select count(*) as T1count from t1
Select count(*) as T2count from t2
Select count(*) as T3count from t3

Since,  you wanted these results in one record,  you must analyze what is the condition which binds these three queries to be used to relate and form one record.

I hope you understand, and tackle your issues in different angle.

0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005107
In fact, the group by is only neccesary if Tbl1.Fld1 can contain duplicate entries, which it doesn't:
SELECT
   Tbl1.Fld1
   , 1 as Tbl1_Count
   , (SELECT COUNT(1) FROM TBL2 WHERE TBL2.FldRef1 = Tbl1.Fld1) as TBL2_Count
   , (SELECT COUNT(1) FROM TBL3 WHERE TBL3.FldRef1 = Tbl1.Fld1) as TBL3_Count
FROM Tbl1;

0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005108
Here a little cleaner.  And no joins here either.  And no additional Selects:

SELECT TableA.Field1,
DCount("Field2","TableB","Field1 = " & [TableA].[Field1]) AS [Count of TableB], DCount("Field3","TableC","Field1 = " & [TableA].[Field1]) AS [Count of TableC]
FROM TableA
GROUP BY TableA.Field1;

lmerrell

0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005109
Same here, KangaRoo!  ;-)

SELECT TableA.Field1,
DCount("Field2","TableB","Field1 = " & [TableA].[Field1]) AS [Count of TableB],

DCount("Field3","TableC","Field1 = " & [TableA].[Field1]) AS [Count of TableC]
FROM TableA;

lmerrell
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005110
sets - As you can see, "There is more than one way to skin any cat" ;-)

lmerrell
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005111
:)
0
 

Author Comment

by:kkirt1
ID: 2005112
Sorry afpcos, it eliminted the duplicate records but it still comes back with a count of 2 for the records in table 2.  I need it to return the number 1 (the number of records in table 2).


0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005113
Have yo tried Imererell's and my solution yet?
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005114
I'm sure that both approaches will work.  I'm curious as to which would be more efficient.  Select statement vs. Function?  What do you think KangaRoo.
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005115
Might depend on the selection fields being indexed. Does DCount use indexed or sequential selection?

The online on DCount remarks:

Tip   The Count function has been optimized to speed counting of records in queries. Use the Count function in a query expression instead of the DCount function, and set optional criteria to enforce any restrictions on the results. Use the DCount function when you must count records in a domain from within a code module or macro, or in a calculated control.
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005116
Interesting.  I would definitely use Count when trying to obtain counts of records pertaining to the Selected datasource, but my use of DCount acts more like scenario of use with a calculated control.  I might try testing with larger volumes of data - when I find the time, of course! ;-)
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005117
Access help does state:

Use the DCount function in Visual Basic, a macro, a query expression, or a calculated control.

and

You can use the DCount function to count the number of records containing a particular field that isn't in the record source on which your form or report.  (It says form or report but the principle should be the same - to count the number of records containing a particular field that isn't in the record source.

0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005118
Oops!  Left "is based" off of "on which your form or report".  I misscut!  ;-)
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005119
Testing would be the idea. Tomorrow.
0
 
LVL 1

Expert Comment

by:jason_lewis
ID: 2005120
Here's yet another approach. (One more way to 'skin the cat') It's not strict SQL but it will work in Access. (I'm assuming that this is in Access?)

In Access, you can use the immediate if function (IIf) inside your SQL. So instead of counting, test for null, and add one to the total if it is not null:

SELECT Field1, Sum(Iif(Table2.Field2 is NULL, 0, 1)) as Table2Count, Sum(Iif(Table3.Field2 is NULL, 0, 1)) as Table3.Count
FROM (Table1 LEFT JOIN Table 2 on Table1.Field1 = Table2.Field1) LEFT JOIN Table3 on Table1.Field1 = Table3Field3

By the way -- If Kangaroo's Subquery idea (9:46 am) works, it will probably be the most efficient. Mine combines BOTH joins and functions, which both slow things down. (I guess I only posted it because I'm positive it will work -- if nothing else does.)
0
 

Author Comment

by:kkirt1
ID: 2005121
Yes, the dcount would work if table one only had one record (I guess that is what I said orginally to make things simpler - sorry)  

The tables actually have multiple records.  Maybe I can clarify: I want to count the number of records that relate within the three tables.  Table one relates to table two which relates to table 3.  So table one may have 10 records, table two may have  100 records and table 3 may have 1000 records.  When all is said and done the SQL statement should come back with 10 records and the count of records that match each record from table two and so on.

Table1,Field 1......Table2,Field 2.....Table3,Field2
.....1..................10................50
.....2..................20................25



The total for the Table2,Field 2 column should be the total number of records in that table.  The total for Table 3,Field 2 column should be the total number of records in that table.  

The counts with a simple sql count function are coming back with more records than are in the tables.  The records in table2 are being counted more than once.

I tried to make it as simple as possible and in doing so I wasted a lot of your time.  Sorry!  

Any ideas you have would be appreciated.
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005122
Did you actually test Immerell's and mine proposal?
0
 

Author Comment

by:kkirt1
ID: 2005123
Yes, it worked to bring back the total number of records from table2 and table3.  I need to either expand on that solution or go a diferent direction.  

I tried using the Dcount function to bring back only the count from table2 that related to a record in table1 but couldn't get it.  It either came back with the count of all the records or a none.
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005124
kkirt1,

I think we understood your requirements.  I just ran a test with multiple records in TableA - 3 records; TableB - 6 records; TableC - 12 records.

SELECT TableA.Field1,
DCount("Field2","TableB","Field1 = " & [TableA].[Field1])
AS [Count of TableB],
DCount("Field3","TableC","Field1 = " & [TableA].[Field1])
AS [Count of TableC]
FROM TableA;

Resulting output:

Field1      Count of TableB      Count of TableC
11                            1                            2
22                            2                            4
33                            3                            6

My TableA records were 11, 22, 33.
My TableB records were 11-a, 22-a, 22-b, 33-a, 33-b, 33-c.
My TableC records were 11-a, 11-b, 22-a, 22-b, 22-c, 22-d, 33-a, 33-b, 33-c, 33-d, 33-e, 33-f.

with 11-a being Field1 11; Field2 a for example.

It seems to work fine for me.  Or am I missing the point. ;-)

lmerrell
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005125
It is key to use the third argument of the DCount function to match the key field of TableA to the value of the key field from TableA.  Of course my TableA is your Table1, etc.
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005126
The same applies to the WHERE clauses in the subqueries. Can you gave us the actual names of the tables and the relevant fieldnames and the relations?
I'm unclear on one poit. In the Q you stated that table two and three have a field that refers to table one. A few comments back you say that table three refers to table two and table two refers to table one.
0
 

Author Comment

by:kkirt1
ID: 2005127
Sorry KangaRoo.  I have the tables set up both ways - just trying to get something to work.  Ideally table three would not have to have a field that relates it to table1.  It would use the table2 relationship.  

Immerell:  I am working on implementing your solution.  It looks promissing.  I will let you know.  Thanks!
0
 
LVL 7

Expert Comment

by:KangaRoo
ID: 2005128
Can you give the tables, fields and relations. Table 3 depending on Table 2 is rather a big change. For both methods it means that the columns depend on each other, now both counts only depend on Table 1
0
 

Author Comment

by:kkirt1
ID: 2005129
lmerrell:  I think that is it.  A thousand thanks (oh, and the points too).  One last question though.  I have been messing with the dcount function and cannot figure out the syntax.  Actually I am trying to add another criteria and the " and the & is very confusing.  Microsoft's help is no help!  Can you help me understand that syntax?  Thanks!
0
 
LVL 7

Accepted Solution

by:
lmerrell earned 400 total points
ID: 2005130
The syntax is:

DCount(expr, domain[, criteria])
DCount("Field2","TableB","Field1 = " & [TableA].[Field1])

expr --> Name of field as string = "Field2"
domain --> Like the source (ie. table name, query name, etc) = "TableB"
criteria --> specifies which records from source (like a where keyword) =
                 "Field1 = " & [TableA].[Field1] = If tableA's field1 was 11 then the result would be "Field1 = 11".  If the field was alpha you would also have to concatenate quotes into the criteria.  See the help example:

intX = DCount("[ShippedDate]", "Orders", "[ShipCountry] = '" _
& Forms!Orders![ShipCountry] & "'AND [ShippedDate] > #1-1-95#")

I hope that clarifies it some.  Let me know if it doesn't.  Just glad I could help, kkirt1!

lmerrell
0
 

Author Comment

by:kkirt1
ID: 2005131
What about the Dcount syntax?  Any help, or have I used up my 100 points?  Either way, Thanks!
0
 
LVL 7

Expert Comment

by:lmerrell
ID: 2005132
kkirt1,

Check the text of my answer.  Up at the top of the question thread.  Just click on "Text above...".  I tried to explain the syntax there.  Let me know if you need more help with it. ;-)

lmerrell
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question