Query return the same record many times over. (1 Viewer)

[SirDrums]

First I dont know if this is the proper way to go about this so feel free to berate me if I am braking some fundemental rule.

I have reference a query inside of another query and joined it to a table.

When I run the query, It returns the same records over and over.

But when I try the join manually (copying a value in the joined table and then filtering the refernced query with it) it gives me only one record like I expected.

Why does it return the same record (every field is the same) over and over again?

 

[Yeldarb]

Sounds like you are looping through each occurrence of the same value to the outside table.

Check your sql statement for which JOIN you are using; and I have a feeling you will find an INNER instead of a LEFT or RIGHT

 

[SirDrums]

I checked SQL and it is a Left join

SELECT PIPING.COMP_ID,[QUERY1].COMP_ID
FROM PIPING LEFT JOIN [QUERY1] ON PIPING.COMP_ID = [QUERY1].COMP_ID

The COMP_ID's in the table and query match and when I run this manually like stated above I get 1 record.

When I run it through the sql above I get hundreds of rows with the same record and data in it.

 

[Yeldarb]

Can you also post the query sql?

 

[SirDrums]

Sure

SELECT PIPING.COMP_ID, RELATIONSHIPINSTANCE.RELATIONSHIPTYPE, RELATIONSHIPINSTANCE.ID1
FROM PIPING LEFT JOIN RELATIONSHPINSTANCE ON PIPING.COMP_ID = RELATIONSHIPINSTANCE.ID2
WHERE (((RELATIONSHIPINSTANCE.RELATIONSHIPTYPE)=3));

This returns what I expect, just a small list of items that I can use in the other query. All of the items in this list are unique and there are no duplicates.

 

[Yeldarb]

Okay, now that I've had my morning jolt, reverse what I said earlier. When I run the same queries I get only one record with an INNER JOIN.

Like so...

SELECT Piping.Comp_ID, Query1.Comp_ID
FROM Piping INNER JOIN Query1 ON Piping.Comp_ID = Query1.Comp_ID;

With a left join i get multiple.

Hope that fixes it

 

[SirDrums]

inner join returns the same mess that left join did.

This is fustrating but I'll keep at it.

 

[Pat Hartman]

Whether the join is inner or left won't affect the rows returned in this case. One of the tables has more than one instance of Comp_ID. Run the query as it is and pick a comp_ID that is duplicated. Then count that comp_ID in the piping table and count it in your query. The product of those two counts is the number of rows you will end up with.

Sometimes you can summarize away this duplication with a totals query but you need to know what is causing it to determine if it is safe to simply summarize it away.

Is it possible that the query should include relationship type in the join. I looked at the tables in another thread but I didn't memorize them.

 

[SirDrums]

I figured it out and how this happened I do not know.

THe table Piping was listed in the query design as Piping_1 just like I brought the table into the query twice. But the sql string said piping.

When I forced the table to be called Piping instead of piping_1 and ran it, it freaked out and gave me all of those funcky returns.

I deleted the query and recreated it and made sure there wasn't any funny business going on. When I referenced this query into the other query and joined the table to it, the returns came back normal.....

really strange... I think I have about half the hair on my head than I did this morning.