Hello I have a question if have

[goransaler]

Hello
I have a question
if have tables like this

NEWS A table
ID NAME
1 NEWS1
2 NEWS2


NAME B table
ID NAME
1 JIM
2 ADAM

ISSUE C table
ID NAME
1 ISSUE1
2 ISSUE2
3 ISSUE3
4 ISSUE4

(if a new ISSUE row is added a row whith BID is set as <NULL> and always a AID)

ABC table
ID AID BID CID
1 1 <NULL> 1
2 1 <NULL> 2
3 1 <NULL> 1
4 1 <NULL> 2
5 1 1 1
6 1 1 2
7 1 2 1

If i like to view it like this

JIM

* ISSUE1
* ISSUE2
ISSUE3
ISSUE4


If i try with

SELECT dbo.ABC.ID, dbo.ABC.AID, dbo.ABC.BID, dbo.C.AAA, CASE WHEN dbo.ABC.BID IS NULL THEN ' ' ELSE '# ' END + SUBSTRING(dbo.C.AAA, 1, 20)
AS Activeact
FROM dbo.ABC INNER JOIN
dbo.C ON dbo.ABC.CID = dbo.C.ID
WHERE dbo.ABC.BID = 1 OR
dbo.ABC.BID IS NULL

I get this


ISSUE1
ISSUE2
ISSUE3
ISSUE4
* ISSUE1
* ISSUE2

I cant figure out how to do this please help

 

[ClaireHCM]

I found many confusing point in your question.
I created A,B,c and ABC table and inserted values exactly the same as you described in the question.

Table A:
ID NAME
---- ----------
1 NEWS1
2 NEWS2

Table B:
ID NAME
--- ----------
1 JIM
2 ADAM

Table C:
ID NAME
---- ----------
1 ISSUE1
2 ISSUE2
3 ISSUE3
4 ISSUE4

Table ABC

ID AID BID CID
--- --- ------ ----
1 1 NULL 1
2 1 NULL 2
3 1 NULL 1
4 1 NULL 2
5 1 1 1
6 1 1 2
7 1 2 1

Instead of doing the select statement you wrote.I first tested ,I first tested
select * FROM dbo.ABC INNER JOIN dbo.C ON dbo.ABC.CID = dbo.C.ID WHERE dbo.ABC.BID = 1 OR dbo.ABC.BID IS NULL

ID AID BID CID ID NAME
--- ---- ---- ---- --- ----------
1 1 NULL 1 1 ISSUE1
2 1 NULL 2 2 ISSUE2
3 1 NULL 1 1 ISSUE1
4 1 NULL 2 2 ISSUE2
5 1 1 1 1 ISSUE1
6 1 1 2 2 ISSUE2

QUESITON COME!
I cant get ISSUE3 and ISSUE4 in the list.How did you get it?

 

[ClaireHCM]

And one more question.

How is the result related with Table A and Table B.Both tables didnt appear in the select statement.But it seems you do want to relate Table B with your desired outcome.

 

[goransaler]

I´m so sorry
the table ABC should be like this

Table ABC

ID AID BID CID
--- --- ------ ----
1 1 NULL 1
2 1 NULL 2
3 1 NULL 3
4 1 NULL 4
5 1 1 1
6 1 1 2
7 1 2 1


and yes i have related A,B and C
whith ABC

but i view the other tables seperatly
but but the problem is whith this query

 

[goransaler]

i like to show all issues one time
and the one that JIM has got
i like to have a sign i front

like this



* ISSUE1
* ISSUE2
ISSUE3
ISSUE4

 

[goransaler]

and whith that query i get
all these columns
but my thinking was at the Activeact column

ID AID BID CID ID NAME Activeact

1 1 NULL 1 1 ISSUE1 ISSUE1
2 1 NULL 2 2 ISSUE2 ISSUE2
3 1 NULL 1 1 ISSUE3 ISSUE3
4 1 NULL 2 2 ISSUE4 ISSUE4
5 1 1 1 1 ISSUE1 #ISSUE1
6 1 1 2 2 ISSUE2 #ISSUE2

 

[goransaler]

sorry again i saw one more foult in the sql

SELECT dbo.ABC.ID, dbo.ABC.AID, dbo.ABC.BID
, dbo.C.AAA should be dbo.C.Name


now i hope it´s ok

 

[ClaireHCM]

Try this ~~

SELECT dbo.ABC.ID,dbo.ABC.AID,dbo.ABC.BID,dbo.C.Name,CASE WHEN dbo.ABC.BID IS NULL THEN '' ELSE '*'
END + SUBSTRING(dbo.C.name, 1, 20) AS Activeact
FROM ABC inner JOIN C ON ABC.CID =C.ID WHERE ABC.BID IS NULL and name not in (
select name FROM ABC inner JOIN C ON ABC.CID =C.ID
WHERE ABC.BID = 1) or abc.bid = 1

 

[goransaler]

Thanks a lot
It worked just perfect