Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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

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
coregis
Asked:
coregis
  • 3
  • 3
  • 3
1 Solution
 
jarasaCommented:
Can you post the error?

Javier
0
 
kennethxuCommented:
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
 
coregisAuthor Commented:
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
Technology Partners: 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!

 
jarasaCommented:
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
 
coregisAuthor Commented:
Thanks a lot, I'v solved my problem.
0
 
kennethxuCommented:
was Javier's recommendation the solution to your problem?
0
 
coregisAuthor Commented:
Yes, Thanks Javier for helping me solve my problem.
0
 
jarasaCommented:
My pleasure :c)
Javier
0
 
kennethxuCommented:
thanks coregis and Javier, I was asking because I want to learn for sure.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now