• each company has a unique id


• each contact is identified by company and a sequential number


• the contact sequence starts from 1 for each company's contacts

-- create temp tables for testing
BEGIN
CREATE TABLE	#cust_company	(
		company_id	INT	IDENTITY	NOT NULL
	,	company_name	NVARCHAR(50)	NOT NULL
		)	
END

BEGIN
CREATE TABLE	#cust_contact	(
		company_id	INT	NOT NULL
	,	contact_no	INT	NOT NULL
	,	contact_name	NVARCHAR(50)	NOT NULL
		)
END

BEGIN
CREATE TABLE	#order_header	(
		order_id	INT	IDENTITY	NOT NULL
	,	company_id	INT	NOT NULL
	,	contact_no	INT	NOT NULL
		)
END

-- insert sample data
-- sample companies
BEGIN
	INSERT INTO	#cust_company	(company_name)
	SELECT	company_name	=	'ABC Company'
	UNION ALL
	SELECT	company_name	=	'DEF Company'
	UNION ALL
	SELECT	company_name	=	'GHI Company'
END

-- sample contacts
BEGIN
	INSERT INTO	#cust_contact	(company_id, contact_no, contact_name)
	SELECT	company_id = 1, contact_no = 1, contact_name = 'Joe Smith'
	UNION ALL
	SELECT	company_id = 1, contact_no = 2, contact_name = 'Jane Grey'
	UNION ALL
	SELECT	company_id = 2, contact_no = 1, contact_name = 'Tom Jones'
	UNION ALL
	SELECT	company_id = 3, contact_no = 1, contact_name = 'Mary Thompson'
	UNION ALL
	SELECT	company_id = 3, contact_no = 2, contact_name = 'Larry Watts'
END

-- sample orders for random contacts
BEGIN
	DECLARE	@Count	INT
	SET	@Count	=	1
	WHILE	@Count	<=	4
	BEGIN
		INSERT INTO	#order_header	(company_id, contact_no)
			SELECT TOP 1 company_id = c.company_id, contact_no = c.contact_no
			FROM	#cust_contact	c
			ORDER BY	NewID()
		SET	@Count	=	@Count + 1
	END
END

-- return contacts who have placed orders
BEGIN
	SELECT
			cu.company_name
		,	co.contact_name
		,	orders	=	count(*)
	FROM
			#order_header	o
		,	#cust_company	cu
		,	#cust_contact	co
	WHERE
			o.company_id	=	cu.company_id
	AND
			o.company_id	=	co.company_id
	AND
			o.contact_no	=	co.contact_no
	GROUP BY
			cu.company_name
		,	co.contact_name
	ORDER BY
			count(*)	DESC
END

-- clean up
BEGIN
	DROP TABLE	#cust_company
END

BEGIN
	DROP TABLE	#cust_contact
END

BEGIN
	DROP TABLE	#order_header
