• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6455
  • Last Modified:

Left outer join not supported in SQL statement

Access says "Join expression not supported":

SELECT Count(*)
FROM dbo.organiz a left outer join SIC_CODE c on
a.SIC_ID=c.SIC_ID AND a.SIC_GID=c.SIC_GID, PERSON b
WHERE a.CREATEPER = b.PERSON_ID AND
(a.CREATE_DAT Between #8/18/2004# And #10/17/2004#) AND
(a.SIC_ID Is Null OR c.SIC_ABRV='TBD') AND (a.MKT_SEG_ID<>8) AND (left(b.PER_INIT,LEN('LV'))='LV');

How can I modify this statement so Access can understand it?

Thanks!
0
psk1
Asked:
psk1
  • 2
  • 2
  • 2
  • +2
2 Solutions
 
Rey Obrero (Capricorn1)Commented:
try

SELECT Count(*)
FROM dbo.organiz a LEFT join SIC_CODE c on
a.SIC_ID=c.SIC_ID AND a.SIC_GID=c.SIC_GID, PERSON b
WHERE a.CREATEPER = b.PERSON_ID AND
(a.CREATE_DAT Between #8/18/2004# And #10/17/2004#) AND
(a.SIC_ID Is Null OR c.SIC_ABRV='TBD') AND (a.MKT_SEG_ID<>8) AND (left(b.PER_INIT,LEN('LV'))='LV');
0
 
psk1Author Commented:
Same thing happening here:

SELECT Count(organiz.org_id)
FROM organiz organiz left outer join orgperus orgperus
on organiz.ORG_ID=orgperus.ORG_ID, person person
WHERE organiz.MKT_SEG_ID<>8 and orgperus.org_id is null AND
(organiz.CREATE_DAT Between @BeginDate And @EndDate) AND
organiz.CREATEPER=person.person_id AND
left(person.per_init,LEN(@IDCode))=@IDCode;

What's up with this left outer join?
0
 
psk1Author Commented:
Capricorn,
That gave me the same result :(

It doesn't like the left join at all?  What should I be looking at to find the root of the problem?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Rey Obrero (Capricorn1)Commented:
Can you try it first with a simple query.
create two tables A and B with two fields and try a query like this

SELECT A.ID, A.Amount
FROM A LEFT JOIN B ON A.ID = B.ID;
0
 
shanesuebsahakarnCommented:
Try:

SELECT Count(*)
FROM (dbo.organiz a LEFT JOIN SIC_CODE c on
a.SIC_ID=c.SIC_ID AND a.SIC_GID=c.SIC_GID) INNER JOIN PERSON b
ON a.CREATEPER = b.PERSON_ID WHERE
(a.CREATE_DAT Between #8/18/2004# And #10/17/2004#) AND
(a.SIC_ID Is Null OR c.SIC_ABRV='TBD') AND (a.MKT_SEG_ID<>8) AND (left(b.PER_INIT,LEN('LV'))='LV');
0
 
poayseeCommented:
Can you try to check on your data type and length which you join together?
0
 
miqrogrooveCommented:
Each one of the queries so far appears to have invalid syntax directly after the word FROM, unless this is something I'm not familiar with.  See:

FROM dbo.organiz a
FROM organiz organiz
FROM (dbo.organiz a

As far as I know, none of these are valid, and all would cause a join error.

Good luck,
-- Miqro
0
 
miqrogrooveCommented:
psk1,  if those queries are working for you, could you explain what 'dbo.organiz a' does?  I'm curious.
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now