Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2004-03-25
9
Medium Priority
?
349 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 6

Accepted Solution

by:
jarasa earned 300 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Simulator games are perfect for generating sample realistic data streams, especially for learning data analysis. It is even useful for demoing offerings such as Azure stream analytics, PowerBI etc.
The first step to building an amazing About page is to figure out what you want the page to say about your company. You then must grab the attention of the reader, boast a bit, tell a story and let others brag about you. With a little bit of thought…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

927 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