FIND OUT INFO JOIN - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

FIND OUT INFO JOIN

Author

Topic

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-10 : 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

52326 Posts

Posted - 2012-05-10 : 11:57:17

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 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

52326 Posts

Posted - 2012-05-10 : 12:12:42

[code]
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]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jfm
Posting Yak Master

145 Posts

Posted - 2012-05-10 : 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

52326 Posts

Posted - 2012-05-10 : 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

145 Posts

Posted - 2012-05-10 : 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

52326 Posts

Posted - 2012-05-10 : 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

145 Posts

Posted - 2012-05-11 : 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

52326 Posts

Posted - 2012-05-11 : 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

227 Posts

Posted - 2012-05-14 : 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"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources