Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

database query displaying more than i want

Posted on 2006-10-30
4
Medium Priority
?
343 Views
Last Modified: 2008-02-01
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
Comment
Question by:gilmac
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 1

Accepted Solution

by:
dacz earned 2000 total points
ID: 17840069
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
 
LVL 1

Expert Comment

by:Aristo
ID: 17840130
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
 
LVL 8

Expert Comment

by:mnrz
ID: 17840141
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
 

Author Comment

by:gilmac
ID: 17847631
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

Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What we learned in Webroot's webinar on multi-vector protection.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question