December 29, 2008 at 10:08 am
Hi,
I have two tables:
Table 1 has two columns which I'll call TBL_1_COL_1 and TBL_1_COL_2.
Table 2 also has two columns which I'll call TBL_2_COL_1 and TBL_2_COL_2.
As an example the values in table 1 look like this:
TBL_1_COL_1 TBL_1_COL_2
========== ==========
91129 236747
91129 401046
91129 446609
91129 446610
Table 2 has values like this:
TBL_2_COL_1 TBL_2_COL_2
========== =========
236747 F
401046 F
446609 M
446610 M
I inner join TBL_1_COL_2 with TBL_2_COL_1 so the results will look like this:
TBL_1_COL_1 TBL_2_COL_1 TBL_2_COL_2
----------- ---------- -------------
91129 236747 F
91129 401046 F
91129 446609 M
91129 446610 M
I want to know if there is a way to set TBL_2_COL_2 to all the same values based on a value in TBL_2_COL_2. For example if any value in TBL_2_COL_2 equals 'F' I want to set all the remaining rows to 'F' where the value in TBL_1_COL_1 is the same (eg. 91129)? So the end result would look like this:
TBL_1_COL_1 TBL_2_COL_1 TBL_2_COL_2
----------- ---------- -------------
91129 236747 F
91129 401046 F
91129 446609 F
91129 446610 F
Any Ideas?
December 29, 2008 at 11:19 am
It is better to write a script that creates the tables and inserts data into the table instead of drawing the table and the columns' values. Pleas have a look at the script that I wrote (which includes the creation of the table and insert statements to make it easier to check it) and also check out the URL at my signature to a post that shows how to ask questions.
As for your question - The script bellow has a query that does what you asked
create table tbl_1 (col_1 int, col_2 int)
go
insert into tbl_1 (col_1, col_2)
select 91129, 236747
union select 91129, 401046
union select 91129, 446609
union select 91129, 446610
union select 1, 1
go
create table tbl_2 (col_1 int, col_2 char(1))
go
insert into tbl_2 (col_1, col_2)
select 236747, 'F'
union select 401046, 'F'
union select 446609, 'M'
union select 446610, 'M'
union select 1, 'm'
go
with CheckMinCol2 as
(select tbl_1.col_1, min(tbl_2.col_2) as col_2
from tbl_1 inner join tbl_2 on tbl_1.col_2 = tbl_2.col_1
group by tbl_1.col_1)
select CheckMinCol2.col_1, tbl_2.Col_1, CheckMinCol2.col_2
from tbl_1 inner join tbl_2 on tbl_1.col_2 = tbl_2.col_1
inner join CheckMinCol2 on tbl_1.col_1 = CheckMinCol2.col_1
go
drop table tbl_1
go
drop table tbl_2
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 29, 2008 at 1:08 pm
Thanks for the code snippet - it works really well. Also, thanks for the link to 'Best Practises' when posting a question. Very helpful and something I'll remember next time.
December 29, 2008 at 6:57 pm
Good form, Adi. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply