Join not returning correct number of rows

[ebnemik]

I am trying to write a query to select 3 fields from one query & 2 other fields from another query. When I attempt to run this and I join on the three fields from the first query to the 2nd I get duplicate rows. Meaning, without the 2 fields form the 2nd query I get X amount of rows, but since there are duplicate records in the 1st query (I do want these because the values in the fields from the 2nd query can be different) I get double the rows for those records that are duplicate. For the rows that are not, it is fine.

I have tried using ‘Distinct’, but the row count is off. Any ideas?

Thanks, Mike

 

[jrbarnett]

Ebnemik

My guess is that your tables are not joined with referential integrity against them. When joins between the tables are not specified, the default is to produce the cartesian product of two tables: namely for every entry in table1, there will be a corresponding entry from table2.

The way around it is to ensure that the tables are joined together in query design mode on a common field, or if this is not possible due to its design, I would do a select query from one, then UNION it to a select query from the second table.

If you want some more help, please post the SQL code for your query here (go to View -> SQL in query design).

John

 

[ebnemik]

jrbarnett,

Thanks for the reply. I dont think these tables have referential integrity enforced, but I do not believe I am getting a cartesian join. I have included my query: I want to get the number of records that are not in one of the queries, so I want to combine them where they are the same, but only display the extra records. I need some different fields from the two queries, so I thought I could not use JOIN. Thanks...

SELECT qryReconcileTMSRates.ID AS Lane, qryReconcileTMSRates.CARRIER_ID, qryReconcileTMSRates.EFFECTIVE, qrymanuMasterRates.VARIABLE_RATE, qrymanuMasterRates.FIXED_CHARGE
FROM qryReconcileTMSRates INNER JOIN qrymanuMasterRates ON (qryReconcileTMSRates.ID = qrymanuMasterRates.LANE_ID) AND (qryReconcileTMSRates.EFFECTIVE = qrymanuMasterRates.EFFECTIVE) AND (qryReconcileTMSRates.CARRIER_ID = qrymanuMasterRates.CARRIER_ID)
WHERE (((qryReconcileTMSRates.EFFECTIVE)>#5/20/2003#))
ORDER BY qryReconcileTMSRates.EFFECTIVE, qryReconcileTMSRates.ID;

 

[RickSpr]

You are getting kind of a "mini-Cartesian product" for the duplicate rows. That is, each row of a set having the same ID, EFFECTIVE, and CARRIER_ID in qryReconcileTMSRates is being matched with each row of qrymanuMasterRates having matching LANE_ID, EFFECTIVE, and CARRIER_ID values. It's effectively an inner join on a many-to-many relationship.

I take it that you want each row from qryReconcileTMSRates, but you want it matched with only one row from qrymanuMasterRates.

Open qrymanuMasterRates and look at sets of rows that have the same LANE_ID, EFFECTIVE, and CARRIER_ID values. You want to pull VARIABLE_RATE and FIXED_CHARGE from that set of rows. Will VARIABLE_RATE and FIXED_CHARGE always have the same values for the rows in each set?

If so, you can solve your problem this way:
1. Create a GROUP BY query based on qrymanuMasterRates.
2. Group by the three fields you use to join with qryReconcileTMSRates.
3. Select the First() value for each of VARIABLE_RATE and FIXED_CHARGE.
4. Save the query. Then substitute this query for qrymanuMasterRates in your original query.
(BTW, if VARIABLE_RATE and FIXED_CHARGE always have the same values, it suggests either that one of the underlying tables isn't normalized, or that qrymanuMasterRates is itself a join that includes some table you don't need in the present case. If the latter, it would be more efficient to eliminate the duplicates by creating a similar query that omits the unneeded table(s).)

If VARIABLE_RATE and FIXED_CHARGE do not always have the same values, what you're asking for doesn't make sense, because you haven't given us any criterion for choosing one value over another. If you don't really care which row you use in the join, you can use the method above. If you do care, you need to expand your inner join to incorporate that criterion.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein