One record One Query Multiple tables 46 returns

[Zydeceltico]

Hi All -

I have many tables. Most are lookup tables.

Two tables are not lookup tables.

One table is a general job table.

The second is a job detail table.

I created a query that pings the two tables I just mentioned and has to access multiple other lookup tables.

I created one (1) test record that has data recorded in one row of table 1 and one row of table 2.

When I hit "Run", I am returned a datasheet of 46 records that are all identical.

Before I go down a rabbit hole that will make me nuts and read/watch multiple tutorials - - - I thought I would just ask:

Why is that?

Thanks!

Tim

 

[theDBguy]

Hi Jim. You may be seeing a cartesian product. Are you using any JOINs in your query? Can you post the SQL statement? Thanks.

 

[Zydeceltico]

Hi Jim. You may be seeing a cartesian product. Are you using any JOINs in your query? Can you post the SQL statement? Thanks.

I did not "intend" to use any joins yet..................


SELECT tblInspectionEvent.DateTime, tblJobs.JobNumber, tblInspectionEvent.BundleNumber, tblInspectionEvent.Piecemark, tblInspectionEvent.FirstPieceCheck, tblParts.PartType, tblFinalProducts.FinalProductType, tblAssemblyComponents.ComponentType, LUtblPersonnel.FirstName, tblInspectMill.LengthRequired, tblInspectMill.LengthActual
FROM LUtblPersonnel, ((tblFinalProducts INNER JOIN (tblParts INNER JOIN tblAssemblyComponents ON tblParts.Part_ID = tblAssemblyComponents.Part_FK) ON tblFinalProducts.FinalProduct_ID = tblAssemblyComponents.FinalProduct_FK) INNER JOIN tblJobs ON tblFinalProducts.FinalProduct_ID = tblJobs.FinalProduct_FK) INNER JOIN (tblCoils INNER JOIN (tblInspectionEvent INNER JOIN tblInspectMill ON tblInspectionEvent.[InspectionEvent_PK] = tblInspectMill.[InspectionEvent_FK]) ON tblCoils.CoilNumber_PK = tblInspectMill.CoilNumber_FK) ON tblJobs.Job_ID = tblInspectionEvent.Job_FK;

 

[theDBguy]

Hi Jim. Without any JOINs, you do get a Cartesian Product. Check out this short blog article. When you get there, you can click on the link to the Cartesian Query article.

 

[MajP]

SELECT tblinspectionevent.datetime, 
       tbljobs.jobnumber, 
       tblinspectionevent.bundlenumber, 
       tblinspectionevent.piecemark, 
       tblinspectionevent.firstpiececheck, 
       tblparts.parttype, 
       tblfinalproducts.finalproducttype, 
       tblassemblycomponents.componenttype, 
       lutblpersonnel.firstname, 
       tblinspectmill.lengthrequired, 
       tblinspectmill.lengthactual 
FROM   lutblpersonnel, 
       ((tblfinalproducts 
         INNER JOIN (tblparts 
                     INNER JOIN tblassemblycomponents 
                             ON tblparts.part_id = 
                    tblassemblycomponents.part_fk) 
                 ON tblfinalproducts.finalproduct_id = 
                    tblassemblycomponents.finalproduct_fk) 
        INNER JOIN tbljobs 
                ON tblfinalproducts.finalproduct_id = tbljobs.finalproduct_fk) 
       INNER JOIN (tblcoils 
                   INNER JOIN (tblinspectionevent 
                               INNER JOIN tblinspectmill 
                                       ON 
                               tblinspectionevent.[inspectionevent_pk] = 
                               tblinspectmill.[inspectionevent_fk]) 
                           ON tblcoils.coilnumber_pk = 
                              tblinspectmill.coilnumber_fk) 
               ON tbljobs.job_id = tblinspectionevent.job_fk;

lots of free sql formatters online. Makes much easier to decipher.

 

[MajP]

lutblpersonnel causes the cartesian product since it is not joined to anything.