return data from two tables

Hi,
hope im posting in right group...
Im trying to return a value from 1 of two tables depending on the value in
one table.
I.e. TableA has an action_id and action_type fields. Depending on if
action_type = S, return the Desc field from TableB, where action_id =
Desc_ID. if action_type = L return the Desc field from TableC, where
action_id = Desc_ID
i.e.
select a.Type_id, (either b.Desc or c.Desc)
from TableA a, TableB b, TableC c
where 1=1
if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
i just dont know how to get this into an sql query. i can do it VBA, but
that isn't an option.
Any help or direction would be greatly appreciated.
Thanks
Tony Z.select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc
end
from TableA a, TableB b, TableC
where 1=1
"Tony Zappal" wrote:

> Hi,
> hope im posting in right group...
> Im trying to return a value from 1 of two tables depending on the value in
> one table.
> I.e. TableA has an action_id and action_type fields. Depending on if
> action_type = S, return the Desc field from TableB, where action_id =
> Desc_ID. if action_type = L return the Desc field from TableC, where
> action_id = Desc_ID
> i.e.
> select a.Type_id, (either b.Desc or c.Desc)
> from TableA a, TableB b, TableC c
> where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_i
d
> i just dont know how to get this into an sql query. i can do it VBA, but
> that isn't an option.
> Any help or direction would be greatly appreciated.
> Thanks
> Tony Z.|||>select a.Type_id, Descr=case when a.action_type='S' then b.desc else c.desc endred">
> from TableA a, TableB b, TableC
> where 1=1
Those three tables, listed without any joining, will result in a cross
product result set. If there were 100 rows each, the result set would
have 100*100*100 = 1000000 rows.
Roy|||There are a few ways this could be written. Here is one.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S'
THEN (select B.desc from TableB as B
where A.something = B.something)
WHEN A.action_type = 'L'
THEN (select C.desc from TableC as C
where A.something = C.something)
FROM TableA as A
And another.
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
WHERE A.action_type = 'S'
UNION ALL
SELECT A.Type_ID, B.desc
FROM TableA as A
JOIN TableC as C
ON A.something = C.something
WHERE A.action_type = 'L'
And a third.
SELECT A.Type_ID,
CASE WHEN A.action_type = 'S' THEN B.desc
WHEN A.action_type = 'L' THEN C.desc
END
FROM TableA as A
JOIN TableB as B
ON A.something = B.something
JOIN TableC as C
ON A.something = C.something
Roy
On Tue, 21 Feb 2006 18:16:27 -0800, "Tony Zappal"
<TonyZappal@.discussions.microsoft.com> wrote:

>Hi,
>hope im posting in right group...
>Im trying to return a value from 1 of two tables depending on the value in
>one table.
>I.e. TableA has an action_id and action_type fields. Depending on if
>action_type = S, return the Desc field from TableB, where action_id =
>Desc_ID. if action_type = L return the Desc field from TableC, where
>action_id = Desc_ID
>i.e.
>select a.Type_id, (either b.Desc or c.Desc)
>from TableA a, TableB b, TableC c
>where 1=1
> if a.action_type = 'S' then return b.Desc where a.action_id = b.Desc_id
> or if a.action_type = 'L' then return c.Desc where a.action_id = c.Desc_id
>i just dont know how to get this into an sql query. i can do it VBA, but
>that isn't an option.
>Any help or direction would be greatly appreciated.
>Thanks
>Tony Z.|||Thank you John and Roy.
I've got my query returning exactly what i need.
Big thanks.
Tony.sql

return a value from SQL server SP back to .net

I have a SP code:
select 'nothing' from tableA where userID = '123'
if @.@.rowcount = 0
return 0
else
return 1

.net code:
Dim myConnection As New SqlConnection("server=(local);database=pubs;Trusted_Connection=yes")
Dim myCommand As New SqlCommand("StoreP", myConnection)

myCommand.Connection.Open()
Returnvalue = myCommand.ExecuteNonQuery()
myCommand.Connection.Close()

