Five Inner Join in Jet 1

[XaRz]

Hi to all,

[I'm a newbie in JET SQL please don't blame me!]

I've a SQL query that I need to convert to JET SQL but I don't understand the sintaxis of FROM and INNER JOIN when 3 or more are involved.

Here I paste to you my SQL Query.. any hint would help

SELECT
CLIE_CODI,ALVC_CODI,FRVC_CODI

FROM CLIE
    JOIN ALVC ON CLIE_PK = ALVC_CLIE_PK
    JOIN ALVCTOTL ON ALVCTOTL_ALVC_PK = ALVC_PK
    JOIN ALVL ON ALVL_ALVC_PK = ALVC_PK
    JOIN FRVL ON (FRVL_LINEA_PADRE_PK = ALVL_PK AND FRVL_TB_PADRE ='ALVL')
    JOIN FRVC ON FRVC_PK = FRVL_FRVC_PK
WHERE CLIE_CODI  = '05083'

AND FRVC_FECHA_ALTA > '04/30/2005'
and FRVC_ANULADO = 0
and ALVC_ANULADO = 0

GROUP BY CLIE_CODI ,FRVC_CODI,ALVC_CODI

 

[lupins46]

From what I can see at first look, all you have to do is put the word Inner in front of each 'Join'.

Inner Join

 

[XaRz]

No. This doesn't work.
I've read throght the net that the sintaxis should be something like this:

SELECT campos
FROM tabla1 INNER JOIN
(tabla2 INNER JOIN [( ]tabla3
[INNER JOIN [( ]tablax [INNER JOIN ...)]
ON tabla3.campo3 operadorcomp tablax.campox)]
ON tabla2.campo2 operadorcomp tabla3.campo3)
ON tabla1.campo1 operadorcomp tabla2.campo2;

But when I aplied to 5 Joins and it doesn't work.

 

[lespaul]

I think it should be:

SELECT
CLIE_CODI,ALVC_CODI,FRVC_CODI

FROM ((((CLIE
    INNER JOIN ALVC ON CLIE_PK = ALVC_CLIE_PK)
    INNER JOIN ALVCTOTL ON ALVCTOTL_ALVC_PK = ALVC_PK)
    INNER JOIN ALVL ON ALVL_ALVC_PK = ALVC_PK)
    INNER JOIN FRVL ON (FRVL_LINEA_PADRE_PK = ALVL_PK AND FRVL_TB_PADRE ='ALVL'))
    INNER JOIN FRVC ON FRVC_PK = FRVL_FRVC_PK
WHERE CLIE_CODI  = '05083'

AND FRVC_FECHA_ALTA > '04/30/2005'
and FRVC_ANULADO = 0
and ALVC_ANULADO = 0

GROUP BY CLIE_CODI ,FRVC_CODI,ALVC_CODI

(I'm sure PHV will be right behind me with the "right" answer!)

Although, I think I saw in another post that you can't have a JOIN statement like this in Access:

INNER JOIN FRVL ON (FRVL_LINEA_PADRE_PK = ALVL_PK AND [b]FRVL_TB_PADRE ='ALVL'[/b]))

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[XaRz]

Doesn't works.

I can't find a similar example to this kind of joins in JET.
I'll visit a library this evening...perhaps I can find a book of SQL JET...and advanced one! because in a medium skll book I can't find any example.

Well Thanks anyway.

 

[fredericofonseca]

all compares with constants must be on the "where" bit.
The joins should only contain field names from the tables.

Also each join should be enclosed in "()" pairs


The date bit may also need to be converted so if the code below does not work try and remove
AND FRVC_FECHA_ALTA > '04/30/2005'
If it then works you will need to change this also.

SELECT
CLIE_CODI,ALVC_CODI,FRVC_CODI

FROM (((((CLIE
  INNER  JOIN ALVC ON CLIE_PK = ALVC_CLIE_PK)
  INNER  JOIN ALVCTOTL ON ALVCTOTL_ALVC_PK = ALVC_PK)
  INNER  JOIN ALVL ON ALVL_ALVC_PK = ALVC_PK)
  INNER  JOIN FRVL ON FRVL_LINEA_PADRE_PK = ALVL_PK)
  INNER  JOIN FRVC ON FRVC_PK = FRVL_FRVC_PK)
WHERE CLIE_CODI  = '05083'

AND FRVC_FECHA_ALTA > '04/30/2005'
and FRVC_ANULADO = 0
and ALVC_ANULADO = 0
AND FRVL_TB_PADRE ='ALVL'

GROUP BY CLIE_CODI ,FRVC_CODI,ALVC_CODI

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[JonFer]

Why don't you use the Access query design window to create your joins?

John

http://www.interautomateconsulting.com

 

[lespaul]

I'm pretty sure I've got the parens all right, I think it's the second part of my reply where you are trying to join on a literal expression. I don't think Access can do that. You may need to change it to the WHERE clause:

SELECT
CLIE_CODI,ALVC_CODI,FRVC_CODI

FROM ((((CLIE
    INNER JOIN ALVC ON CLIE_PK = ALVC_CLIE_PK)
    INNER JOIN ALVCTOTL ON ALVCTOTL_ALVC_PK = ALVC_PK)
    INNER JOIN ALVL ON ALVL_ALVC_PK = ALVC_PK)
    INNER JOIN FRVL ON FRVL_LINEA_PADRE_PK = ALVL_PK)
    INNER JOIN FRVC ON FRVC_PK = FRVL_FRVC_PK
