query Join problem

[Domac]

Hi,

I have following situation :


Query A :

Field1 Field2 Field3
---------------------------
1 A DATA1
2 B DATA2
3 C DATA3
4 D DATA4
5 E DATA5


Query B:

Id Name
---------------
1 Name1
2 Name2
3 Name3
4 Name4


I would like to : LEFT JOIN A.Field1 = B.Id to get five records from table
A and four records from table B and one NULL

Desirable result woud be :

Field1 B.Name Field 2 Field3 Expression1
------------------------------------------------------
1 Name1 A Data1 Exp1
2 Name2 B Data2 Exp2
3 Name3 C Data3 Exp3
4 Name4 D Data4 Exp4
5 NULL E Data5 Exp5
-------------------------------------------------------


I get cartesian product with (5x4) 20 records.
What did I do wrong???


Querys A&B are made using GROUP BY (A.Field3 is Sum grouped on values
A.Field1 and A.Field2)


Please help!


Domagoj

 

[Douglas J. Steele]

Sounds as though you didn't join the two tables in your query.

If you're using the graphical query builder, you need a line between Field1
in Query A and Id in Query B. Once you've drawn the line, select it and
change its properties so that it selects everything from Query A, and those
records that match from Query B. There should be an arrow at the Query B end
of the line after you do that.

In SQL, you'd want:

SELECT A.Field1, B.Name, A.Field2, A.Field3
FROM A LEFT JOIN B
ON A.Field1 = B.Id
ORDER BY A.Field1