Query Issue-SQL Server 2000 - 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)

Query Issue-SQL Server 2000

Author

Topic

ramdas
Posting Yak Master

181 Posts

Posted - 2003-02-18 : 15:44:36

Hi Folks,
I have a query with a Join Clause like this in access:

SELECT a,b,c,d
FROM tbl1,tbl2,tbl3
INNER....
WHERE (((tbl1.a) > 0)
AND ((tbl1.c) = 'A'))
OR (((tbl1.b) > 0)
AND ((tbl1.c) = 'A'))

This query in access gives me unique values for the column d,
where as in SQL Server I get more than one row for Column d, in short in SQL Server I get repeating rows. What would be an equivalent in Sql server to get unique rows.

Ramdas Narayanan
SQL Server DBA

Vivaldi
Constraint Violating Yak Guru

298 Posts

Posted - 2003-02-18 : 16:06:20

Well,

Select Distinct ...

will do it.

________________________________________________
A recent study shows that Southern Germany may have the best beer in the world.

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 16:26:02

ummmmmmmmmmmmmmmm

Can you cut and paste the actual query...what you have is not a valid syntax.

Also, what are the relationships between the tables? You're joing 3 tables together with no correlation between each other..this (potentially) will give you a cartesian product.

Wouldn't you need something Like (Assuming you're using surrogate keys):

SELECT a,b,c,d
FROM tbl1
INNER JOIN tbl2
ON tbl1.key = tbl2.FKey
INNER JOIN tbl3
ON tbl2.key = tbl3.FKey
WHERE (((tbl1.a) > 0)
AND ((tbl1.c = 'A'))
OR (((tbl1.b) > 0)
AND ((tbl1.c = 'A'))

A couple of other questions. What table does column d come from? Is Column d the PK of a table or have a unique index? What are all of the extraneous paranthesis for?

Let mw know. Maybe I can help

Brett

8-)

ramdas
Posting Yak Master

181 Posts

Posted - 2003-02-18 : 16:41:45

Hi,
Here is the where clause in the access query:

WHERE ((([NW Sales Teknekron Table].Primary)>0) AND ((dbo_Tbl_Info_Import_Gate_TEK_CMS.Skip)=No) AND ((dbo_Tbl_Info_Import_Gate_TEK_CMS.[Center Name])="NDS Seattle")) OR ((([NW Sales Teknekron Table].Secondary)>0) AND ((dbo_Tbl_Info_Import_Gate_TEK_CMS.Skip)=No) AND ((dbo_Tbl_Info_Import_Gate_TEK_CMS.[Center Name])="NDS Seattle"))

This is where I am having the problem.
Bye

Ramdas Narayanan
SQL Server DBA

X002548
Not Just a Number

15586 Posts

Posted - 2003-02-18 : 16:56:34

OK...first things first...I like Access for certain things...but it really is a pig in a dress.

It tries to generate appropriate sql, and what it did for you is the same as:

WHERE ([NW Sales Teknekron Table].Primary>0
OR [NW Sales Teknekron Table].Secondary>0)
AND dbo_Tbl_Info_Import_Gate_TEK_CMS.Skip=No
AND dbo_Tbl_Info_Import_Gate_TEK_CMS.[Center Name]="NDS Seattle"

I'm also assuming that it's doing it's inner joins based on the keys of the table, because you used the QBE Grid.

Now you say in Access you don't get duplicates, but you do in SQL Server. First there are no miracles. Second, the Access query you have here shows a ODBC Linked SQL Server Table (I assume), with an Access table (I know it's access because no one would ever write sql the way access generates it). So the questions is now, how did you run it in SQL Server? Did you migrate the entire access table to SQL? Did you make a linked serever out of Access? Is the data identical in both environments? Are the Objects the same in both?

Let Me know

Brett

8-)

ramdas
Posting Yak Master

181 Posts

Posted - 2003-02-19 : 11:28:36

Hi,
Thanks for your help. Your query equivalent worked correctly in SQL Server.
I was running the access query in acess itself using a ODBC connection for SQL Server for the linked tables.

Everything is fine now.

SQL Team forums have been very helpful to me.

Ramdas Narayanan
SQL Server DBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources