We help IT Professionals succeed at work.

Check out this week's podcast, "Dairy Farms to Databases: Community's Hand in Technology"Listen Now

x

Subquery non unique records

Leogal
Leogal asked
on
194 Views
Last Modified: 2014-05-05
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.
Comment
Watch Question

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

Author

Commented:
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...

Author

Commented:
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]

Author

Commented:
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
CERTIFIED EXPERT
Top Expert 2012

Commented:
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.

Author

Commented:
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.
CERTIFIED EXPERT
Top Expert 2012
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Thank you I will add ORDER BY column 1

Author

Commented:
I am double checking the data, it appears that the query it working correctly now.

Author

Commented:
I appreciate the help in this matter.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.