END

	SELECT DISTINCT
			cu.company_name
		,	co.contact_name
	FROM
			#order_header	o
		,	#cust_company	cu
		,	#cust_contact	co
	WHERE
			o.company_id	=	cu.company_id
	AND
			o.company_id	=	co.company_id
	AND
			o.contact_no	=	co.contact_no


	SELECT 
			cu.company_name
		,	co.contact_name
	FROM
			#cust_company	cu
		,	#cust_contact	co
	WHERE
			co.company_id	=	cu.company_id
	AND
			NOT EXISTS (SELECT * FROM #order_header o WHERE o.company_id = cu.company_id)

SELECT
			cu.company_name
		,	co.contact_name
		,	orders	=	count(o.order_id)
	FROM
			#order_header	o
			join #cust_company	cu on o.company_id	=	cu.company_id
		right join #cust_contact	co on o.company_id	=	co.company_id
									and o.contact_no	=	co.contact_no
	WHERE
		o.company_id is null and o.contact_no is null
	GROUP BY
			cu.company_name
		,	co.contact_name
	ORDER BY
			count(o.order_id)	DESC

WHERE co.company_id, co.contact_no NOT IN (SELECT company_id, contact_no FROM #order_header)

-- return contacts who have not placed orders
BEGIN
	SELECT
			cu.company_name
		,	co.contact_name
		,	orders	=	count(o.order_id)
	FROM
			#cust_company	cu
		,	#cust_contact	co
	LEFT JOIN	#order_header	o
		ON	co.company_id	=	o.company_id
		AND	co.contact_no	=	o.contact_no
	WHERE
			cu.company_id	=	co.company_id
	AND
			o.company_id	IS NULL
	AND
			o.contact_no	IS NULL
	GROUP BY
			cu.company_name
		,	co.contact_name
	ORDER BY
			count(o.order_id)	DESC
END

quote:

---

snSQL,

Your query sometimes returns no results, so it cannot be correct

---

quote:

---

Originally posted by CorpDirect

Harsh Athalye,

Looks like you were on target with the outer join. I worked with your concept to come up with a query that seems to give company name and contact name for contacts who have not ordered. Please take a look and let me know what you think.

-- return contacts who have not placed orders
BEGIN
	SELECT
			cu.company_name
		,	co.contact_name
		,	orders	=	count(o.order_id)
	FROM
			#cust_company	cu
		,	#cust_contact	co
	LEFT JOIN	#order_header	o
		ON	co.company_id	=	o.company_id
		AND	co.contact_no	=	o.contact_no
	WHERE
			cu.company_id	=	co.company_id
	AND
			o.company_id	IS NULL
	AND
			o.contact_no	IS NULL
	GROUP BY
			cu.company_name
		,	co.contact_name
	ORDER BY
			count(o.order_id)	DESC
END

Also if anyone knows an alternate way to do this, please share -- options are good!

Thank you,

Daniel

*Edit - code cleanup

---

BEGIN
	SELECT
			cu.company_name
		,	co.contact_name
		,	orders	=	count(o.order_id)
	FROM
			#cust_company	cu
		Join	#cust_contact	co 
                on cu.company_id = co.company_id	LEFT JOIN	#order_header	o
		ON	co.company_id	=	o.company_id
		AND	co.contact_no	=	o.contact_no
	WHERE
			o.company_id	IS NULL
	AND
			o.contact_no	IS NULL
	GROUP BY
			cu.company_name
		,	co.contact_name
	ORDER BY
			count(o.order_id)	DESC
END

quote:

---

Originally posted by snSQL
Based on your example I assumed that an order always had a customer and a contact, if that is not the case then you will need to use the outer join and your original example must also not always return results.

---

quote:

---

Originally posted by harsh_athalye
Looks fine to me!

One thing is you can use ANSI join to avoid the risk of inadvertently having cross join.

---

-- implicit join
FROM
		table1	t1
	,	table2	t2
WHERE
		t1.colA	=	t2.colA

-- explicit join
FROM
		table1	t1
	JOIN	table2	t2
		ON	t1.colA	=	t2.colA

quote:

---

Originally posted by CorpDirect

quote:

---

Originally posted by snSQL
Based on your example I assumed that an order always had a customer and a contact, if that is not the case then you will need to use the outer join and your original example must also not always return results.

---

Every order DOES have a customer and contact. I'm looking for the set of contacts who have NOT ordered. Your query returned no records each time...

---

SELECT 
			cu.company_name
		,	co.contact_name
	FROM
			#cust_company	cu
		,	#cust_contact	co
	WHERE
			co.company_id	=	cu.company_id
	AND
			NOT EXISTS (SELECT * FROM #order_header o WHERE o.company_id = cu.company_id AND o.contact_no = co.contact_no)

quote:

---

Originally posted by CorpDirect

quote:

---

Originally posted by harsh_athalye
Looks fine to me!

One thing is you can use ANSI join to avoid the risk of inadvertently having cross join.

---

Do you mean so that you don't forget to specify join criteria? Seems to me that these two are the same:

-- implicit join
FROM
		table1	t1
	,	table2	t2
WHERE
		t1.colA	=	t2.colA

-- explicit join
FROM
		table1	t1
	JOIN	table2	t2
		ON	t1.colA	=	t2.colA

The difference is just one of semantics, right? No difference in their handling/processing by SQL Server?

So I assume you are suggesting using JOIN so that you will follow with ON and remember to define the relationship. Please correct me if I'm missing something!

Thanks,

Daniel

---

quote:

---

Originally posted by snSQL
Ah, OK then just adjust the WHERE clause to include the contact

WHERE
		NOT EXISTS (SELECT * FROM #order_header o WHERE o.company_id = cu.company_id AND o.contact_no = co.contact_no)

This is not necessarily better or worse than the join option, you'd need to test.

---