Hi Guys, feel quite stupid with this one, been staring at it for ages.
What to combine three tables with the left join sql. keep getting the syntax error in Join operation.

SELECT SubmittedTeam
FROM (NETT
LEFT JOIN NETT ON NETT.SubmittedTeam = Team.Team)
LEFT JOIN tempDate ON NETT.Date = tempDate.date
WHERE tempDate.date is NULL AND Team.Team is NULL;

Thank you so much for looking at this.

Are you trying to actually do a JOIN with 3 tables or 2 tables?
The Team table is never specified. I don't have your data or your intentions in mind but what about something like this?

SELECT SubmittedTeam
FROM NETT
LEFT JOIN Team b ON NETT.SubmittedTeam = Team.Team
LEFT JOIN tempDate ON NETT.Date = tempDate.date
WHERE tempDate.date is NULL AND Team.Team is NULL;

If you could post small samples of what the data looks like and what you want your query to return it would be much easier to find you a solution.

Thanks,
Kenny

Thanks Kenny, very fast response, i see what i have done wrong, but I am still having difficulty in solving the bigger goal.

What I have is three tables that i want to match up.

Table 1 (NETT) holds daily uploaded data by certain teams
Table 2 (List of all the teams)
Table 3 (list of dates)

what I want to be able to do is see which team has not uploaded data for a given date range i.e. find missing records.

so the user would be presented with a form to enter start date and end date, which then, using vba, creates a temporary table (table 3) with the list of dates. Then a sql query is run to find all missing records within that range.

final output would be: missing uploads [TEAM] - [DATE]

Thanks for looking at this, really appreciate it

Hey, i might have made progress, i have managed to create two queries, one for finding missing teams in table 1, and one query to find missing dates from table 1

Missing Teams:

SELECT Team.Team
FROM NETT RIGHT JOIN Team ON NETT.SubmittedTeam=Team.Team
WHERE (((NETT.SubmittedTeam) Is Null));

Missing Dates:

SELECT tempDate.date
FROM NETT RIGHT JOIN tempDate ON NETT.Date=tempDate.date
WHERE (((NETT.Date) Is Null));

So what i am struggling with now is how to combine the two, tried to simple put two RIGHT JOIN statements in there but doesn't seem to work.

Can you post what some of the data is like? If Team always contain the team names I would do a couple left joins like below:

SELECT Team.Team,
		NETT.SubmittedTeam,
		tempDate.Date
FROM Team
	LEFT JOIN NETT ON Team.Team = NETT.SubmittedTeam
	LEFT JOIN tempDate ON NETT.Date = tempDate.Date
WHERE NETT.SubmittedTeam IS NULL
	OR tempDate.Date IS NULL

The results would look something like the attached image.

If this isn't what you have in mind please post sample data.

Thanks,
Kenny

commented: Really appreciate the work done to help me understand +2
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.