join's question

  • Thread starter Thread starter Alejandra Parra
  • Start date Start date
A

Alejandra Parra

I have 3 tables A,B and C

A is related with B
and A is related with C

FROM (A LEFT JOIN B on (A.DATA)= (B.DATA)) RIGHT JOIN C on
(C.str_cvealumno)= A.str_cvealumno))

I know I can´t do this because I can't nest left and right oins, but, what
can I do to have this result???

Thanx
 
?? I created a query in design view with the joins you stated, and the SQL
that I get looks like this:

SELECT A.*
FROM (A LEFT JOIN B on A.DATA= B.DATA) RIGHT JOIN C on A.str_cvealumno =
C.str_cvealumno;
 
I understand that but...
I want to do something like this
from (a LEFT JOIN B ON A.DATA=B.DATA) RIGHT JOIN C ON
B.STR_CVEALUMNO=C.STR_CVEALUMNO

and I can't do it
please!!!! help!!!!
 
Ah, but that isn't what you'd posted originally.

You'll need to do it in two different queries. Likely you'll need to create
a query that gives you one of the join's results, then create a second query
that gives you the results of the second join (using the first query as one
of the tables for the second join).
 
Could you be so kind to give me an example about the theory you posted,
please???
 
Create a query that does the Left Join. Save it and name it qryLeft.

Then create a query that does the Right Join, using qryLeft as the table on
the left side of the right join.

However, do note that the query will try to match up some Null values from
the qryLeft query's "B.STR_CVEALUMNO" field as the joining value for records
in the C table, and this will give you no results at all. So, are you sure
that you want to do what you have posted?

Perhaps, let's back up and start with a description from you of what you're
trying to accomplish with the query, and let's see if the correct approach
is something quite different.
 
I have 3 tables:

Tbl_alumnos :

str_cvealumno Str_nombre str_numerolista
----------------------------------------------------------
CODE1 NAME 1
CODE2 NAME 2
CODE3 NAME 3
.....

Tbl_DatosCaptura:

str_cvedato str_descripcion
---------------------------------------
DATA1 DESCRIPTION
DATA2 DESCRIPTION
DATA3 DESCRIPTION
....

Tbl_calificaciones:

str_cvealumno str_cvedato str_cvevalor
---------------------------------------------------
CODE1 DATA1 10
CODE1 DATA2 9
CODE2 DATA1 9.5
......

Tbl_alumnos and Tbl_DatosCaptura will always have data in them.
Tbl_Calificaciones won't

I want to get this

STR_NUMEROLISTA |STR_NOMBRE | DATA1 | DATA2 |
DATA3 ------>ALL DESCRIPTIONS IN TBL_DATOS CAPTURA
----------------------------------------------------------------------------
---------
1 | NAME | 10
| 9 |
2 | NAME | 9.5
| |
3 | NAME |
| |
|
\____________________/
|
|
|
STR_VALOR IN
V MATCHING
STR_CVEALUMNO - STR_CVEDATO
ALL STR_NUMEROLISTA IN TBL_CALIFICACIONES
AND STR_NOMBRE IN
TBL_ALUMNOS
 
I have 3 tables:
Click to expand...

Apparently you also have a question, but you didn't post it in this
thread - and among the hundreds of postings in the newsgroup, I
apparently didn't happen to download your previous message. Could you
please explain what problem you're having?
 
I have this query


TRANSFORM sum(PRUEBA.str_Valor)
SELECT prueba.str_NombreAbreviado AS Nombre,prueba.str_NumeroLista AS NL
FROM ((SELECT tbl_alumnos.str_NombreAbreviado, tbl_alumnos.str_NumeroLIsta,
tbl_alumnos.str_cvegrado, tbl_alumnos.str_cvegrupo,
tbl_alumnos.str_cvealumno, tbl_calificaciones.str_valor,
TBL_CALIFICACIONES.STR_CVEDATO from tbl_alumnos left join
tbl_calificaciones on tbl_alumnos.str_cvealumno =
tbl_calificaciones.str_cvealumno where tbl_alumnos.str_cvegrado='1' and
tbl_alumnos.str_cvegrupo='B') prueba
???????????? join
(SELECT tbl_calificaciones.str_Valor,
tbl_calificaciones.str_cvealumno,TBL_DATOSCAPTURA.STR_CVEDATO,
tbl_datoscaptura.str_descripcion from tbl_calificaciones
RIGHT JOIN tbl_DATOSCAPTURA on tbl_calificaciones.str_cvedato=
tbl_datoscaptura.str_cvedato) ale
on (prueba.str_cveDATO) =(ale.str_cveDATO))
GROUP BY prueba.str_NombreAbreviado,prueba.str_NumeroLista
PIVOT ale.str_descripcion

Where the ??????????????? are located, I don´t know what to write, because
if I use RIGHT JOIN I have just a part of the table, and if I use LEFT JOIN
I get the other part of the table I need, and if I use INNER JOIN nothing
appears, because I must get NULL values in the crossed reference table, so,
I tried to use cross join or full join but they don't work in access 2003
I'd like some help in this
TNX!!!!
 
sorry, did you receive the explanation of the tables I have and the result
table I want to get???
 
Hey!

Well, I've solved this little "problem"
I had to insert at least one str_cvealumno for each str_cvedato in
tbl_calificaciones, so it could make at least one match and bring the rest
of the str_cvealumno and str_cvedato, and I left str_valor wiht null so I
could get what I wanted

I hope you understand this, because it sounds twisted, if not, I'll answer
every question you have.

I thank you very much for your time, you were really helpful.

Alejandra
Mexico
 
I'm not sure I fully understand, but I think what you've done is to ensure
that there is a value in each linking field so that records can be
returned -- which is what I'd indicated your original query apparently could
not do for you.

Good luck. I hadn't replied yet because I was still comprehending the
structure and thinking about what to reply!
 
Yes, I just did that
And don't worry, I really thank you for your time and the attention, it was
really good.
Good luck!!!
 
Back
Top