WHERE IN A LEFT JOIN

[CortexWayne]

SELECT t1.CodeFemel, t1.Tatou as Tatou,'F' as Sexe, 
t3.CCSIErr, t3.RepEnvoi FROM 
(PMater.PFemelc AS t1 LEFT JOIN (SELECT CodeFemel, Tatou, TypeReq, RepEnvoi, CCSIErr FROM PGenet.PGCcsiF WHERE TypeReq = 4) AS t3 ON t3.CodeFemel = t1.CodeFemel)

Err 3131 Error in FROM clause

Sorry, I'm french and my english is not so good. That's why I don't speak very much...

Thx in advance
Martin

 

[jebry]

Hi Martin!

I don't think that Access SQL allows a subquery in the From clause. You can use your SQL to create a query and then use the query name in the From clause.

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CortexWayne]

Arggg! To bad

What I need is to join these two table but only in the case the t3 table have typeReq = 4 because the CodeFemel and TypeReq are a spit unique key. Any suggestion other than doing 2 query and filtering them after?

thx Jeff to have took the time to anwser my post.

Martin

 

[jebry]

Hi!

Can't you just do it like this:

SELECT t1.CodeFemel, t1.Tatou as Tatou,'F' as Sexe,
t3.CCSIErr, t3.RepEnvoi FROM
PMater.PFemelc AS t1 LEFT JOIN PGenet.PGCcsiF AS t3 ON t3.CodeFemel = t1.CodeFemel WHERE TypeReq = 4;

?

hth


Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CortexWayne]

NO

Because it returns only the rows than have typeReq equal to 4 and I want ALL t1 rows WITH t3.CCSIErr, t3.RepEnvoi in the case TypeReq equal to 4 in t3

thanks again!


Here is a simple example of what i need

t1.CodeFemel t1.Tatou
1 A
2 B
3 C
4 D
5 E

t3.CodeFemel t3.TypeReq
1 1
2 7
3 4
3 2
4 2
4 7
5 4
5 1
5 3

I would need
t1.CodeFemel t1.Tatou t2.TypeReq
1 A
2 B
3 C 4
4 D
5 E 4


Hope you understand my not so good english

Martin

 

[r937]

yes you can use a subquery in the FROM clause

this should work, i removed one level of parentheses -

SELECT t1.CodeFemel
     , t1.Tatou 
     , 'F'   as Sexe
     , t3.CCSIErr
     , t3.RepEnvoi 
  FROM PMater.PFemelc AS t1 
LEFT outer
  JOIN (
       SELECT CodeFemel
            , Tatou
            , TypeReq
            , RepEnvoi
            , CCSIErr
         FROM PGenet.PGCcsiF 
        WHERE TypeReq = 4
       ) AS t3 
    ON t3.CodeFemel = t1.CodeFemel

are you sure the table names are correct? i don't recall ever seeing a "name dot name" format for table names in Access


jeff, yours won't work as a LEFT OUTER JOIN because you have a WHERE condition on the right table, that effectively turns it into an inner join

r937.com | rudy.ca

 

[jebry]

Yes, I see you are correct. I didn't test it first and have never tried it since I always just created a query and used it as a table in my next query.

I really thought Access didn't allow subqueries in the From clause. I know I got an error saying that recently, but maybe it was from another program. I use Brio a lot too.



Jeff Bridgham
Purdue University
Graduate School
Data Analyst

 

[CortexWayne]

No I tried your code in my VisData, same error...

A "name dot name" is different database
database.tablename
so they look in 2 different access database
A genetic database and a maternity database wich are used in the same project

 

[r937]

what's VisData? i thought this was Access

try running the subquery by itself as a query

if that works, then please save it as a query and then the main query becomes --

SELECT t1.CodeFemel
     , t1.Tatou 
     , 'F'   as Sexe
     , t3.CCSIErr
     , t3.RepEnvoi 
  FROM PMater.PFemelc AS t1 
LEFT outer
  JOIN [b]query_you_saved[/b] AS t3 
    ON t3.CodeFemel = t1.CodeFemel

r937.com | rudy.ca

 

[CortexWayne]

Ok I might not be in the right forum

I'm developing a VB application wich use Access Database, VisData is a freeware wich call query to access database and return response so I dont need to run my program for each test i do.

I'm unable to save anything as a query and the use it again.
Can you direct me to the good forum plz

Martin, my bad english lead me wrong angain!

Thx for all the hlp...

 

[CortexWayne]

oK! WOooHOOO!! I found the anwser

here it is!

SELECT t1.CodeFemel
     , t1.Tatou
     , 'F'   as Sexe
     , t3.CCSIErr
     , t3.RepEnvoi
  FROM PMater.PFemel AS t1
LEFT outer
  JOIN [
       SELECT CodeFemel
            , Tatou
            , TypeReq
            , RepEnvoi
            , CCSIErr
         FROM PGenet.PGCcsiF
        WHERE TypeReq = 4
       ]. AS t3
    ON t3.CodeFemel = t1.CodeFemel

in fact! What help me solve the solution is that you told me I was not in the good forum so I tought "I have to think outside of access" and then it struck me like a lightning. I have to pass it the subquery as an entire string.

thx a lot you've been a great hlp, both of you!!

happy Martin!