| Author |
Topic |
|
baja_yu
Starting Member
24 Posts |
 Posted - 2010-08-16 : 12:38:41
|
| Hi people,I'm not sure if the terminology in the thread title is right, but here is what I'm trying to do. Lets say I have two tables:tbl1: Name, IDtbl2: tbl1ID, IDTable 2 is a list of IDs from the first table. What I would like to show as a result is a table looking like this:Name | Comment | ID---------------------------name1 | | 1name2 | | 2name3 | aaaaa | 3The comment column would be empty if the ID from tbl1 does not exist in tbl2, and it would read anything if the record with that ID does exist in tbl2. The end result is basically the entire tbl1 listed with an additional column to indicate which item is in tbl2 too. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-16 : 12:43:06
|
| [code]SELECT t1.Name,CASE WHEN t2.ID IS NULL THEN '' ELSE 'aaaaa' END AS Comment,t1.IDFROM tbl1 t1LEFT JOIN tbl2 t2ON t1.ID=t2.tbl1ID[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
X002548
Not Just a Number
15586 Posts |
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-08-16 : 16:05:18
|
| visakh16, thanks for the reply, I'll give it a try and report back.X002548, what did you mean by your comment? Do you think the method visakh16 suggested wont work, or do you think my method looks odd? I am doing this as I need to manually check a table with all the records in it, and pay special attention to the ones with IDs in table 2, so I just need to mark them in any way. And I need to check them in context with other elements of table1. With the additional column and anything written in it for items that are in table2 also, it will be easy for me to spot them without manually comparing IDs or what not. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-08-16 : 16:37:35
|
| Table2 two contains a list of IDs to items in table1, I need to list the entire table1 so I can inspect the records, but I need to pay special attention to ones with IDs in table2. That new column is just to point them out to me visually.Lets say I have in table1Name | ID---------Mark | 1Peter | 2John | 3Jack | 4Michael | 5And in table2 I havetbl1ID | ID-----------3 | 1What I wanted to do is get a result like thisName | Comment | ID--------------------Mark | | 1Peter | | 2John | aaaaaa | 3Jack | | 4Michael | | 5That way when I'm looking through all of them I see that I need to pay attention to John. Table1 can have thousands of items. I hope that clears it. That's just some example data, the tables have a lot more fields, and I need to list all the items from table1 for my inspection, not just the ones referred in table 2. |
|
|
|
baja_yu
Starting Member
24 Posts |
Posted - 2010-08-16 : 20:53:41
|
| visakh16, works like a charm! Many thanks! |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-08-17 : 11:02:45
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
Conditional Join