sql statement - returning double records

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

Transact-SQL (2000)

sql statement - returning double records

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-19 : 05:37:37

I have the follwing statement
SELECT users.*, Sources.source, timezones.timezone
FROM (users LEFT JOIN Sources ON users.filesource = Sources.SourceID) LEFT JOIN timezones ON users.timezoneid = timezones.TimeZoneID
ORDER BY users.myid;

When I run it in access it works fine.
When I run it in sql server (i just moved my db to sql server) It returns double of each record.

Can someone help me debug?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-19 : 07:12:41

try it without ( and ):
FROM users LEFT JOIN Sources ON users.filesource = Sources.SourceID
LEFT JOIN timezones ON ...

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-19 : 07:25:51

Nah, one of the three tables has had its rows inserted twice. It's the one without the primary key.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-19 : 07:32:34

you mean in transfer from access to sql?? possibly...

do this in access and sql server and tell us if you get the same numbers
select count(*) from Sources
select count(*) from users
select count(*) from timezones

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-19 : 08:17:17

time zone and sources are just lookup tables. They do not have teh same rows as users and users has it only once.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 09:24:11

you need to give us some sample data and, based on that sample data, what you would like returned.

(had to highlight the "based on the sample data part" -- too many people lately give us sample data that has nothing to do with what they want returned !

)

- Jeff

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-19 : 09:40:20

What's being returned is the follosing

userid,username,password,source,timezone (fieldnames)
1,xxx,yyy,zz,2
1,xxx,yyy,zz,2
2,ddd,ddd,ss,2
2,ddd,ddd,ss,2

All records are being returned twice,. (when there is only one record in the db and userid is autoincremetn

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 09:45:20

what is the data in your tables that is relevant to what you are showing us? How we can determine why that is being displayed if we don't know what is stored in your tables????

(stop and think logically about us trying to help you, and how you can provide the information we need to help you out)

- Jeff

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-19 : 09:50:58

this is the field names and data (it's only in test mode but you can see the problem here. You can see it return the records twice. and teh query is below.

userid,username,password,source,timezone (fieldnames)
1,xxx,yyy,zz,2
1,xxx,yyy,zz,2
2,ddd,ddd,ss,2
2,ddd,ddd,ss,2

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-19 : 10:47:04

Assuming this isn't a miracle...we'll need:

DDL
Sample Data
DML
Expected Results


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2 varchar(10))
CREATE TABLE myTable00(Col1 int, Col2 varchar(10))
GO

INSERT INTO myTable99(Col2) 
SELECT 'a' UNION ALL
SELECT 'b' UNION ALL
SELECT 'c'

INSERT INTO myTable00(Col1, Col2) 
SELECT 1, 'w' UNION ALL
SELECT 1, 'x' UNION ALL
SELECT 2, 'y' UNION ALL
SELECT 2, 'z'
GO

    SELECT * 
      FROM myTable99 l 
 LEFT JOIN myTable00 r 
	ON l.Col1 = r.Col1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 10:53:24

you are showing us the RESULTS you are getting. That data did not come from mid-air -- those values were pulled from some tables somewhere, right? We need you to show us the data in your tables that is causing those results.

Does this make sense? If you post those results you are getting one more time I'm going to send Brett over there to smack you !

Again -- stop everything, and look at what you are asking us and think to yourself "Am I giving them the information they need to help me? What else can I provide for them to get to the bottom of this?" I not kidding -- say those words out-loud over and over if you need to. and then think about the answer.

- Jeff

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-19 : 11:20:47

my query has no where to it. It's just pulling up source name instead of number and timezone

1,xxx,yyy,1,2
2,ddd,ddd,1,2

For some strange reason it is duplicting the rows.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-19 : 11:24:45

ok, last try:

you have tables in the statement you gave us:

SELECT users.*, Sources.source, timezones.timezone
FROM (users LEFT JOIN Sources ON users.filesource = Sources.SourceID) LEFT JOIN timezones ON users.timezoneid = timezones.TimeZoneID
ORDER BY users.myid;

This query retreives data from those 3 tables, and generates the results you are seeing. WHAT IS IN THOSE TABLES!?

did you see Brett's post ?? he is giving you an example of how to ask a question. give us the create table statements, some INSERT statements with sample data, what you have tried, and then what the results you are looking for are.

You didn't stop and try to think logically about how you can provide information for us, did you? We have now spent 10 posts trying to figure out your question, as opposed to your answer....

- Jeff

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-19 : 11:47:22

sources

1,form
2,internet
3,voicemail

timezones
1, 212
2, 773

They are just lookup tables -- 2 fields in each

X002548
Not Just a Number

15586 Posts

Posted - 2004-10-19 : 12:04:40

OK...

Do you have Enterprise Manager installed?

Are you using Query Analyzer?

You need to go in to Enterprise Manager (EM) for us, and Script the DDL for the table, inclusing all the constraints and Triggers

Brett

8-)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 05:06:07

hell... just use distinct...

select distint ...
from...

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

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-20 : 07:37:48

quote:
Originally posted by spirit1

hell... just use distinct...

select distint ...
from...

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

i bet that's the answer to the question...

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

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-20 : 09:09:13

That's actually, IMHO, one of the worst practices I see in SQL. So many people don't write proper joins or group their data correctly or use subqueries when needed, they see duplicates, and they just start adding DISTINCT and/or random GROUP BY's until it looks right .

ALWAYS a bad idea ..... it might "look" OK for this particular run, but when the data changes or you need to make modifications to the SQL in the future, all bets are off and you'll have no idea what is going on.

Stop, forget about SQL, look at the data logically, use a small simplified subset if necessary, use a pen & paper and work it out that way manually if you need to (I do this very often) and make sure it all makes sense. Then, after you logically have determined the best way to get this data, you work in steps -- you might write 3 seperate SQL queries, troubleshoot each individually, and eventually join them all together.

I can't stress this advice enough. Feel free to ignore it, but that's the way to solve these problems.

- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 09:12:16

i agree with you one hundred percent, but in this case .... ddl and dml are still missing, so i have no better advice...
personally i hate distinct.

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

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2004-10-20 : 09:13:08

problem solved. Thanks to you all for your help.
the source table had double entries.
For some reason when using dts for the tables it does not keep the primary keys or identity fields.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-20 : 09:30:43

i guess the general was correct all along

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

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources