Count problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Count problem

Author

Topic

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-22 : 11:17:11

I have the following statement. Everything works fine apart from I am getting multiple lines for a supplier where I only want one


select sc.supplier_contact_title, sc.supplier_contact_fname, sc.supplier_contact_lname, sc.supplier_contact_email,  tbs.supplier_name
	from tblsuppliercontact sc, tblsupplier tbs
	where tbs.supplier_name in ( select distinct d.sup_name
					from tbl_documents d
					where (d.created > getdate()-62) and d.docType = 13
	)
	and sc.supplier_id = tbs.supplier_id and sc.supplier_contact_email <> '' and
	(select count(supplier_id) from tblsupplier where sc.supplier_id = supplier_id) <=1
	order by supplier_name, sc.supplier_contact_lname, sc.supplier_contact_fname

Can you see what is wrong with it? Weary eyes now

MichaelP
Jedi Yak

2489 Posts

Posted - 2004-12-22 : 11:27:37

I think you might want a GROUP BY on this instead of that COUNT thing.
It might be helpful if you post some sample data and sample desired result set.

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-22 : 11:29:26

i think this should be it... if not post some ddl, dml, and desired result


select 	sc.supplier_contact_title, sc.supplier_contact_fname, sc.supplier_contact_lname, 
	sc.supplier_contact_email, tbs.supplier_name
from 	tblsuppliercontact sc
	inner join tblsupplier tbs on sc.supplier_id = tbs.supplier_id and sc.supplier_contact_email <> ''
	inner join (select d.sup_name
			from tbl_documents d
			where (d.created > getdate()-62) and d.docType = 13
	) t1 on t1.sup_name = tbs.supplier_name
group by sc.supplier_contact_title, sc.supplier_contact_fname, sc.supplier_contact_lname, 
	sc.supplier_contact_email, tbs.supplier_name
having 	count(tbs.supplier_id) <= 1
order by supplier_name, sc.supplier_contact_lname, sc.supplier_contact_fname

Go with the flow & have fun! Else fight the flow

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-22 : 11:38:33

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-12-22 : 11:56:36

select distinct sc.supplier_contact_title, sc.supplier_contact_fname, sc.supplier_contact_lname,
sc.supplier_contact_email, tbs.supplier_name
from ...
won't do?

EDIT:
add this to the joines
select...
from ...
inner join (select supplier_contact_title, max(supplier_contact_email) as supplier_contact_email from tblsuppliercontact group by supplier_contact_title) t on t.supplier_contact_title = sc.supplier_contact_title and t.supplier_contact_email = sc.supplier_contact_email
...

Go with the flow & have fun! Else fight the flow

jparker
Posting Yak Master

118 Posts

Posted - 2004-12-22 : 11:58:49

Nope

Doesn't work on either yours or mine worked examples that is why I was using the count selection

Hippi
Yak Posting Veteran

63 Posts

Posted - 2004-12-22 : 13:31:13

quote:
Originally posted by jparker

I'm afraid that did work I still get multiple records for each supplier

What I am trying to achieve is rather than having

Title | First | Surname | Email | Supplier
MR | A | Sample | ab.sample@email.com | AB Sample Ltd
MR | A | Sample | ac.sample@email.com | AB Sample Ltd
MR | A | Sample | ad.sample@email.com | AB Sample Ltd
MR | B | Simple | ba@simple.com | Simpleton
MR | B | Simple | bc@simple.com | Simpleton

is to only take the first occurrence of each new supplier, so i get

Title | First | Surname | Email | Supplier
MR | A | Sample | a.sample@email.com | AB Sample Ltd
MR | B | Simple | ba@simple.com | Simpleton

Hope this makes it clearer

I may not understand the requirement exactly but how about:
Select * from table t1
where t1.email=(select min(email) from table t2 where t1.surname=t2.surname)

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-22 : 23:10:09

quote:
Originally posted by jparker

I'm afraid that did work I still get multiple records for each supplier

What I am trying to achieve is rather than having

Title | First | Surname | Email | Supplier
MR | A | Sample | ab.sample@email.com | AB Sample Ltd
MR | A | Sample | ac.sample@email.com | AB Sample Ltd
MR | A | Sample | ad.sample@email.com | AB Sample Ltd
MR | B | Simple | ba@simple.com | Simpleton
MR | B | Simple | bc@simple.com | Simpleton

is to only take the first occurrence of each new supplier, so i get

Title | First | Surname | Email | Supplier
MR | A | Sample | a.sample@email.com | AB Sample Ltd
MR | B | Simple | ba@simple.com | Simpleton

Hope this makes it clearer

how did you get a.sample@email.com and just get ba@simple.com?

select title,[first],surname,min(email),supplier
from tablename
group by title,[first],surname,supplier

--------------------
keeping it simple...

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-12-23 : 11:05:13

What does this do?

and	(
	select 	count(supplier_id) 
	from 	tblsupplier 
	where 	sc.supplier_id = supplier_id
	) <=1

I would think that supplier_id would be unique in tblsupplier and if the count were less than 1 there would be no record to join/return.

Does this return the same results as your query?

SELECT	  sc.supplier_contact_title
	, sc.supplier_contact_fname
	, sc.supplier_contact_lname
	, sc.supplier_contact_email
	, tbs.supplier_name

FROM	tblsuppliercontact sc JOIN tblsupplier tbs
	  ON  sc.supplier_id = tbs.supplier_id
	JOIN tbl_documents d
	  ON  tbs.supplier_name = d.sup_name
	  AND DATEDIFF(dd, d.created, getdate()) < 62

WHERE	sc.supplier_contact_email <> ''

ORDER BY supplier_name, sc.supplier_contact_lname,
         sc.supplier_contact_fname

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources