Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Returning multiple records when only one is specified Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 2:42 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
Hi, can someone please explain something to me.

SET @NAMEID = '14359'

IF @ADR2 IS NULL AND @ADR3 IS NULL

SELECT ADR1, POSTAL_CODE, (STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS P, STATE_NAME S
WHERE P.NAME_ID = @nameid
AND P.STATE_ID = S.STATE_ID
AND P.CURRENT_ONE = '1'
AND P.LANGUAGE_ID = '3'

In the Pat_names_address table, only one record can be current, if there are more the current = 0.

When i run the folowing code, I get 10 records back instead of one, all showing different countries, but the same addresses.

But I want the record where current one = 1, so obviously it should only return one record as in my code.

Why am I getting 10 different coutries?
Post #1538083
Posted Wednesday, February 5, 2014 3:01 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 369, Visits: 648
At a guess I'd say you had multiple records in the STATE_NAME table with the same STATE_ID

Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Post #1538087
Posted Wednesday, February 5, 2014 3:13 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 4:59 AM
Points: 132, Visits: 407
SET @NAMEID = '14359'

IF @ADR2 IS NULL AND @ADR3 IS NULL

SELECT ADR1,
POSTAL_CODE,
(STATE_NAME) AS COUNTRY
FROM PAT_NAMES_ADDRESS AS P
INNER JOIN STATE_NAME AS S ON P.STATE_ID = S.STATE_ID
WHERE P.NAME_ID = @nameid
AND P.CURRENT_ONE = '1'
AND P.LANGUAGE_ID = '3'

Have a look at your join on the 2 tables

Post #1538089
Posted Wednesday, February 5, 2014 3:22 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
Thanks guys I figured it out.

I set the language_id from the wrong table to 3.
Post #1538091
Posted Wednesday, February 5, 2014 3:48 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
can someone please tell me how I can set the @ADR2 in the if statement equals to the actual column name?
Post #1538101
Posted Wednesday, February 5, 2014 3:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 369, Visits: 648
Sorry not sure I follow the question... do you want to do something like

IF(@ADR2 = 'ADR2')
BEGIN
some code...
END


??


Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Post #1538104
Posted Wednesday, February 5, 2014 3:57 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 369, Visits: 648
or are you after


SELECT CASE

WHEN COALESCE(@ADR2,'itsnull') <> 'itsnull' THEN ADR2
WHEN COALESCE(@ADR3,'itsnull') <> 'itsnull' THEN ADR3
ELSE ADR1

END AS 'ADDRESS'
, --the rest of your query


Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Post #1538105
Posted Wednesday, February 5, 2014 5:08 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
It still says iunvalid column name
Post #1538116
Posted Wednesday, February 5, 2014 5:09 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 9:35 AM
Points: 369, Visits: 648
which column name is invalid? is it definitely in your table?

Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
Post #1538117
Posted Wednesday, February 5, 2014 5:13 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Friday, June 27, 2014 10:04 PM
Points: 60, Visits: 71
Adr2 and Adr3.

I want to use the column name instead of the variable in the if statement, because i have to set the variable = to the column anyway.
Bit I cant say If ADR2 is null because it says invalid column name.

I dont get errors if I say IF @ADR2, but I dont get the correct output because I havent set @ADR2 = to the column.
Post #1538119
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse