Non exclusive filtered INNER JOIN......Rookie

  • Thread starter Thread starter Atlas
  • Start date Start date
A

Atlas

I have two tables:

A) ID_A, fieldx, fieldy, fieldz

B) ID_B, ID_A, fieldDate, fieldu,fildv, fieldw

relationship: 1 to many

I would like to build a query that selects all records from A and (IF ANY,
non exclusive!!!!) records from B meeting date range. Unfortunatelly using
the query below, keeps out from results all parents from A with no childs in
B.

SELECT A.*, B.*
FROM A
INNER JOIN B ON A.ID_A = B.ID_A
WHERE (B.fieldDate >= CONVERT(DATETIME, '01-01-2004 00:00:00', 105) AND
(B.fieldDate <= CONVERT(DATETIME, '31-12-2004 00:00:00', 105)


What can I try?
 
If you simply query against table B with that criteria, do you get any
records back?
 
Douglas J. Steele said:
If you simply query against table B with that criteria, do you get any
records back?
Click to expand...
Yes, only those who met that condition.

The problem I'm facing is that each record in A can, have multiple childs
in B; but also it can happen that A hasn't got any child at all.
I want to select all records from A, and if any in B only those that meet
date range. But sticking date range in the WHERE clause, prevents others
records in A to be returned.

C
 
See whether the following works for you:

SELECT A.*, Filtered.*
FROM A
INNER JOIN
(SELECT * FROM B
WHERE (B.fieldDate >= CONVERT(DATETIME, '01-01-2004 00:00:00', 105) AND
(B.fieldDate <= CONVERT(DATETIME, '31-12-2004 00:00:00',105))
AS Filtered
ON A.ID_A = Filtered.ID_A
 
See whether the following works for you:
WHOOOAAAAAA!!!!!!!!!

The power of knowledge (and skill!!!!)

It works!
 
Back
Top