| Author |
 Topic  |
|
|
jfm
Posting Yak Master
134 Posts |
 Posted - 05/10/2012 : 11:02:42
|
Hi guys,
I have T1, T2
Columns that I have in those tables:
T1: A, B
T2: Z (this column whether contains info from A, B,C or D)
I need to create a new table, using the info that column Z has equal from columns A or B. Eliminating the repetitions.
Thanks! |
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 05/10/2012 : 11:17:08
|
Not 100% sure what you are looking for, but may be this?SELECT
DISTINCT X INTO NewTable
FROM
T2
INNER JOIN T1
ON T2.Z = T1.A OR T2.Z = T1.B |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 05/10/2012 : 11:57:17
|
SELECT t2.*
FROM T2 t2
INNER JOIN (SELECT val
FROM T1
UNPIVOT(val FOR Col IN ([A],[B],[C],[D]))u
)t1
ON t1.val = t2.Z
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/10/2012 : 12:09:13
|
I have:
Table1, one column called Column_ID (has the postal code of London)
Table2, two different columns:
ZIP_L
ZIP_N
But column_ID has in each cell, or ZIP_L or ZIP_N information.
What I need is a new table, that shares the info between Table1 and Table2, and give me the total output of ZIP_L and ZIP_N if is that info in Column_ID
So, I will have Column_ID, but up to the number of ZIP_L and ZIP_N info taken from Table2, and using DISCTINCT in order to eliminate the possible repetitions of the postcodes (if I have nw2, I dont want to appear nw2 as many times as I have in the previous table)
Thanks!!!!!!
I need a new table, that recognize the
DISTINCT X INTO NewTable
FROM
T2
INNER JOIN T1
ON T2.Z = T1.A OR T2.Z = T1.B[/code]
[/quote] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 05/10/2012 : 12:12:42
|
SELECT DISTINCT Column_ID INTO NewTable
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM table2
WHERE ZIP_L = t1.Column_ID
OR ZIP_N = t1.Column_ID
)
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/10/2012 : 12:21:30
|
Sorry visakhm,
WHERE EXISTS (SELECT 1?
thats correct?
sorry for asking, but FROM table1 t1, what is t1?
Many thanks
SELECT DISTINCT Column_ID INTO NewTable
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM table2
WHERE ZIP_L = t1.Column_ID
OR ZIP_N = t1.Column_ID
)
[/code]
------------------------------------------------------------------------------------------------------
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 05/10/2012 : 12:26:10
|
quote:
Originally posted by jfm
Sorry visakhm,
WHERE EXISTS (SELECT 1?
thats correct?
sorry for asking, but FROM table1 t1, what is t1?
Many thanks
SELECT DISTINCT Column_ID INTO NewTable
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM table2
WHERE ZIP_L = t1.Column_ID
OR ZIP_N = t1.Column_ID
)
[/code]
------------------------------------------------------------------------------------------------------
Please check the query and see if it gives you intended result.
the EXISTS check ensure you return only ID from table1 which exists either in ZIP_L or ZIP_N columns of Table2. DISTINCT will avoid duplicate occurances of ID from Table1 being returned. I think that was what you asked for.
The t1 is an alias ie short name for Table1 so that you dont have repeat tablename everywhere
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/10/2012 : 12:33:27
|
No I cant...
The 1 next to SELECT, is correct?
Thank you visakhm
quote:
Originally posted by visakh16
quote:
Originally posted by jfm
Sorry visakhm,
WHERE EXISTS (SELECT 1?
thats correct?
sorry for asking, but FROM table1 t1, what is t1?
Many thanks
SELECT DISTINCT Column_ID INTO NewTable
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM table2
WHERE ZIP_L = t1.Column_ID
OR ZIP_N = t1.Column_ID
)
[/code]
------------------------------------------------------------------------------------------------------
Please check the query and see if it gives you intended result.
the EXISTS check ensure you return only ID from table1 which exists either in ZIP_L or ZIP_N columns of Table2. DISTINCT will avoid duplicate occurances of ID from Table1 being returned. I think that was what you asked for.
The t1 is an alias ie short name for Table1 so that you dont have repeat tablename everywhere
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 05/10/2012 : 15:39:08
|
quote:
Originally posted by jfm
No I cant...
The 1 next to SELECT, is correct?
Thank you visakhm
quote:
Originally posted by visakh16
quote:
Originally posted by jfm
Sorry visakhm,
WHERE EXISTS (SELECT 1?
thats correct?
sorry for asking, but FROM table1 t1, what is t1?
Many thanks
SELECT DISTINCT Column_ID INTO NewTable
FROM Table1 t1
WHERE EXISTS (SELECT 1 FROM table2
WHERE ZIP_L = t1.Column_ID
OR ZIP_N = t1.Column_ID
)
[/code]
------------------------------------------------------------------------------------------------------
Please check the query and see if it gives you intended result.
the EXISTS check ensure you return only ID from table1 which exists either in ZIP_L or ZIP_N columns of Table2. DISTINCT will avoid duplicate occurances of ID from Table1 being returned. I think that was what you asked for.
The t1 is an alias ie short name for Table1 so that you dont have repeat tablename everywhere
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
why? whats the issue you're facing in checking it?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
jfm
Posting Yak Master
134 Posts |
Posted - 05/11/2012 : 05:36:59
|
It taking more than 50min to execute the query ...
If I execute another query, with the same data, it only takes 3 seconds.
There is not another way., to process the data?
Thanks
------------------------------------------------------------------------------------------------------
[/quote]
Please check the query and see if it gives you intended result.
the EXISTS check ensure you return only ID from table1 which exists either in ZIP_L or ZIP_N columns of Table2. DISTINCT will avoid duplicate occurances of ID from Table1 being returned. I think that was what you asked for.
The t1 is an alias ie short name for Table1 so that you dont have repeat tablename everywhere
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
[/quote]
[/quote]
why? whats the issue you're facing in checking it?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
[/quote] |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48076 Posts |
Posted - 05/11/2012 : 14:59:26
|
what about this?
SELECT t1.*
FROM Table1 t1
INNER JOIN (SELECT val
FROM T1
UNPIVOT(val FOR Col IN ([ZIP_L],[ZIP_N]))u
)t2
ON t2.val = t1.Column_ID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
vinu.vijayan
Posting Yak Master
India
227 Posts |
Posted - 05/14/2012 : 04:46:52
|
This could be so much easier if you could post the sample data and the Expected Result Set.
N 28° 33' 11.93148"
E 77° 14' 33.66384" |
|
|
| |
Topic |
|
FIND OUT INFO JOIN
