2 table - table 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

2 table - table join

Author

Topic

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 10:17:12

First off i love this forum, everyone is so helpful! :)

I have 2 tables, that have lots of data in them. Now Ive done a join where you join everything in that table, however, I want to do a join based on filtered results of those tables.

For example.

Table 1
joe - test - 1
joe - test - 2
joe - test - 3
tom - test - 1
tom - test - 2

Table 2
joe - test - 1
joe - test - 2
joe - test - 3
tom - test - 1
tom - test - 2

Can i just filter the results on each table to just the records for 'joe', and then join those tables?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:24:04

yup you can


SELECT col1,col2,col3
FROM
(SELECT col1,col2,col3
 FROM Table1
 WHERE col1='joe'
UNION ALL
 SELECT col1,col2,col3
 FROM Table2
 WHERE col1='joe'
)t

if you want unique values just use UNION instead of UNION ALL

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 10:27:02

Now, i was doing a case statment, to get a result row, can i do the same thing? if so how..
sorry im still new on sql...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 10:30:50

case statement to get rows conditionally? can you explain how exactly you want output to come?

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 10:33:22

Thanks for the fast reply, i wanted to do a case statment to show a results column at the end, like a 1 or 2.

So..
if 'Joes Group' was in table 1, and in table 2, to have results column = 0
if 'Joes Group' was in table 1, but not in table 2, to have a results column = 1
if 'Joes Group' was in table 2, but not in table 1, to have the results column = 2

Does that help?

Here is an example if this helps

Table 1
joe - test - 1
joe - test - 2
joe - test - 3
tom - test - 1
tom - test - 2

Table 2
joe - test - 1
joe - test - 2
joe - test - 5
tom - test - 1
tom - test - 2

Results
joe - test - 1 - 0
joe - test - 2 - 0
joe - test - 3 - 1
joe - test - 5 - 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:32:55

[code]
SELECT col1,col2,col3,
CASE WHEN COUNT(DISTINCT Cat) = 2 THEN 0 ELSE MAX(Cat) END AS result
FROM
(SELECT col1,col2,col3,1 AS Cat
FROM Table1
WHERE col1='joe'
UNION ALL
SELECT col1,col2,col3,2
FROM Table2
WHERE col1='joe'
)t
GROUP BY col1,col2,col3
[/code]

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 11:51:08

NICe, that worked, is there a way on to pass in a paramater for this? maybe make it a view, and pass it a paramet, instead of hard coding 'joe' in there?
Also, can i make it so only brin back result's that = 1 or 2 ? i guess i can do that in my view, but how to i pass a view a paramater?

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 11:54:44

on another note, what does the 't' to after the ) ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 11:55:11

you cant pass parameter to view. for that you need to make it procedure.

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:10:08

Is there a way to only display results that = 1 or 2 ?
i tried to put a where clause above there order by, but it still brough back all records.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:12:03

quote:
Originally posted by jjmusicpro

on another note, what does the 't' to after the ) ?

t is name given to query which forms the derived table called table alias

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:12:48

quote:
Originally posted by jjmusicpro

Is there a way to only display results that = 1 or 2 ?
i tried to put a where clause above there order by, but it still brough back all records.

you mean only those present in any one of tables?

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:26:57

yes, you are correct.

Maybe have the ability to just add a where clause to this, so based on the result column, i can only display result numbers that i specifi

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:29:53

[code]SELECT col1,col2,col3,
CASE WHEN COUNT(DISTINCT Cat) = 2 THEN 0 ELSE MAX(Cat) END AS result
FROM
(SELECT col1,col2,col3,1 AS Cat
FROM Table1
WHERE col1='joe'
UNION ALL
SELECT col1,col2,col3,2
FROM Table2
WHERE col1='joe'
)t
GROUP BY col1,col2,col3
HAVING CASE WHEN COUNT(DISTINCT Cat) = 2 THEN 0 ELSE MAX(Cat) END = @YourValue
[/code]

now declare @YourValue and pass it 1,2,0 etc

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:39:46

I want to be able to pass multiple values to @YourValue

I want to be able to look at the table as a whole soemtimes, but others just results of 1 and 2.
- 9 times out of 10 it will be just results of 1 and 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:42:49

you've only 2 values rite? 1 & 2. then what do you mean by whole?

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

jjmusicpro
Yak Posting Veteran

79 Posts

Posted - 2010-05-12 : 12:55:46

So right now, its kicking out the table as a whole, showing results that are 0,1, or 2

Results
joe - test - 1 - 0
joe - test - 2 - 0
joe - test - 3 - 1
joe - test - 5 - 2

I just wanted to put a "where" clause on it, to where i can say, only show me results that are 1 or 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-12 : 12:59:49

[code]SELECT col1,col2,col3,
CASE WHEN COUNT(DISTINCT Cat) = 2 THEN 0 ELSE MAX(Cat) END AS result
FROM
(SELECT col1,col2,col3,1 AS Cat
FROM Table1
WHERE col1='joe'
UNION ALL
SELECT col1,col2,col3,2
FROM Table2
WHERE col1='joe'
)t
GROUP BY col1,col2,col3
HAVING CASE WHEN COUNT(DISTINCT Cat) = 2 THEN 0 ELSE MAX(Cat) END IN (1,2)
[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources