We help IT Professionals succeed at work.

JOIN - where clauses on both tables in a view

WaterAid
WaterAid used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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.
you can do this

WITH  CTE1 AS
(select * from table1 where surname = 'smith') ,
CTE2 AS
(select * from table2 WHERE sequence = 1)
select * from CTE1 JOIN CTE2 ON CTE1.Employeeid = CTE2.employeeid

Commented:
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?
Top Expert 2011

Commented:
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)
 

1. to gplana: If you put the condition like "S.SalesItem='Book'" in the "ON" clause then (starting from MSSQL 2005) sometimes it provides wrong result set. It must be moved to WHERE.

2. a = b for OUTER JOIN must be replaced with
((a=b) or (a IS NULL) or (b IS NULL))

- missing (a IS NULL) converts OUTER JOIN to RIGHT JOIN
- missing (b IS NULL) converts OUTER JOIN to LEFT JOIN
- missing (a IS NULL) or (b IS NULL) converts OUTER JOIN to INNER JOIN

3 a = 1 for OUTER JOIN must be replaced with
((a = 1) or (a IS NULL))

If you apply it to your query you will have all rows you are missing.

Author

Commented:
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
Top Expert 2011

Commented:
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...)

Author

Commented:
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.







Top Expert 2011
Commented:
then try it with a from clause like this

from (select * from c  where customercontact = 1) as c
full outer join
       (select * from s where salesman='book') as s
on c.customerid=s.customerid

 

Author

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

Author

Commented:
Apologies to all for not being totally clear with first request!
Top Expert 2011

Commented:
select coalesce(c.customerid,s.customerid) as customerid
      ,....