Join queries in one, ACCESS

0

I have these questions:

consultaA:

SELECT DISTINCT t1.FK_HD, t1.DE_rfc
FROM tbl_DE t1
WHERE (((t1.FK_HD)=105))
GROUP BY t1.FK_HD, t1.DE_rfc
HAVING (((Sum(t1.DE_cantidad))<>0))
ORDER BY t1.DE_rfc;

consultaB:

SELECT DISTINCT t2.FK_HD, t2.DE_rfc
FROM tbl_DE t2
WHERE (((t2.FK_HD)=106));

From the two consultations I make a comparison using the name of the same.

Comparacion

SELECT consultaA.DE_rfc
FROM consultaA LEFT JOIN consultaB ON consultaA.DE_rfc = consultaB.DE_rfc
WHERE (((consultaB.DE_rfc) Is Null))

This query returns 48 records.

For reasons of my application I have to make ONE SINGLE query and I have tried it this way:

SELECT DE_rfc FROM
(SELECT DISTINCT t1.FK_HD, t1.DE_rfc
FROM tbl_DE t1
WHERE (((t1.FK_HD)=105))
GROUP BY t1.FK_HD, t1.DE_rfc
HAVING (((Sum(t1.DE_cantidad))<>0))) a LEFT JOIN (SELECT DISTINCT t2.FK_HD, t2.DE_rfc
FROM tbl_DE t2
WHERE (((t2.FK_HD)=106))) b ON a.DE_rfc = b.DE_rfc WHERE (((b.DE_rfc) Is Null)) 

But I do not throw any results, I hope you can guide me what is my mistake because I can not decipher it.

    
asked by SdeSistemas 17.12.2018 в 17:00
source

1 answer

2

The only difference I see between your query comparación and the last one is:

SELECT consultaA.DE_rfc ...

SELECT DE_rfc FROM..

The conflict could be that DE_rfc you have in both queries (A and B) then you have to specify which query you would have to obtain the column.

Try this and say that:

SELECT a.DE_rfc FROM
(SELECT DISTINCT t1.FK_HD, t1.DE_rfc
FROM tbl_DE t1
WHERE (((t1.FK_HD)=105))
GROUP BY t1.FK_HD, t1.DE_rfc
HAVING (((Sum(t1.DE_cantidad))<>0))) a LEFT JOIN (SELECT DISTINCT t2.FK_HD, t2.DE_rfc
FROM tbl_DE t2
WHERE (((t2.FK_HD)=106))) b ON a.DE_rfc = b.DE_rfc WHERE (((b.DE_rfc) Is Null)) 
    
answered by 17.12.2018 / 17:51
source