Solved

Using MS SQL Server2000 Driver in jsp to execute INNER JOIN statement retrieves incorrect results

Posted on 2004-03-25
9
316 Views
Last Modified: 2010-04-01
I'm using MS SQL Server 2000 Driver for JDBC in JSP. When I use the following SQL statements in my JSP page, It retrieves incorrect results from the database. My statement like this:

SELECT DISTINCT table1.field1, table2.field2 FROM table1
          INNER JOIN table2 ON table1.fieldX=table2.fieldX
          INNER JOIN table3 ON table2.fieldY=table3.fieldY
          INNER JOIN table4 ON table3.fieldZ=table4.fieldZ

I use MS SQL Profiler to trace the running of my statement, I find that an asterisk (*) is added into the field list like this:

SELECT DISTINCT table1.field1, table2.field2, * FROM table1
          INNER JOIN table2 ON table1.fieldX=table2.fieldX
          INNER JOIN table3 ON table2.fieldY=table3.fieldY
          INNER JOIN table4 ON table3.fieldZ=table4.fieldZ

I'm really stuck. Plz help.
 
0
Comment
Question by:coregis
  • 3
  • 3
  • 3
9 Comments
 
LVL 6

Expert Comment

by:jarasa
ID: 10676088
Can you post the error?

Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10678154
I'm sure that's not the real query. I might be able to see what's wrong if you show us the real one, must likely your query has problem.
0
 

Author Comment

by:coregis
ID: 10683238
The following is my sql statement :

SELECT DISTINCT corporation.corporationId, county.countyName, town.townName
FROM corporation
INNER JOIN town ON corporation.townId=town.townId
INNER JOIN county ON town.countyId=county.countyId
INNER JOIN corporationAttribute ON corporation.attrId=corporationAttribute.attrId
INNER JOIN corporationType ON corporationAttribute.typeId=corporationType.typeId
INNER JOIN region ON county.regionId=region.regionId
WHERE region.regionId='1' AND corporationType.typeId='1'

It should return:
corporationId countyName    townName
  1           Cangzhou      Qingxian
  2           Cangzhou      Mengcun

But the results my application returns:
corporationId countyName    townName
  1           Cangzhou      Qingxian
  1           Cangzhou      Qingxian
  1           Cangzhou      Qingxian
  1           Cangzhou      Qingxian
  2           Cangzhou      Mengcun
  2           Cangzhou      Mengcun
  2           Cangzhou      Mengcun
  2           Cangzhou      Mengcun

I found that my statements has been changed to the following:

SELECT DISTINCT corporation.corporationId, county.countyName, town.townName, *
FROM corporation
INNER JOIN town ON corporation.townId=town.townId
INNER JOIN county ON town.countyId=county.countyId
INNER JOIN corporationAttribute ON corporation.attrId=corporationAttribute.attrId
INNER JOIN corporationType ON corporationAttribute.typeId=corporationType.typeId
INNER JOIN region ON county.regionId=region.regionId
WHERE region.regionId='1' AND corporationType.typeId='1'

And so do all the other sql statements.

0
 
LVL 6

Accepted Solution

by:
jarasa earned 100 total points
ID: 10685321
Hi why don't you try to put this:

SELECT DISTINCT corporation.corporationId, county.countyName, town.townName
FROM corporation, town, country, corporationAttribute, corporationType, region
WHERE corporation.townId=town.townId
AND town.countyId=county.countyId
AND corporation.attrId=corporationAttribute.attrId
AND corporationAttribute.typeId=corporationType.typeId
AND county.regionId=region.regionId
AND region.regionId='1'
AND corporationType.typeId='1'

Javier
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:coregis
ID: 10692575
Thanks a lot, I'v solved my problem.
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10692590
was Javier's recommendation the solution to your problem?
0
 

Author Comment

by:coregis
ID: 10730475
Yes, Thanks Javier for helping me solve my problem.
0
 
LVL 6

Expert Comment

by:jarasa
ID: 10730497
My pleasure :c)
Javier
0
 
LVL 14

Expert Comment

by:kennethxu
ID: 10733299
thanks coregis and Javier, I was asking because I want to learn for sure.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Facing problems with you memory card? Cannot access your memory card? All stored data, images, videos are lost? If these are your questions...than this small article might help you out in retrieving your lost or inaccessible data.
In 2017, ransomware will become so virulent and widespread that if you aren’t a victim yourself, you will know someone who is.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now