Show Null as well in a query of related tables...?

O

Offace

Hello Newsgroup,

I have three tables set up as such;

tblFilter:
ID------Name
1 Gender
2 Age

tblFilterValue
ID-----FilterID-----Value-----ValueTag
1 1 1 Male
2 1 2 Female
3 2 1 Teens
4 2 2 Twenties
5 2 3 Thirties
6 2 4 Forties
7 2 5 Fifties
8 2 6 Seniors

tblFilterData
FilterID-----Index-----Value
1 1 1
1 2 1
1 3 1
1 4 2
1 5 2
1 6 2
1 7 2
1 8
1 9 2
1 10 1
2 1 3
2 2 3
2 3 2
2 4 3
2 5 2
2 6
2 7
2 8 2
2 9 4
2 10 1

At the table end the relationships are basically as follows (in classical
1-many fashion);
[tblFilter].[ID]----[tblFilterValue].[FilterID] and
[tblFilter].[ID]----[tblFilterData].[FilterID] with R.I enforced.

I have another relationship without enforcing R.I (many-many);
[tblFilterValue].[Value]----[tblFilterData].[Value].

Via a query the user is able to view data for a specific filter from the
'tblFilterData' table as well as the appropriate value name from
[tblFilterValue].[ValueTag] related table field. The SQL looks like this;

[qselFilterData]
PARAMETERS [filterID] Long;
SELECT [D].[Index], [D].[Value], [V].[ValueTag]
FROM [tblFilterData] AS D INNER JOIN [tblFilterValue] AS V ON ([D].[Value] =
[V].[Value]) AND ([D].[FilterID] = [V].[FilterID])
WHERE ([V].[FilterID]=filterID)
ORDER BY [D].[Index] ASC;

This query does the job but NULL values steaming from the 'tblFilterData'
table don't follow through into the query. How do I acheive (sorry about
the
long post) this;

[qselFilterData] :: WHERE ([FilterID]=2)
Index-----Value----ValueTag
1 3 Thirties
2 3 Thirties
3 2 Twenties
4 3 Thirties
5 2 Twenties
6
7
8 2 Twenties
9 4 Forties
10 1 Teens

I.e the NULL values still appear in the query, rather than records 6 and 7
not coming through. Many thanks in advance all help is deeply appreciated.

Regards,
Offace.
 
K

Ken Snell [MVP]

To show records in one table that have no match in a linked table, use a
LEFT JOIN (or RIGHT JOIN, depending upon how the query is written):

PARAMETERS [filterID] Long;
SELECT [D].[Index], [D].[Value], [V].[ValueTag]
FROM [tblFilterData] AS D LEFT JOIN [tblFilterValue] AS V ON ([D].[Value] =
[V].[Value]) AND ([D].[FilterID] = [V].[FilterID])
WHERE ([V].[FilterID]=filterID)
ORDER BY [D].[Index] ASC;
--

Ken Snell
<MS ACCESS MVP>


Offace said:
Hello Newsgroup,

I have three tables set up as such;

tblFilter:
ID------Name
1 Gender
2 Age

tblFilterValue
ID-----FilterID-----Value-----ValueTag
1 1 1 Male
2 1 2 Female
3 2 1 Teens
4 2 2 Twenties
5 2 3 Thirties
6 2 4 Forties
7 2 5 Fifties
8 2 6 Seniors

tblFilterData
FilterID-----Index-----Value
1 1 1
1 2 1
1 3 1
1 4 2
1 5 2
1 6 2
1 7 2
1 8
1 9 2
1 10 1
2 1 3
2 2 3
2 3 2
2 4 3
2 5 2
2 6
2 7
2 8 2
2 9 4
2 10 1

At the table end the relationships are basically as follows (in classical
1-many fashion);
[tblFilter].[ID]----[tblFilterValue].[FilterID] and
[tblFilter].[ID]----[tblFilterData].[FilterID] with R.I enforced.

I have another relationship without enforcing R.I (many-many);
[tblFilterValue].[Value]----[tblFilterData].[Value].

Via a query the user is able to view data for a specific filter from the
'tblFilterData' table as well as the appropriate value name from
[tblFilterValue].[ValueTag] related table field. The SQL looks like this;

[qselFilterData]
PARAMETERS [filterID] Long;
SELECT [D].[Index], [D].[Value], [V].[ValueTag]
FROM [tblFilterData] AS D INNER JOIN [tblFilterValue] AS V ON ([D].[Value] =
[V].[Value]) AND ([D].[FilterID] = [V].[FilterID])
WHERE ([V].[FilterID]=filterID)
ORDER BY [D].[Index] ASC;

