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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 SQL Server Development (2000)
 SQL query problem
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Ask SQLTeam Question

0 Posts

Posted - 05/01/2006 :  12:37:40  Show Profile  Visit AskSQLTeam's Homepage  Reply with Quote
Mircea writes "Hi,

I have this SQL:

sql="SELECT table1.firstname, table2.filepath, table3.refer_no, table3.id_m FROM (((table_extra INNER JOIN table_extra1 ON table_extra.id_c = table_extra1.id_c) INNER JOIN table_extra2 ON table_extra.id_c = table_extra2.id_c) INNER JOIN table3 ON table_extra1.id_e = table3.id_e) INNER JOIN (table1 LEFT JOIN table2 ON (table1.id_m = table2.id_m AND table2.mainpic = 1)) ON table_extra2.id_z = table1.id_z WHERE table3.id_e="& id_e &" AND conf=1 ORDER BY id_e_invite"

Set rs = Server.CreateObject("ADODB.Recordset") sql,strconn, adUseClient, adCmdText


This query should show me the name of the people,their pic (if they have any) and how many people they will refer (refer_no).

All tables are in some sort of relationship to each other (direct or indirect - from table_extra,table_extra1,table_extra2 I am not extracting any records but through them I am going to other table from where I am extracting records).

The problem is that "count_i" returns me 240 and I know there are only 48 records!

It seems that many records are duplicates...from where or why I don't know...any ideas?

Also when I am using count(*) in SQL and set rs=db.execute(sql) I get the cout as 1 (not as 48 as it should be).

I can't see why i am getting wrong counts in both cases (maybe in the second case could be because of the cursor?)...any ideas?


Flowing Fount of Yak Knowledge

2365 Posts

Posted - 05/01/2006 :  13:25:07  Show Profile  Reply with Quote
Start by simplifying your JOIN syntax. The use of parenthesis in JOINs is unnecessary and confusing.

What does this JOIN syntax return:
FROM	table_extra INNER JOIN table_extra1 ON table_extra.id_c = table_extra1.id_c
	INNER JOIN table_extra2 ON table_extra.id_c = table_extra2.id_c
	INNER JOIN table3 ON table_extra1.id_e = table3.id_e
	INNER JOIN table1 ON table_extra2.id_z = table1.id_z
		ON table1.id_m = table2.id_m
		AND table2.mainpic = 1

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000