This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes sql question Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "sql question" Watch "sql question" New topic
Author

sql question

Abigail Moore
Greenhorn

Joined: May 13, 2004
Posts: 12
I have the following sql:

SELECT customer_id, hierarchy_id, hierarchy_type_cd,
primary_station, primary_type_cd
FROM ebat.ha_station_hierarchy_811
WHERE customer_id = '9999999999'
AND primary_station = '5629402401'

This returns 2 rows of data where primary_type_cd is different. How do I change this sql so that it rolls the 2 rows of data into 1, ignoring the difference in the primary_type_cd?

Thanks for the help
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60743
    
  65

Abigial, why would you be posting a SQL question in a forum about build tools such as Ant? Please be a bit more careful about where you are posting.

I've moved this post to the JDBC forum for you.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
stu derby
Ranch Hand

Joined: Dec 15, 2005
Posts: 333
Originally posted by Abigail Moore:
I have the following sql:

SELECT customer_id, hierarchy_id, hierarchy_type_cd,
primary_station, primary_type_cd
FROM ebat.ha_station_hierarchy_811
WHERE customer_id = '9999999999'
AND primary_station = '5629402401'

This returns 2 rows of data where primary_type_cd is different. How do I change this sql so that it rolls the 2 rows of data into 1, ignoring the difference in the primary_type_cd?

Thanks for the help


Note the addition of the DISTINCT keyword, and the removal of the primary_type_cd column from the selection criteria; since there are two different values and you want just one row, you either have to pick a particular value of primary_type_cd or omit it.

An example of picking one of the primary_type_cd values is:

The success of this SQL depends on what your database actually supports and the actual data type of the primary_type_cd column; it might not work for you on your particular database, but something else along those lines will.
Abigail Moore
Greenhorn

Joined: May 13, 2004
Posts: 12
Thank you! I will try that, I did try distinct but didn't remove primary_type_cd.
 
wood burning stoves
 
subject: sql question
 
Similar Threads
Optional join of tables
Simple SQL joining 2 tables - 2 many rows being returned
Apache Derby Sql Statement's
JPA-Hibernate One-to-One Bidirectional Relationship issue
executeUpdate not updating and no exception thrown