This query does the job but NULL values steaming from the 'tblFilterData'
table don't follow through into the query. How do I acheive (sorry about
the
long post) this;

[qselFilterData] :: WHERE ([FilterID]=2)
Index-----Value----ValueTag
1 3 Thirties
2 3 Thirties
3 2 Twenties
4 3 Thirties
5 2 Twenties
6
7
8 2 Twenties
9 4 Forties
10 1 Teens

I.e the NULL values still appear in the query, rather than records 6 and 7
not coming through. Many thanks in advance all help is deeply appreciated.

Regards,
Offace.
Click to expand...
 
O

Offace

You beaut Ken. Thanks heaps! and just one more thing for all out there with
a similar issue, looking at the query below change,
WHERE ([V].[FilterID]=filterID), to WHERE ([D].[FilterID]=filterID), and all
will work. Much appreciated Ken and thanks alot once again.

Regards,
Offace.

: To show records in one table that have no match in a linked table, use a
: LEFT JOIN (or RIGHT JOIN, depending upon how the query is written):
:
: PARAMETERS [filterID] Long;
: SELECT [D].[Index], [D].[Value], [V].[ValueTag]
: FROM [tblFilterData] AS D LEFT JOIN [tblFilterValue] AS V ON ([D].[Value]
=
: [V].[Value]) AND ([D].[FilterID] = [V].[FilterID])
: WHERE ([V].[FilterID]=filterID)
: ORDER BY [D].[Index] ASC;
: --
:
: Ken Snell
: <MS ACCESS MVP>
:
:
: : > Hello Newsgroup,
: >
: > I have three tables set up as such;
: >
: > tblFilter:
: > ID------Name
: > 1 Gender
: > 2 Age
: >
: > tblFilterValue
: > ID-----FilterID-----Value-----ValueTag
: > 1 1 1 Male
: > 2 1 2 Female
: > 3 2 1 Teens
: > 4 2 2 Twenties
: > 5 2 3 Thirties
: > 6 2 4 Forties
: > 7 2 5 Fifties
: > 8 2 6 Seniors
: >
: > tblFilterData
: > FilterID-----Index-----Value
: > 1 1 1
: > 1 2 1
: > 1 3 1
: > 1 4 2
: > 1 5 2
: > 1 6 2
: > 1 7 2
: > 1 8
: > 1 9 2
: > 1 10 1
: > 2 1 3
: > 2 2 3
: > 2 3 2
: > 2 4 3
: > 2 5 2
: > 2 6
: > 2 7
: > 2 8 2
: > 2 9 4
: > 2 10 1
: >
: > At the table end the relationships are basically as follows (in
classical
: > 1-many fashion);
: > [tblFilter].[ID]----[tblFilterValue].[FilterID] and
: > [tblFilter].[ID]----[tblFilterData].[FilterID] with R.I enforced.
: >
: > I have another relationship without enforcing R.I (many-many);
: > [tblFilterValue].[Value]----[tblFilterData].[Value].
: >
: > Via a query the user is able to view data for a specific filter from the
: > 'tblFilterData' table as well as the appropriate value name from
: > [tblFilterValue].[ValueTag] related table field. The SQL looks like
this;
: >
: > [qselFilterData]
: > PARAMETERS [filterID] Long;
: > SELECT [D].[Index], [D].[Value], [V].[ValueTag]
: > FROM [tblFilterData] AS D INNER JOIN [tblFilterValue] AS V ON
([D].[Value]
: =
: > [V].[Value]) AND ([D].[FilterID] = [V].[FilterID])
: > WHERE ([V].[FilterID]=filterID)
: > ORDER BY [D].[Index] ASC;
: >
: > This query does the job but NULL values steaming from the
'tblFilterData'
: > table don't follow through into the query. How do I acheive (sorry
about
: > the
: > long post) this;
: >
: > [qselFilterData] :: WHERE ([FilterID]=2)
: > Index-----Value----ValueTag
: > 1 3 Thirties
: > 2 3 Thirties
: > 3 2 Twenties
: > 4 3 Thirties
: > 5 2 Twenties
: > 6
: > 7
: > 8 2 Twenties
: > 9 4 Forties
: > 10 1 Teens
: >
: > I.e the NULL values still appear in the query, rather than records 6 and
7
: > not coming through. Many thanks in advance all help is deeply
: appreciated.
: >
: > Regards,
: > Offace.
: >
: >
:
:
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting Nulls as well in a select distinct... 4
Scrabble Value calculation for Welsh words 0
Fonction DMax 0
combining tables into query 2
Query 6
how to solve the following in excel 1
Help with countifs 1
how to combine each record into a range? 3

Top