query from 3 tables

hi,

I have 3 tables
[Credit Memo - Expense Details] - which i refer to CMED
[Credit Memo - Project Change Request] - which i refer to CMPCR
[Credit Memo Table] - which i refer to CM

CM is connected to CMED by 1 field: CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])

CM is connected to CMPCR by 1 field: CM.[PCR # (if applicable)] = CMPCR.[PCR Number]

CMED and CMPCR are not related

My goal query: show all the fields in CM (and (if invoice columns match in CMED, show that) or (if pcr number columns match in CMPCR, show that) )

So if there is only a match with CMPCR the CMED fields should be blank.

I am thinking of a union but just couldn't get it.

Below is the code to get all the records where CM invoice matches AND pcr matches
i need it to be OR

SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM INNER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
INNER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];

MS access 2003 - i hate it but i jsut came on board and this is already setup/

Thankshow about to use outer join?

SELECT
CM.[Project Name], CM.[Credit Memo #], CM.[Date of Credit Memo Request], CM.[Date of Credit Receipt], CM.[PO Number], CM.[PCR # (if applicable)], CM.[# of Units], CM.[Unit Cost], CM.[Net Cost], CM.Tax, CM.Freight, CM.[Total Credit], CM.Description, CM.[Credit Applied?], CM.[Invoice # Credit is applied against (if applicable)], CM.Comments,
CMPCR.[PCR Number], CMPCR.[DC Number], CMPCR.Category, CMPCR.[Sub-Category], CMPCR.[Building Location / Floor], CMPCR.[Vendor(s) Impacted (if any)],
CMED.[Invoice # / Expenses Reference #], CMED.Company, CMED.[DC Number], CMED.[PO Number], CMED.[Cost Center / Billing Code], CMED.[Building Location / Floor], CMED.Category, CMED.[Sub-Category], CMED.Transaction, CMED.[Sub-Transaction]
FROM
([Credit Memo Table] CM LEFT OUTER JOIN [Credit Memo - Expense Details] CMED ON CM.[Invoice # Credit is applied against (if applicable)] = CMED.[Invoice # / Expenses Reference #])
LEFT OUTER JOIN [Credit Memo - Project Change Request] CMPCR ON CM.[PCR # (if applicable)] = CMPCR.[PCR Number];|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.|||you mentioned the database is ms access, I believe there's equivalent of OUTER JOIN in Access. I'm not sure but I think it's LEFT JOIN instead of LEFT OUTER JOIN|||On systems not supporting (LEFT) OUTER JOINs, those may indeed be simulated with a UNION.
The fist part is then the corresponding INNER JOIN, while the second part only interrogates the first table, with an additional WHERE condition "IS NULL" on the join column.

by the way, this solution can't work. idea is OK but you have to use cartesian product instead of inner join. then using combination of AND, OR conditions defined in WHERE clause you'll retrieve what you need without using UNION. but for now forget this solution and try to find equivalent of LEFT OUTER JOIN in your DB server.|||i disagree, madafaka

peter's union suggestion works perfectly, you should try it

it's also possible to simulate a full outer join with a (suitably coded) union of left and right joins|||maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.|||why? because it might be way faster, that's why :)|||why? because it might be way faster, that's why :)
Now you're wrong. The performance is the reason I avoid using UNION.

"When using the UNION statement, keep in mind that, by default, it performs the equivalent of a SELECT DISTINCT on the final result set. In other words, UNION takes the results of two like recordsets, combines them, and then performs a SELECT DISTINCT in order to eliminate any duplicate rows. This process occurs even if there are no duplicate records in the final recordset."

"Sometimes you might want to merge two or more sets of data resulting from two or more queries using UNION. For example:"

SELECT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value
UNION
SELECT column_name1, column_name2
FROM table_name1
WHERE column_name2 = some_value

"This same query can be rewritten, like the following example, and when doing so, performance will be boosted:"

SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value|||dude, we were talking about a UNION to simulate a LEFT OUTER JOIN, versus your suggestion of a cartesian product to simulate a LEFT OUTER JOIN|||ok, I didn't realise this. but this solutions are silly anyway. I can't immagine how those select statements would look like, if you're joining 3 tables and you can't use OUTER JOIN. I believe there must be some OUTER JOIN equivalent in every standard SQL database.|||Just a reply to some of the topics mentioned in previous posts:

- Using "UNION ALL" instead of "UNION" avoids the "SELECT DISTINCT" performance overhead; in that case, a "UNION" emulation of an OUTER JOIN is in principle equally performant. (Most of the time, OUTER JOIN will be a bit more performant, since only a single pass has to be made through the left table, but in rare cases the UNION ALL solution may be more performant, especially when lots of rows have no matching row).

- There are SQL database systems lacking the OUTER JOIN syntax, especially older versions: e.g. Oracle before version 8, DB2 before version 6. Nobody uses these nowadays, but who knows ...

- Performance is not necessarily boosted when using "OR" instead of "UNION ALL" !
To the contrary: the two queries in a UNION ALL may use indexes, while as a rule-of-thumb an OR condition never uses indexed access.

- The equivalent of the querySELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2where a.c2 is a foreign key and b.c2 is the corresponding primary key, isSELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a, tableb AS b WHERE a.c2 = b.c2
UNION ALL
SELECT a.c1, a.c2, NULL, NULL
FROM tablea AS a WHERE a.c2 IS NULLWhen a.c2 is not a foreign key, the condition in the second query becomesWHERE NOT EXISTS (SELECT 1 FROM tableb WHERE c2 = a.c2)

- A FULL OUTER JOIN can always be emulated with a "UNION ALL" of three queries, one on the inner join, one on the first table (as above), and one on the second table.|||Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thankssql