WHERE CLIE_CODI  = '05083'

AND FRVC_FECHA_ALTA > '04/30/2005'
and FRVC_ANULADO = 0
and ALVC_ANULADO = 0
AND FRVL_TB_PADRE ='ALVL'


GROUP BY CLIE_CODI ,FRVC_CODI,ALVC_CODI

 

[XaRz]

Is working!

But I modified to this:

SELECT CLIE.CLIE_CODI, ALVC.ALVC_CODI, FRVC.FRVC_CODI
FROM ALVCTOTL, ALVL, FRVL, FRVC, CLIE INNER JOIN ALVC ON CLIE.CLIE_PK = ALVC.ALVC_CLIE_PK
WHERE (((CLIE.CLIE_CODI)='05083') AND ((FRVC.FRVC_ANULADO)=0) AND ((ALVC.ALVC_ANULADO)=0) AND ((FRVL.FRVL_TB_PADRE)="ALVL"))
GROUP BY CLIE.CLIE_CODI, ALVC.ALVC_CODI, FRVC.FRVC_CODI;

Because access need the table reference before fields.

Another matter is the sentence of FRVC_FECHA_ALTA > '04/30/2005' I receive an error on the criteria field....

Thanks to all anyway!

 

[XaRz]

To JonFer

Because I need more practice in the desing view. On the other hand access is not capable of showing the query in the desing view, at least not this one..

 

[lespaul]

Dates in Access have to be delimited with # not '

try

#04/30/2005#

 

[XaRz]

Thanks now is working in SQL view. But running the query access hangs (or seems to be) ...I'm doing something wrong...but in Ibexperts the results comes in 15 seconds, and in access is still running (1 minute ago I run the query..)

 

[XaRz]

This query is the one which Access understands, but executing on Access hangs:

SELECT
CLIE.CLIE_CODI,ALVC.ALVC_CODI,FRVC.FRVC_CODI

FROM (((((CLIE
    INNER JOIN ALVC ON CLIE.CLIE_PK = ALVC.ALVC_CLIE_PK)
    INNER JOIN ALVCTOTL ON ALVCTOTL.ALVCTOTL_ALVC_PK = ALVC.ALVC_PK)
    INNER JOIN ALVL ON ALVL.ALVL_ALVC_PK = ALVC.ALVC_PK)
    INNER JOIN FRVL ON FRVL.FRVL_LINEA_PADRE_PK = ALVL.ALVL_PK)
    INNER JOIN FRVC ON FRVC.FRVC_PK = FRVL.FRVL_FRVC_PK)
WHERE CLIE.CLIE_CODI  = '05083'

AND FRVc.FRVC_FECHA_ALTA > #04/30/2005#
and FRVC.FRVC_ANULADO = 0
and ALVC.ALVC_ANULADO = 0
AND FRVL.FRVL_TB_PADRE ='ALVL'

GROUP BY CLIE.CLIE_CODI ,FRVC.FRVC_CODI,ALVC.ALVC_CODI;

And this one is the query that I need to perform:

SELECT
CLIE.CLIE_CODI,ALVC.ALVC_CODI,FRVC.FRVC_CODI

FROM (((((CLIE
    INNER JOIN ALVC ON CLIE.CLIE_PK = ALVC.ALVC_CLIE_PK)
    INNER JOIN ALVCTOTL ON ALVCTOTL.ALVCTOTL_ALVC_PK = ALVC.ALVC_PK)
    INNER JOIN ALVL ON ALVL.ALVL_ALVC_PK = ALVC.ALVC_PK)
    INNER JOIN FRVL ON FRVL.FRVL_LINEA_PADRE_PK = ALVL.ALVL_PK [b]AND FRVL.FRVL_TB_PADRE ='ALVL')[/b]
    INNER JOIN FRVC ON FRVC.FRVC_PK = FRVL.FRVL_FRVC_PK)
WHERE CLIE.CLIE_CODI  = '05083'

AND FRVc.FRVC_FECHA_ALTA > #04/30/2005#
and FRVC.FRVC_ANULADO = 0
and ALVC.ALVC_ANULADO = 0
(here was the sencence moved to the join in bold)

GROUP BY CLIE.CLIE_CODI ,FRVC.FRVC_CODI,ALVC.ALVC_CODI;

This Select which works with ibexperts (SQL Standard) is not possible to do in access?
I've executed the first query and access hangs, therefore I suspect that the join is not the same because the condition AND FRVL.FRVL_TB_PADRE ='ALVL' is on the where clause and not in the join.

There is a way to do this in access? or simulate it?

Thanks in advance dudes,

 

[JonFer]

I would add an index to FRVL.FRVL_TB_PADRE and see if that helps the response time. You could also add indexes to the other fields in your WHERE clause. If one of the tables has a lot more rows than the others, put all of the WHERE conditions for that table first.

Why do you "need" to use the second syntax? I consider the first syntax more common.

John

http://www.interautomateconsulting.com

 

[XaRz]

Iep!

Thanks. Finally I've managed to run the query. Now I must adapt it to run with a combobox.... (another story)

Thanks to all, again.