September 10, 2009 at 2:51 pm
I need help doing a query. Here is a simplified version of what I need to achieve.
I have 2 tables, one with sale information and one storing memos. The table storing memos stores memos for not only sales but for events also. I need to get all sales, even if they don't have a memo and if they do, I need to only get the memo related to the sale (tablename = sale)
Sale Table
SID People Amount
109 2 23.95
110 5 100.65
Memo Table
MID TableName ParentID Memo
20 SALE 109 this is a sale memo
21 EVENT 109 this is an event memo
I need to get the following result
SID People Amount Memo
109 2 23.95 this is a sale memo
110 5 100.65 NULL
Right now I have a query like this:
select sid, people, amount, memo
from sale left outer join memo
on sid = parentid
Unfortunately this gives me the following result
SID People Amount Memo
109 2 23.95 this is a sale memo
109 2 23.95 this is an event memo
110 5 100.65 NULL
Can someone help? I thank you very much in advance.
September 10, 2009 at 3:14 pm
select sid, people, amount, memo
from sale left outer join memo
on sid = parentid
AND memo.TableName = 'SALE'
September 10, 2009 at 3:17 pm
I've tried adding
and Tablename = 'SALE' in my query but if a sale does not have a memo, it will disregard the sale alltogether
Thanks for your help though
September 10, 2009 at 3:21 pm
Zaza (9/10/2009)
I've tried addingand Tablename = 'SALE' in my query but if a sale does not have a memo, it will disregard the sale alltogether
Thanks for your help though
Please show us your code. Since we aren't mind readers, we have no way of knowing what you have actually tried.
September 10, 2009 at 3:22 pm
Make sure that the TableName = 'Sale' is in the ON clause and not in the WHERE clause.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 10, 2009 at 3:23 pm
Zaza (9/10/2009)
I've tried addingand Tablename = 'SALE' in my query but if a sale does not have a memo, it will disregard the sale alltogether
Thanks for your help though
Are you sure you didn't try adding WHERE Tablename = 'SALE'? Cuz that would have filtered out the row as you're saying. Adding it onto the join should work properly unless you have other things going on in the query that we can't see. As Lynn said, it may be easier just to post your code.
September 11, 2009 at 9:15 am
Hi Drew,
Your suggestion worked. My final query looks like this:
select s.id, s.people, s.amount, s.ref, m.recordid, m.tablename, m.txt
from sale as s
left outer join usermemo as m
on s.id = m.recordid and m.tablename = 'SALE'
It disregards records with memos coming from other tables (tablename SALE) and at the same times it also lists sales even if they do no have any memos.
Can you please explain what is the difference putting the statement in the ON clause instead of in the WHERE clause.
Thanks again for your help
September 11, 2009 at 9:18 am
Thanks everyone.. I was putting the AND tablename = 'SALE' in a where clause instead of in the ON clause..
September 11, 2009 at 9:25 am
If you put it in the ON clause of a left join, it only joins that table where the criteria is met, but since it is a left join, your original records are still returned.
If you put it in the where, both types of records make it though the join, but then they're filtered out by your where clause because it applies to the entire query, not a specific table.
Putting criteria like this in your where clause essentially converts your left join to an inner join.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply