Avatar of Leogal
Leogal
Flag for United States of America asked on

Subquery non unique records

I need assistance on how to make my subquery pull the correct record. At the heart of the issue is the fact that the lookup table, propery type codes does not and cannot have unique records.  I have attached the table.


Here is the subquery:
 (
                 SELECT cp.[c.PROP_TYPE_CODE]
                         FROM   dbo.[C_Property_type code] cp
                         WHERE   cp.[C_PROP TYPE CODE] =  CI.[PROPERTY_TYPE_CD]

         )        as [State Code]

CI.[property_type_cd] is one element of the table that resides in the TSQL program that this subquery is embedded in.  They are both found in the single SQL Server databse that I am using on my SQL Server.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Leogal

8/22/2022 - Mon
Bob Bender

If you are getting records, and not a single one, then you need to supply more criteria for us to analyze, or in your statement where clause....

It isn't as simple as a DISTINCT issue, where only each unique Property Code would be listed once (despite thewre being multiple entries in the db of that code.

Such as  "SELECT DISTINCT cp.[c.PROP_TYPE_CODE]" ?


Bob
Leogal

ASKER
Attached is a table to show the incoming rows that I am trying to map to the lookup table.  I am stumped on whot to provied a single record output...
Leogal

ASKER
I figured this out:

 (
                 SELECT TOP 1 cp.[c.PROP_TYPE_CODE]
                         FROM   dbo.[C_Property_type code] cp
                         WHERE   cp.[C_PROP TYPE CODE] =  CI.[PROPERTY_TYPE_CD]

         )        as [State Code]
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Leogal

ASKER
I've requested that this question be closed as follows:

Accepted answer: 0 points for Leogal's comment #a40038074

for the following reason:

I did not get any responses to the problem
Anthony Perkins

Using a TOP clause without an ORDER BY clause in general makes no sense and means that you do not care which row is returned.
Leogal

ASKER
Acutally, I do care. I am still learning TSQL and this was the best I could come up with at the time. The first column has mutiples of almost every code. The 3 column is primarily unqiue.  I realize that this solution is not the best and am open to other solutions.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Leogal

ASKER
Thank you I will add ORDER BY column 1
Leogal

ASKER
I am double checking the data, it appears that the query it working correctly now.
Leogal

ASKER
I appreciate the help in this matter.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes