simple join but i don't know how to do it

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

SQL Server 2000 Forums

SQL Server Development (2000)

simple join but i don't know how to do it

Author

Topic

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 14:52:06

i have two tables with two columns each

one has

A V1
A V2

the other has

A V3
A V4
B V1
B V2

i want to get the result to look like

A V1
A V2
A V3
A V4

please help

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 14:58:29

SELECT *
FROM Table1
UNION ALL
SELECT *
FROM Table2
WHERE Column1 = 'A'

Tara

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-09-24 : 14:59:55

this is a long shot but maybe:

select 'A', col2
from Table2
order by col2

Go with the flow & have fun! Else fight the flow

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 15:12:06

tduggan Tara
yes but my table has Hundreds of ID's like 'A' so i can't say Column1='A'

it has to be in terms of the other table columns

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 15:14:36

Then please provide a better example so that we may help you as I don't understand what you need.

Tara

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 15:22:15

SELECT DISTINCT dbo.JobSubmition.job , dbo.Dp3completed.version
FROM dbo.Dp3completed CROSS JOIN
dbo.JobSubmition

this works, but i was wandering if its possible to do it without a cross join

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 15:23:47

Please explain what you are trying to do. Do you just want a cartesian product? If so, then CROSS JOIN is the best way to go.

Tara

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 15:26:37

uups actually it doesn't work, if i add
B V3 in the first table

the results turn out to be

A V1
A V2
A v3
A v4
B V1
B V2
B V3
B V4

only that there is not B V4 it should end at B V3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 15:28:43

Please explain what you are trying to do with better sample data and expected result set. The solution that I provided works for what information you have provided so far. Since that isn't working in your environment, we need to see more information.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-24 : 15:34:46

[code]
USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 char(1), Col2 char(2))
CREATE TABLE myTable00(Col1 char(1), Col2 char(2))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 'A', 'V1' UNION ALL
SELECT 'A', 'V2'

INSERT INTO myTable00(Col1,Col2)
SELECT 'A', 'V3' UNION ALL
SELECT 'A', 'V4' UNION ALL
SELECT 'B', 'V1' UNION ALL
SELECT 'B', 'V2' UNION ALL
SELECT 'B', 'V3'
GO

SELECT Col1, Col2
FROM myTable99
UNION ALL
SELECT Col1, Col2
FROM myTable00
ORDER BY Col1, Col2
GO

SET NOCOUNT OFF
DROP TABLE myTable99
DROP TABLE myTable00
GO

[/code]

Brett

8-)

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 15:44:56

the union should only return rows that have ID's in myTable99

for example
your union should just return
A V1
A V2
A V3
A V4
that's it

if on the other had there is a row in myTable99 that has an ID of B then all B's from myTable00 should be returned

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 15:46:38

I don't mean ID's per say but it should only return rows where Column1 matches

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 16:00:28

Ok i got

SELECT *
FROM myTable00
where EXISTS (SELECT * from myTable99)

union
Select * from myTable99

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 16:08:22

is there a way that this can be done without a union???

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 16:09:50

Why don't you like the union? The union is required due to your table design.

Tara

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 16:16:33

ok made a mistake
its similar though

SELECT *
FROM myTable00
where Col1 in (SELECT Col1 from myTable99)
union
Select * from myTable99

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 16:17:26

Still is there a way to do this without a union?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 16:19:49

Why don't you like the union? Do you see it as a performance concern or something? Please explain.

The reason why you need the union is due to your table design.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2004-09-24 : 16:28:56

Sure....with T-SQL, there's always another way to do the same thing...

But why bother...and in the future, post your question with code...

Makes for an easier discussion


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 char(1), Col2 char(2))
CREATE TABLE myTable00(Col1 char(1), Col2 char(2))
GO

INSERT INTO myTable99(Col1,Col2)
SELECT 'A', 'V1' UNION ALL
SELECT 'A', 'V2'

INSERT INTO myTable00(Col1,Col2)
SELECT 'A', 'V3' UNION ALL
SELECT 'A', 'V4' UNION ALL
SELECT 'B', 'V1' UNION ALL
SELECT 'B', 'V2' UNION ALL
SELECT 'B', 'V3'
GO

   SELECT Col1, Col2 INTO myTable77 
     FROM myTable99
INSERT INTO myTable77(Col1, Col2)
   SELECT Col1, Col2 
     FROM myTable00 o 
    WHERE EXISTS (SELECT * 
		    FROM myTable99 i 
		   WHERE o.Col1 = i.Col1) 
   SELECT Col1, Col2 
     FROM myTable77
 ORDER BY Col1, Col2
GO

SET NOCOUNT ON
DROP TABLE myTable99
DROP TABLE myTable00
DROP TABLE myTable77
GO

Brett

8-)

iancuct
Yak Posting Veteran

73 Posts

Posted - 2004-09-24 : 16:58:57

yeah i thought it would be a performance concern, but i don't think it would be a big deal as it will only be used for a report. Yeah i think its because of poor design of the whole thing, but that is what i have inherited when i got the job. Basicly some guy that didn't know much did all this in like 20 access databases (applications) moving data around... its a mess

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-09-24 : 17:00:48

Just make sure you only pull the data that you need from the tables involved in the union. So if you can use a WHERE clause, then use one. And don't use SELECT * if you are concerned by performance.

Tara

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources