Solved Pulling data from three tables into multiple queries.

[Jomat]

Hi, I'm a bit stuck and hope someone can help.
I have three tables that I am trying to combine the data. I don't need all the data, just some.
I'm using three queries.

1st. I have 1 query to sum the total transactions that are over $10,000 based on one Date and the type.
2nd. I have the 2nd query adds from the customer table to the 1st query with the totals amounts, date and type.
3rd. My 3rd query adds the 2nd query data to a 3rd table that holds control transaction reviews based on the date.

When I run my 3rd query for the results. The ID and Linked_ID does not match.
I'm trying to get the 3rd query to pull the data where the ID and Linked_ID would match.
Example: If you run the 3rd query, line two will show the ID as 1 and the Linked_ID as 8.
I'm guessing that my approach is wrong with the multiple queries and joins.

I've tried to link the ID and Link_ID in my 3rd query but get an error "ambiguous outer join".

Thanks for any help ahead of time.

 

[June7]

Third query does not throw "ambiguous join" error when I open it.
Third query is not linking on ID and Link_ID fields. Why would you expect them to match up? Try adding this link to the query.

There is no need for GROUP BY if there are no aggregate calcs. GROUP BY on every field is the same result as no grouping - unless there really are records that are exact duplicates, in which case SELECT DISTINCT would accomplish same.

Your first query uses HAVING clause. This means it returns aggregate records where the SUM is > 10000. If you want transactions > 10000 then you need WHERE clause.

 

[Jomat]

I removed the link from ID to linked ID because I couldn't get it to work.

I need the id and the linked ID to match when I run my 3rd query because :
Many control transaction reviews can be done on one gaming day total.
For example, many people in different departments can review the daily total transaction and say yes I accepted the money, yes inputed into the database, yes I filed a copy into the monthly sales folder and etc.

when the 3rd query is run. On line 2 there is a linked ID 8 that should not be on that line because that review belongs to the employee with system ID of 8.
Employee ID 8 is not listed because her transactions does not total over $10,000 for any days.

I do know why linked ID 8 is listed for employee ID 1. It is because I did an inner join on the gaming date and the filed for date in my 3rd query. Since linked ID 8 have the same date(filed for date) as in the T gaming day it is listed.
So if I add something along the line of saying FOR ALL RECORDS, ID and linked ID must match then i think that would fix the problem. I don't know how to do that part.

How do make the query say the id and linked must match and the gaming day and filed for date must match in my 3rd query?

 

[Jomat]

Thanks. I think i fixed it. In my 3rd query i added a left join on the id and linked ID. It works now. I think my mistake was the join type.
Thanks for your inputs.