Select-table-case problem. - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select-table-case problem.

Author

Topic

Cyanider
Starting Member

7 Posts

Posted - 2004-09-24 : 10:46:40

Hi,this is my first post here.

I have a problem, i have 3 tables, A, B and C.
In the A table I store some info that refers table B and C, but there is a field in the table A that tells me if i should take info from B or C (ex: if column 'typecon' = 0, take data from B table, but if column 'typecon' = 1, take data from C table).

There is an effective query that allow me to choose the table B or C dynamically?

I've tryed all but my SQL knowledge its preety bad. A CASE or IF instruction will helP ?

PLZ HELP

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 10:50:22

I would first suggest a stored procedure...

but... you could do:


Select *
From
	(
	Select tName='tableA', * From tableA
	Union 
	Select tName='tableB', * From tableB
	Union 
	Select tName='tableC', * From tableC
	) A
Where tName = @tName

EDIT: By the way... Welcome!

Corey

DustinMichaels
Constraint Violating Yak Guru

464 Posts

Posted - 2004-09-24 : 10:52:37

You could try this. Not sure if it is a good approach though.

SELECT [table a columns], [table b columns], [table c columns]
FROM A LEFT OUTER JOIN B ON (a.Pkey = b.FK AND typecon = 0)
LEFT OUTER JOIN C ON (a.Pkey = c.FK AND typecon = 1)

When you retrieve the record set if their is a match between table a and b then columns for c will be null, and if there is a match between a and c then column for b will be null.

Dustin Michaels

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-09-24 : 10:54:39

Just ignore me....

Corey

Cyanider
Starting Member

7 Posts

Posted - 2004-09-24 : 11:10:00

Well, i think its a little more complicated. Let me show u wut i've tryed to do without success:

SELECT
_T.tarea,
_TP.tipo_remitente,
co.Nombre_corresponsal AS Corresponsalito,
(us.Nombres + ' ' + us.Apellidos) AS Destinatario,
_T.cod_rad_recibido,
_T.fecha_rad_recibido,
_M.Descripcion,
_T.Asunto
FROM
_Tareas _T, _Tareas_personas _TP, _Medios _M, corresponsales co, USUARIOS us
WHERE
_T.tarea = _TP.tarea AND
_TP.remitente = co.Codigo_corresponsal AND
_TP.destinatario = us.Codigo_usuario AND
_TP.encargado = 65 AND
_M.Medios = _T.medio AND
_TP.tipo_remitente = 'EX'

UNION
SELECT
_T.tarea,
_TP.tipo_remitente,
(usu.Nombres + ' ' + usu.Apellidos) AS Corresponsalito,
(us.Nombres + ' ' + us.Apellidos) AS Destinatario,
_T.cod_rad_recibido,
_T.fecha_rad_recibido,
_M.Descripcion,
_T.Asunto
FROM
_Tareas _T, _Tareas_personas _TP, _Medios _M, USUARIOS usu, USUARIOS us
WHERE
_T.tarea = _TP.tarea AND
_TP.remitente = usu.Codigo_usuario AND
_TP.destinatario = us.Codigo_usuario AND
_TP.encargado = 65 AND
_M.Medios = _T.medio AND
_TP.tipo_remitente = 'IN'
ORDER BY _T.fecha_asignacion

If u notice, the column _TP.tipo_remitente stores the type of "table" that should show but i'm getting a lot of errors.

The column _TP.encargado = 65 its a test to see if a separated select works, and it DOES but i can't "unite" those 2 selects to see the full result. The only way i can do that its making the querys separated.

Btw, thanx Corey and Dustin.

Cyanider
Starting Member

7 Posts

Posted - 2004-09-24 : 11:12:23

"_TP.tipo_remitente" its like "typecon",
_Tareas_personas = "A", USUARIOS = "B", Corresponsales = "C".

I forgot, sorry

Cyanider
Starting Member

7 Posts

Posted - 2004-09-24 : 13:42:29

HIHI, i solve the problem with a UNION (duh), but thanx for ur help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources