SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

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

AskSQLTeam
Ask SQLTeam Question

USA
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")
rs.open sql,strconn, adUseClient, adCmdText

count_i=rs.recordcount
----------

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?

Thanks."

blindman
Flowing Fount of Yak Knowledge

USA
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
	LEFT OUTER JOIN table2
		ON table1.id_m = table2.id_m
		AND table2.mainpic = 1

Go to Top of Page
  Previous Topic Topic Next Topic  
 New 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.05 seconds. Powered By: Snitz Forums 2000