Link to home
Start Free TrialLog in
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.

 

 User generated image
Avatar of gplana
gplana
Flag of Spain image

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
Avatar of deighton
deighton
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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)
 

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of WaterAid
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!


  User generated image User generated image User generated image
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...)
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.







ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
Apologies to all for not being totally clear with first request!
select coalesce(c.customerid,s.customerid) as customerid
      ,....