[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 351
  • Last Modified:

database query displaying more than i want

I have 3 tables in a  MS Access97 database. I created a query where table 1 contains a one to many relationship to table 2 and table 3.

In this query I am displaying the Primary Key from table 1 and a field from table 2 and a field from table 3. Therefore the final outcome of the qery may look like this.

PK 3      Field 2      Field 1
1      C      B
1      C      A
1      D      B
1      D      A
1      E      B
1      E      A

The SQL for the above is:

SELECT Table3.[PK 3], Table2.[Field 2], Table1.[Field 1]
FROM (Table3 INNER JOIN Table2 ON Table3.[PK 3] = Table2.[FK 2]) INNER JOIN Table1 ON Table3.[PK 3] = Table1.[FK 1];

However, what I am trying to achieve is this:

PK 3      Field 2      Field 1
1      C      
1      D      
1      E      
1            A
1            B

How can I modify the design of the query to achieve this result?

Kind regards

Gilmac            
0
gilmac
Asked:
gilmac
1 Solution
 
daczCommented:
try this SQL query:

SELECT DISTINCT Table3.[PK 3], Table2.[Field 2], '' AS [Field 1]
FROM Table2 INNER JOIN Table3 ON  Table2.[FK 2] =Table3.[PK 3]

UNION

SELECT DISTINCT Table3.[PK 3], '' AS [Field 2], Table1.[Field 1]
FROM Table1  INNER JOIN Table3 ON Table1.[FK 1] =  Table3.[PK 3]
0
 
AristoCommented:
Hello Gilmac,

You should use a union query. Write a manual query in Access like this:

SELECT Table3.[PK 3], Table2.[Field 2], NULL as [Field 1]
FROM (Table3 INNER JOIN Table2 ON Table3.[PK 3] = Table2.[FK 2])
UNION ALL
SELECT Table3.[PK 3], NULL AS [Field 2], Table1.[Field 1]
FROM (Table3 INNER JOIN Table1 ON Table3.[PK 3] = Table1.[FK 1]);
0
 
mnrzCommented:
Hi

would you tell us the result you want to achieve again because the thing I see is not correct
and also show the date of tables 1 and 2 and 3.

I am not familiar with MS Access but
test this:
SELECT T3.[PK 3], T2.[Field 2], T1.[Field 1]
FROM Table3 t3, Table2 t2, Table1 t1
where
t3.pk3 = t2.fk2 and
t3.pk3 = t1.fk1

0
 
gilmacAuthor Commented:
thank you dacz and everyone else. the solution from dacz works fine, so i'll give him the points.

however, i simplified the problem in hope of understanding how it works and then apply it to real life problem. however, i found that i am struggling to understand it, so i'll raise another question with the real problem, which has more tables and is a little bit more complicated.

if you could please have a look at that and help me with it, it would be much appreciated.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now