# SQL Join Query



## gmazza76

Good afternoon

I have around 5 tables I want to create one overall query from, but I am coming up against an issue when creating my first join
I use the code below to pull agent names etc... from a source table which is ok, but I will need to add the 2nd query below 

I will need to do around 5 more queries like "Query2" but no matter what I try I cannot work out which join is best as I want the data to be to the right of the "initial Query" and how I would work out the multiple WHERE clauses and table naming.  I have had a look and I have tried several ways of doing this but I usually get an error either with the JOIN or syntax issue




		Code:
__


Initial Query
select [AgentID], [Name], [SDate], [Lead] AS [Department Lead], [Team Leader]
from Table1
Where CISCOID is not null and [CX_Lead] = 'Nxxxme' and [Title] = 'Standard'


code works on The following to calculate a field on its own



		Code:
__


Query2

SELECT [Cdate] as [Call Date], count([AgentID]) as [IB]
FROM Table2
WHERE (((Table2.[Lead])='Name') AND ((Table2.[Flag])='Handled'))
GROUP BY Table2.[CDate], Table2.[agent], Table2.[Manager];


thanks in advance
Gavin


----------



## gmazza76

I have currently tried to do a left join as follows



		Code:
__


SELECT [AgentID], [Name], [SDate], [Lead] AS [Department Lead], [Team Leader], count(c.[Agent Peripheral Number])
FROM Table1
LEFT JOIN Table2 c ON AgentID = c.[AGENTID2]
WHERE (c.[Lead])='Name') AND ((c.[Flag])='Handled')


But I get an error JOIN expression not supported

thanks


----------



## welshgasman

> (c.[Lead])='Name') AND ((c.[Flag])='Handled')


Are those Fieldnames?


----------



## gmazza76

The ones in [] are and the ' ' criteria for the fields


----------



## welshgasman

So Lead would contain 'Name' ?, not an actual name?


----------



## gmazza76

Sorry, Yes it would


----------



## welshgasman

TBH, I do most of my sql starting from the QBE design window, then modify that,  if that does not aupport a particular aspect of what I am trying to do.

Thinking about it, it is just criteria, whether any exists is a different matter.


----------

