Solved

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

Posted on 2004-03-25
9
326 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Direct mail marketing is the act of mailing materials straight to prospective customers. This wide form of marketing is one of the oldest methods of communicating with a geographic based demographic. So is it useful in 2017 and beyond?
This article summaries thoughts and ideas from two years of sustained use. It provides good reasoning to make the jump to Windows 10.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

680 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