Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

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
?
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Read on to get a few ideas on how to promote your next corporate event.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

722 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