SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Returning multiple records when only one is specified


Returning multiple records when only one is specified

Author
Message
crazy_new
crazy_new
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 385
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?
BenWard
BenWard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 827
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
----------------------------------------
mea99sdp
mea99sdp
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 760
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
crazy_new
crazy_new
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 385
Thanks guys I figured it out.

I set the language_id from the wrong table to 3.
crazy_new
crazy_new
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 385
can someone please tell me how I can set the @ADR2 in the if statement equals to the actual column name?
BenWard
BenWard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 827
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
----------------------------------------
BenWard
BenWard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 827
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
----------------------------------------
crazy_new
crazy_new
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 385
It still says iunvalid column name
BenWard
BenWard
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1218 Visits: 827
which column name is invalid? is it definitely in your table?

Ben

^ Thats me!


----------------------------------------
01010111011010000110000101110100 01100001 0110001101101111011011010111000001101100011001010111010001100101 01110100011010010110110101100101 011101110110000101110011011101000110010101110010
----------------------------------------
crazy_new
crazy_new
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1032 Visits: 385
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search