SQL Join Question

Question

Post reply

SQL Join Question

Ash-N

Say Hey Kid

Points: 703
More actions
September 8, 2006 at 10:04 am

#114418

I have two tables
Table A
aid
aname
acomments
TableB
bid
aid
acomments
TABLE A
aid aname acomments
---------------------------
1 onea onecomments
2 twoa twocomments
3 threea threecomments
.
.
100     hundreda hundredcomments
TABLE B
bid aid acomments
-------------------------------------
1 1 onecommentsupdated
2 3 threecommnetsupdated
The way it works is when ever the comments in a is updated Table B gets populated.
I need to write a query which gets aid, aname & acomments listed out.
If there are no entries in TABLE B then take the comments from TABLE A .
If there is a commment in TABLE B then take the updated comment.
The result looks like
aid aname acomments
--------------------------
1 onea    onecommentsupdated
2 twoa twocomments
3 threea threecommnetsupdated
.
.
100     hundreda hundredcomments

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 1

Why not use a IF Exists statement to do your updating\checking?

Then if your criteria is met, Do your listing - If not update the other table....

Ash-N Say Hey Kid Points: 703 More actions · Answer 2

My current query looks like this ...

select a.aid, a.aname, a.comments as commets, b.acommnets as updatedcomments from

TABLE A LEFT JOIN TABLE B on a.aid = b.aid

where do i put the IF condition?

Martin D. Cymerman SSCrazy Points: 2079 More actions · Answer 3

Sorry I was not following your logic - Why not just write an Update trigger to Update table B.comments when an insert is made to Table A.comments and then display the values.

Ash-N Say Hey Kid Points: 703 More actions · Answer 4

These are external table for me and i do not have access to modify these tables. I have select permission from those two tables.

Ashok

John Rowan SSC Guru Points: 56440 More actions · Answer 5

declare @TableA table (

aid int,

aname varchar(20),

acomments varchar(100)

)

declare @TableB table (

bid int,

aid int,

acomments varchar(100)

)

insert into @tablea

select 1, 'onea', 'onecomments' union all

select 2, 'twoa', 'twocomments' union all

select 3, 'threea', 'threecomments'

insert into @tableb

select 1, 1, 'onecommentsupdated' union all

select 2, 3, 'threecommnetsupdated'

SELECT a.aid,

a.aname,

CASE WHEN b.acomments IS NULL THEN a.acomments ELSE b.acomments END

FROM @tablea a

LEFT JOIN @tableb b

ON a.aid = b.aid

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Sreejith Sreedharan SSCertifiable Points: 5403 More actions · Answer 6

I am assuming you want to get the most current Comments in TableB and if no comment is present then get the coooment from table A.

Try this:

SELECT a.aid, a.aname,COALESCE(UpdateTableB.acomments,a.acomments)

FROM tablea a

LEFT JOIN (

Select TableB.bid,TableB.aid,TableB.acomments

From TableB INNER JOIN

(Select max(bid) as Maxbid, aid from TableB GRoup by aid) MaxB

ON MaxB.aid = TableB.aid) UpdateTableB

On a.aid = UpdateTableB.aid

Hope this helps.

Thanks

Sreejith

Ash-N Say Hey Kid Points: 703 More actions · Answer 7

John , Srijith

Thanks for your help.

A-