Solved

query displaying 2 tables without losing any records

Posted on 2000-04-13
26
138 Views
Last Modified: 2010-05-02
I want to combine two tables into one query, displaying all the records from both tables.
All I get is a query displaying double records or a query filling the wrong fields.
Who can help me?
Please try to answer my question as simple as possible because I’m Dutch.....
0
Comment
Question by:km3
  • 7
  • 7
  • 4
  • +2
26 Comments
 
LVL 28

Expert Comment

by:AzraSound
ID: 2714970
How are the tables related??
0
 

Author Comment

by:km3
ID: 2715045
The tables are relates by "zoeknaam" ("searchname")
One table is a table with sponsors sponsoring money and the other a table with sponsors sponsoring materials. It’s also possible a sponsor sponsors money AND materials.
The query has to display all the records from both tables.
0
 
LVL 1

Expert Comment

by:detiege
ID: 2715061
SELECT * FROM table1
JOIN table2 ON table1.common = table2.common

OR

SELECT * FROM table1,table2
WHERE table1.common1 = table2.common1
AND table1.common2 = table2.common2
      .                .
      .                .
      .                .

This display all records
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 28

Expert Comment

by:AzraSound
ID: 2715062
SELECT * FROM Table1 FULL JOIN Table2 ON Table1.Sponsor = Table2.Sponsor

try that and see if it works
0
 
LVL 1

Expert Comment

by:detiege
ID: 2715065
OOOPS ! ! ! !

Common1 and common2 are the common fields between the tables.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2715070
detiege,
correct me if I'm wrong but a regular join will not include null values. it only includes those records where a match is found.  km3 specified that all records are returned.
0
 
LVL 1

Expert Comment

by:detiege
ID: 2715094
Yes, excuse me !
Replace JOIN by FULL JOIN.

If a row from either table does not match the selection criteria, specifies the row be included in the results set and its output columns that correspond to the other table be set to NULL.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2715098
can you withdraw your answer since mine is the correct solution please
0
 
LVL 1

Expert Comment

by:detiege
ID: 2715148
detiege changed the proposed answer to a comment
0
 

Author Comment

by:km3
ID: 2715167
AzraSound,

When I try a FULL JOIN, I get a Sytaxis-error with the component FROM
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2715175
SELECT * FROM (Table1 FULL JOIN Table2 ON Table1.Sponsor = Table2.Sponsor)

try it with parenthesis

0
 

Author Comment

by:km3
ID: 2715231
It still doesn't work. When I replace FULL for INNER or LEFTor RIGHT etc., I don't get an error but I don't get the right records either.
0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2715246
yes you need full...dont know why its not supported..try one of these then:

SELECT * FROM (Table1 LEFT OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor)


SELECT * FROM (Table1 RIGHT OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor)


0
 
LVL 28

Expert Comment

by:AzraSound
ID: 2715248
however i dont think it will return desired results if it works.
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2715367
Your statement may read:
SELECT * FROM Table1 FULL OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor
 
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2715786
Why don't you use a UNION query like this:

SELECT F1, F2, F3 FROM Table1
UNION
SELECT F1, F2, F3 FROM Table2
ORDER BY F1
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2715825
comment on your comment, emoreau:
1) use rather UNION ALL that simple UNION, because of performance and to avoid duplicate records are eliminated

2) the tables are related by a field, so i don't think this is what he is looking for




0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2715864
angelIII,

The question is: «I want to combine two tables into one query, displaying all the records from both tables.»

This sounds to me a UNION query not a JOIN query!
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2715884
For me, he looks for a list of sponsors (every sponsor listed once, eventually including some details), with the columns [SumOfSponsoredMaterial], [CountOfSponsoredMaterial], [SumOfSponsoredMoney], [CountOfSponsoredMoney]

I think there is no need to "shout" at each other, lets tell km3 what he really wants
0
 

Author Comment

by:km3
ID: 2716076
I tried to use UNION but that doesn’t work either.

There's Table1:

Tabel1ID         SponsorID    Amount
1          1              fl 500,00
2          1              fl 600,00
3          2              fl 100,00

And Table2:

Tabel2ID         SponsorID   Material
1         1             car
2         3             flowers

And my query has to be:

SponsorID    Amount     Material
1           fl 500,00      
1           fl 600,00      
1                  car
2           fl 100,00      
3                  flowers
0
 

Author Comment

by:km3
ID: 2716113
comment on my comment:

In the query, there are three colomns: SponsorID, Amount and Material. In the colomn Amount you should not see the materials and vice versa (It doesn't show that well in my previous comment)
0
 
LVL 70

Accepted Solution

by:
Éric Moreau earned 50 total points
ID: 2716135
You can use this Union Query:

SELECT Table1.SponsorID, Table1.Amount, '' as Material
FROM Table1

UNION

SELECT Table2.SponsorID, 0 as Amount, Table2.Material
FROM Table2

ORDER BY SponsorID;
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 2716411
Please, emoreau, for performance's sake, use UNION ALL !
0
 

Author Comment

by:km3
ID: 2716420
Thanks emoreau, you're great, it works!!!!
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 2716427
Do you accept this now it is working?

You can use this Union Query:

SELECT Table1.SponsorID, Table1.Amount, '' as Material
FROM Table1

UNION ALL

SELECT Table2.SponsorID, 0 as Amount, Table2.Material
FROM Table2

ORDER BY SponsorID;
0
 

Author Comment

by:km3
ID: 2716429
It took me so long to find an answer for this problem, thanks!
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to create a duplicate finder Application 9 122
Excel Automation VBA 19 88
transition to visual .net from vb6 5 37
Fastest way to find and count same items VB6 16 23
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…

856 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