Returnvalue shows -1, it doesnt show the return value from SP. how do I fix this problem? thanksHi,

The return value from the ExecuteNonQuery returns the number of rows affected by the query, not the return value from the stored procedure. For a SELECT statement like you're using, it always returns -1.

To get the return value, you have to add a parameter to the ADO.NET command object, and set its Direction property to ReturnValue. Something like this:

myCommand.Parameters.Add("@.RetVal", SqlType.Integer).Direction = _
 ParameterDirection.ReturnValue

Then you can read the value after you run the query:

Dim i as Int32 = myCommand.Parameters("@.RetVal").Value

I've typed the code from memory, so it may need some tweaking.

Don|||I still have two questions
1. If I want to return 2 value from SP to asp.net, how do I do?
2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Thank you|||Hi,

1. If I want to return 2 value from SP to asp.net, how do I do?

Then use output parameters. You can define as many of those as you want. For them, use ParameterDirection.Output for the Direction property.

2. when I want to insert same value into PK twice in SP, it will give me a error message 2627, and the code break. How do I do to let SP return me an error message WITHOUT hanging the code. In other word, I am trying to let SP return the error message, but I dont want the asp.net web page to stop.

Probably the best way is to raise an error from the SP using the RAISERROR statement. That will generate a SqlException that you can catch and handle in your page.

Another way is to return one or more output parameters from the SP, one for an error number and another for a message. I don't like this option because it's more work and doesn't hook into the natural exception infrastructure of .NET.

Don|||Can you tell me how exactly you do it? I try parameterdirection.output it gives me an error "too many argument specified." how do I code in SP to return 2 values? thank you|||It sounds like you haven't added the output parameters to the stored procedure, right? You have to do that as well as add the ADO.NET code.

Post the complete sp definition and we'll help you make the changes.

Don|||the SP code is

CREATE PROCEDURE test @.aaa as varchar(10) output, @.bbb as varchar(10) output AS
select @.aaa = '111'
select @.bbb = '222'
return
GO

asp.net code is

Dim objCOmmand As New SqlCommand(strSQL, objConnection)
objConnection.Open()
objCOmmand.CommandType = CommandType.StoredProcedure
objCOmmand.Parameters.Add(New SqlParameter("@.aaa", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.aaa", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.aaa").Value = "aaa"
objCOmmand.Parameters.Add(New SqlParameter("@.bbb", SqlDbType.VarChar))
objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.ReturnValue
objCOmmand.Parameters("@.bbb").Value = "bbb"
objCOmmand.ExecuteNonQuery()
Label1.Text = objCOmmand.Parameters("@.aaa").Value
Label2.Text = objCOmmand.Parameters("@.bbb").Value
objConnection.Close()

after SP I should get 111 in stead of aaa in @.aaa and 222 instead of bbb in @.bbb, but I get aaa and bbb in the result. how do I get the value return from SP?|||Your are not using the correct ParameterDirection. ReturnValue is solely to return the value that appears after the RETURN keyword in your stored procedure. Valid ParameterDirection values for stored procedure parameters are:
Input
Output
InputOutput

In your case, you should be using InputOutput for @.aaa and @.bbb since you are supplying data to the stored procedure (input) and are new receiving data back from the stored procedure (output).

Terri|||I try:

objCOmmand.Parameters.Add("@.bbb", SqlDbType.VarChar).Direction = ParameterDirection.InputOutput

but it gives me an error:

Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Parameter 1: '@.aaa' of type: String, the property Size has an invalid size: 0|||Since you are using a VarChar datatype, you need to specify the length. And I don't know if it matters, but I usually take 2 lines to add a parameter and set the direction:

objCommand.Parameters.Add("@.aaa", SqlDbType.VarChar, 10)
objCommand.Parameters("@.aaa").Direction=ParameterDirection.InputOutput
objCommand.Parameters.Add("@.bbb", SqlDbType.VarChar, 10)
objCommand.Parameters("@.bbb").Direction=ParameterDirection.InputOutput

Terri

return 7 col but DISTINCT on 3 col from 2 tables

Hello !
for MS SQL 2000

SELECT tableA.idA, tableB.idB,tableB.ColX,tableA.ColA,
tableA.ColB, tableA.ColC
FROM tableB RIGHT OUTER JOIN tableA ON tableB.idB = tableA.idA


I want to do a DISTINCT on

tableB.ColX, tableA.ColA, tableA.ColB
to return only the rows tableB.ColX, tableA.ColA, tableA.ColB which are differents

thank you for helpingwhat values do you want for the other 4 columns|||the normal values

lets say I need A, B, C, D , E, F
but DISTINCT on A, B, C (you can forget the 2 tables)

if A,B,C are identics I keep anyone of them, Max(A) for exemple

thank you|||Hi anselme

Could you post some example results
a) Of the data as it would appear BEFORE the distinct is applied OR the raw data as it would appear in the tables
b) the results you are hoping for

