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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2006-05-01 : 12:37:40
|
| 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
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2006-05-01 : 13:25:07
|
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 |
 |
|
|
|
|
|
SQL query problem