km3
asked on
query displaying 2 tables without losing any records
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.....
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.....
How are the tables related??
ASKER
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.
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.
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
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
SELECT * FROM Table1 FULL JOIN Table2 ON Table1.Sponsor = Table2.Sponsor
try that and see if it works
try that and see if it works
OOOPS ! ! ! !
Common1 and common2 are the common fields between the tables.
Common1 and common2 are the common fields between the tables.
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.
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.
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.
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.
can you withdraw your answer since mine is the correct solution please
detiege changed the proposed answer to a comment
ASKER
AzraSound,
When I try a FULL JOIN, I get a Sytaxis-error with the component FROM
When I try a FULL JOIN, I get a Sytaxis-error with the component FROM
SELECT * FROM (Table1 FULL JOIN Table2 ON Table1.Sponsor = Table2.Sponsor)
try it with parenthesis
try it with parenthesis
ASKER
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.
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)
SELECT * FROM (Table1 LEFT OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor)
SELECT * FROM (Table1 RIGHT OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor)
however i dont think it will return desired results if it works.
Your statement may read:
SELECT * FROM Table1 FULL OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor
SELECT * FROM Table1 FULL OUTER JOIN Table2 ON Table1.Sponsor = Table2.Sponsor
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
SELECT F1, F2, F3 FROM Table1
UNION
SELECT F1, F2, F3 FROM Table2
ORDER BY F1
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
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
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!
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!
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
I think there is no need to "shout" at each other, lets tell km3 what he really wants
ASKER
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
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
ASKER
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)
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)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Please, emoreau, for performance's sake, use UNION ALL !
ASKER
Thanks emoreau, you're great, it works!!!!
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;
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;
ASKER
It took me so long to find an answer for this problem, thanks!