As an example (without distinct) a)

A B C D E F
----------
aa bb cc 1 2 3
aa bb cc 4 5 6
with distinct I think you would want b)

A B C D E F
----------
aa bb cc ?? ?? ??Is this accurate for your problem?
What would go where the ?? are?|||select A, B, C, min(D), max(E), avg(F)
from daTable
group by A, B, C|||a smart quick SQL in the last post. however i dont think that is what you want. perhaps you need to relook the db design itself. with a proper design this kind of a requirement should not at all come up.|||Yeah Rudy - come on!
You could have written a CLR aggregate function or something to better meet the business requirements stated. SQL Consultant my Aunt Fanny.|||a smart quick SQL in the last post. however i dont think that is what you want. perhaps you need to relook the db design itself. with a proper design this kind of a requirement should not at all come up.oh, boy, this is getting interesting

upalsen, if you wouldn't mind, could you please show how A,B,C,D,E,F can be redesigned so that the requirement for the query in post #4 "should not at all come up"

i'd really like to see how you do it|||sorry Rudy, you are getting me wrong. I never said that the database can be designed to address the query just how Anselme wanted. what I wanted to say is this query should not be a requirement at all as Anselme never asked for a sum or avg. rather just wanted a distinct row set from a one to many relationship that too with an outer join which appears to be nonrealastic and absurd. or the data is such that they can be there in the first table itself, if normalized properly :confused:.|||would you kindly repeat what you just said? i'm having a little trouble understanding it

also, i really want to know why you think his outer join is unrealistic and absurd|||ok. I am ready to withdraw all my comments if you can show me the distinct output without using aggregate functions. mind that the original post never ever asked for any sum, avg, or max. it just wanted a distinct set of rows of 3 cols while selecting 7 cols from a one-to-many relationship|||there is a flaw in the original specification, which you seem to have propagated -- "a distinct set of rows of 3 cols while selecting 7 cols"

first of all, you were pretty close when you said "a distinct set of rows" because it is rows that are distinct

so if you put 7 columns into the row, you cannot do a "distinct" on just 3 of them

but i suspect you knew that already, right?

the alternative is to either

1. use DISTINCT and only have 3 columns in the SELECT

or

2. use GROUP BY on the 3 columns, but then you are forced to use an aggregate function on the other 4

my question to you -- given columns A,B,C,D,E,F, how would you produce a result set where A,B,C, are unique? what would you do for the other 4 columns?|||ok. I am ready to withdraw all my comments if you can show me the distinct output without using aggregate functions.
OK. Just to stir the pot:select A,
B,
C,
(select top 1 D from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as D,
(select top 1 E from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as E,
(select top 1 F from daTable subtable where subtable.A = daTable.A and subtable.B = daTable.B and subtable.C = daTable.c) as F
from daTable
group by A,
B,
C
There. Now you can both be pi$$ed at me instead of eachother. ;)|||thank you r937, for my needs your answers works fine

min,min,min or max,max,max no matter, sometimes you need tree different girls and you dont care of the color of the shoes

thank you to everybody, and blindman for the other solution|||sometimes you need tree different girls and you dont care of the color of the shoes
...and thank you for that colorful metaphore.sql