query from 3 tables

vextout · #1 (**permalink**) 11-03-05, 09:09

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

Code:

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/

Thanks

madafaka · #2 (**permalink**) 11-03-05, 10:44

how about to use outer join?

Code:

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];

Peter.Vanroose · #3 (**permalink**) 11-03-05, 12:06

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.

madafaka · #4 (**permalink**) 11-03-05, 15:31

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

madafaka · #5 (**permalink**) 11-03-05, 15:40

Quote:

Originally Posted by Peter.Vanroose

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.

r937 · #6 (**permalink**) 11-03-05, 19:22

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

madafaka · #7 (**permalink**) 11-04-05, 04:36

maybe it works but why use UNION and put together 2 or more selects if you can retrieve your date using one select.

r937 · #8 (**permalink**) 11-04-05, 06:06

why? because it might be way faster, that's why

madafaka · #9 (**permalink**) 11-04-05, 06:38

Quote:

Originally Posted by r937

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:"

Code:

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:"

Code:

SELECT DISTINCT column_name1, column_name2
FROM table_name1
WHERE column_name1 = some_value OR column_name2 = some_value

r937 · #10 (**permalink**) 11-04-05, 07:09

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

madafaka · #11 (**permalink**) 11-04-05, 07:35

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.

Peter.Vanroose · #12 (**permalink**) 11-04-05, 11:13

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 query

Code:

SELECT a.c1, a.c2, b.c2, b.c3
FROM tablea AS a LEFT OUTER JOIN tableb AS b ON a.c2 = b.c2

where a.c2 is a foreign key and b.c2 is the corresponding primary key, is

Code:

SELECT 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 NULL

When a.c2 is not a foreign key, the condition in the second query becomes

Code:

WHERE 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.

vextout · #13 (**permalink**) 11-04-05, 11:59

Whatever you guys decide which way is the best - i know that the way Madafaka first used works for me. So thanks

Internet Services

Web Development

Digital Marketing

Consumer Tech