• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 375
  • Last Modified:

Sybase SQL Query pull single record if individual has multiple records with different data in one table

Hello Experts, I am creating a SQL query in Crystal Reports to run against a Sybase db. The data is information about individuals. The Address table contains Street addresses, both business and residential, with a column that contains an identifier(BUS, RES). I need to be able to pull all the unique individuals without getting both addresses. In other words, if an individual has just a RES address he needs to be pulled and if an individual has both types of addresses, I want to pull the business address only, and if Business only, pulled also.
1 Solution

You can do it with a GROUP BY HAVING:

create table addresses (id int, type char(3))
insert addresses values (1, "BUS")
insert addresses values (2, "BUS")
insert addresses values (2, "RES")
insert addresses values (3, "RES")
select id, type from addresses group by id having type = min(type)

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

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