SQLTeam.com | Weblogs | Forums

Select statement


#1

I have a history table that holds items that are sold to a customer and the vendor the item was ordered from.

Trying to get a list of Vendors that show Customer numbers that have not ordered from them.

Table: oelinhst
I'm using '_" as spaces since I can't seem to figure out how to have tabbed spaces in this forum.
cus_no_______Vendor
100_________500
200_________600

I would want my select statement to return.

Vendor________Cus_no
500 ____________200
600_____________100


#2

Edit: Sorry, misread the q originally.


#3

Something like this:

with oelinst as
(
select cus_no, vendor from (values
(100, 500),
(200, 600),
(200, 500),
(100, 400)
) v(cus_no, vendor)
)

select distinct b.vendor, a.cus_no
from oelinst a
cross apply
(
select c.vendor
from oelinst c
where c.cus_no <> a.cus_no
and c.vendor not in
( select vendor
from oelinst d
where d.cus_no = a.cus_no
)
) b
order by b.vendor, a.cus_no


#4
SELECT c.cus_no, v.vendor
FROM (
    SELECT DISTINCT o.cus_no
    FROM oelinst o
) AS c
CROSS JOIN (
    SELECT DISTINCT o.vendor
    FROM oelinst o
) AS v
WHERE NOT EXISTS(SELECT 1 FROM oelinst o WHERE o.cus_no = c.cus_no AND o.vendor = v.vendor)

#5

Scott,
Your script was the one that worked. Thanks,