This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.
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.
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.
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
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
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...
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]
(
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]
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
Accepted answer: 0 points for Leogal's comment #a40038074
for the following reason:
I did not get any responses to the problem
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.
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.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial