Avatar of WaterAid
WaterAid
 asked on

JOIN - where clauses on both tables in a view

Hi...

I'm looking to build a view which brings in specific rows from the two tables that are being joined (using a FULL OUTER JOIN)

I would normally use a WHERE clause in these circumstances but that doesn't seem to work, and neither does appending the condition to the JOIN syntax using AND (a=b OR y=z).

Can anyone help?

I've tried to summarise below what I'm looking to achieve in this image...  

PS I'm running SQL 2000.

 

 JOIN nirvana
Microsoft SQL Server

Avatar of undefined
Last Comment
Lowfatspread

8/22/2022 - Mon
gplana

Try this:

SELECT *
FROM C FULL OUTER JOIN S ON C.CustomerID = S.CustomerID AND (C.CustomerContacted=1 OR S.SalesItem='Book');

Hope it helps.
SOLUTION
deighton

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
dqmq

The sql in your graphic seems correct, just not sure why you need full outer join.  Have you tried this:

select c.customerid, c.customercontacted, s.salesid, s.salesitem
dbo.c left join dbo.s on c.customerid = s.customerid
where C.CustomerContacted=1 OR S.SalesItem='Book'

Can you describe what you are getting that is wrong?
Lowfatspread

that is because

in the where clause you have not allowed for the case where the other tables data is missing...

from c  full outer join s
on c.customerid=s.customerid
where (c.customercontact = 1 or c.customercontact is null)  
or (s.salesman='Book' or s.salesman is null)
 

Your help has saved me hundreds of hours of internet surfing.
fblack61
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WaterAid

ASKER
Hi - thanks for your answers!

Sorry, I'm not sure I provided enough detail of the outcome I'm looking for...  Would you mind taking a further look at the revised snapshots below?

This syntax gets me almost to where I want to be...  BUT, is there any way I can get the view to contain only the data I'm filtering on?  See Preferred View...

Thanks again!


  Tables Current View Preferred View
Lowfatspread

no it is not clear what you intend...

give us a statement as to what you are trying to achieve ...

in business english , without specific reference to the underlying tables...


e.g.

I want to list customers who i have contacted , or customers to whom a sale have been made for a specific item.....

(it is very unclear wether you want a customer list or a list of customer/sales...)
WaterAid

ASKER
Sure...  Your example is exactly that I am after.

I want a table detailing both customers who have been contacted and sales that have been achieved for specific item.  There may be some customers who have purchased the specific item but who have not been contacted and there are some who have been contacted but not purchased the specific item.  And of course there are those who have been contacted AND purchased the specific item.







Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
WaterAid

ASKER
Spot on - thanks lowfatspread!  Much appreciated.  Thanks also to everyone else who contributed to the solution.

Here's the complete SQl should anyone ever want to reference this:

SELECT     c.CustomerID, c.CustomerContacted, s.SalesID, s.CustomerID AS Expr1, s.SalesItem, s.SalesItemDetail
FROM         (SELECT     *
                       FROM          customer
                       WHERE      customercontacted = 1) c FULL OUTER JOIN
                          (SELECT     *
                            FROM          sales
                            WHERE      salesitem = 'book' AND SalesItemDetail = 'Blue') s ON c.CustomerID = s.CustomerID
WaterAid

ASKER
Apologies to all for not being totally clear with first request!
Lowfatspread

select coalesce(c.customerid,s.customerid) as customerid
      ,....
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23