join columns from different tables

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

join columns from different tables

Author

Topic

faebsi
Starting Member

10 Posts

Posted - 2009-01-29 : 15:38:31

hi guys

hope your doing great, just started with sql last week and im having a question..

situation:
got 4 selects .. each select shows 1 column with 20 results

now i want to join the 4 selects.. that the results is also 20 but with 4 columns (each select represents a column)

how can i do that?

thanks in advance for your advice.

tonymorell10
Yak Posting Veteran

90 Posts

Posted - 2009-01-29 : 15:42:30

Union the selects together:
SELECT col1, col2...col20 FROM table1
UNION ALL
SELECT col1, col2...col20 FROM table2
UNION ALL
SELECT col1, col2...col20 FROM table3
UNION ALL
SELECT col1, col2...col20 FROM table4

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 15:42:41

We need to see sample data and expected output.

faebsi
Starting Member

10 Posts

Posted - 2009-01-29 : 15:50:09

quote:
Originally posted by tonymorell10

Union the selects together:
SELECT col1, col2...col20 FROM table1
UNION ALL
SELECT col1, col2...col20 FROM table2
UNION ALL
SELECT col1, col2...col20 FROM table3
UNION ALL
SELECT col1, col2...col20 FROM table4

Nope that doesnt work. .in that case ill get all entries in one column -> 40 results. instead of 2 columns of 20 entries each.

ok sample.. here 3 selects..

SELECT date
FROM test_table
WHERE date IS NOT NULL

->> 20 entries, 1 column (every entry is a date)

SELECT time
FROM test_table2
WHERE time IS NOT NULL

->> 20 entries, 1 column (every entry is a time)

SELECT action
FROM test_table3
WHERE action IS NOT NULL

->> 20 entries, 1 colomn (every entry is a action text)

Now the results are ordered correctly. But I have 3 select and each selects gives a column. now how can I make up a select which shows me all three colums next to another?

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-29 : 16:06:48

quote:
Originally posted by sodeep

We need to see sample data and expected output.

And DDL for the tables....

Terry

-- Procrastinate now!

faebsi
Starting Member

10 Posts

Posted - 2009-01-29 : 16:10:33

hi.. thanks for the answer.. but dont think thats relevant for solution.

actually im just asking if you can take one column from table x and one column from table y and then create a table z with two columns, the first column would be the one from table x and the second column would be the column of table y.

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-29 : 16:20:20

Everything we've asked for IS relevant for a working solution. If your tables have ONLY one column each, you have nothing to join them on. I know in SQL2005, there is a way to assign row numbers to rows and maybe joining that way but I don't have that expertise (still on S2K).

Terry

-- Procrastinate now!

faebsi
Starting Member

10 Posts

Posted - 2009-01-29 : 16:27:07

There is in fact nothing to join them on. But the order of the entries is correct.
I've got tables with each 20 rows and each 1 row. The order of each table corresponds to the data of the others.

Example:

The second entry of the table date is: 22.12.2008
The second entry of the table time is: 12:00

those belong togehter..

sital
Yak Posting Veteran

89 Posts

Posted - 2009-01-29 : 23:21:57

Try using the join operator.

I think that will help you solve your problem.

faebsi
Starting Member

10 Posts

Posted - 2009-01-30 : 01:33:37

I cant use any join operator because i have no relationship between the columns (there is only one column in each table, there isn't a second one with an ID i could join on..)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 01:42:53

quote:
Originally posted by faebsi

I cant use any join operator because i have no relationship between the columns (there is only one column in each table, there isn't a second one with an ID i could join on..)

you cant guarantee the order of retrieval unless you've a column to order on, so your reqmnt that 2nd val of one select to 2nd val of other wont always give you desired o/p

faebsi
Starting Member

10 Posts

Posted - 2009-01-30 : 01:49:03

so there is no way to create a new table and fill data from other tables for example like in excel.. i copy&paste one column.. and copy&paste another column from another sheet. the result is.. a new table with two columns.. that must be possible in sql.

i mean with SELECT UNION.. you can add rows.. it must be something similar for columns, mustn it?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-01-30 : 08:49:54

http://www.learn-sql-tutorial.com/DatabaseBasics.cfm

Look under Relationships.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-02-02 : 07:36:08

Faebsi,

Theres a few fundamental things that you are missing here. You said that you are new to SQL so that's not a reflection on you:

1) A table with no key is not a good thing! As you have discovered there is no reliable way to form relationships with other tables. All your tables should have a key - even if that key is just a unique number with no other meaning (called a surrogate key) You can then reliably identify an individual row in your table.

2) The example you quoted has a date column and a time column. SQL server keeps track of time / dates with 1 variable == DATETIME. That is a type that is both DATE and TIME. People new to SQL often find that confusing and come up with "alternatives" (dates and times as VARCHAR's or INT's) Generally that's a really bad idea!

3) Without an ORDER BY clause there is no GUARANTEED way of returning your data -- you might have put those dates in in some sequence but SQL server doesn't guarantee that it will return it in the same sequence unless there is some way of ordering them. As you only have one column you can see that your ordering options are rather limited! Because you can't guarantee the order of your dates and times there's no reliable way to join them using a ROWNUMBER() method.

Here's what I imagine your tables look like (you can cut and paste this code into management studio and run it -- it only accesses table variavles):


DECLARE @dates TABLE ([date] CHAR(10))
DECLARE @times TABLE ([time] CHAR(5))

INSERT @dates ([date])
      SELECT '22.12.2008'
UNION SELECT '10.01.2009'
UNION SELECT '04.04.2004'
UNION SELECT '01.01.1995'

INSERT @times ([time])
      SELECT '14:51'
UNION SELECT '15:12'
UNION SELECT '10:00'
UNION SELECT '00:00'

SELECT [date] FROM @dates
SELECT [time] FROM @times

If you run this you can see that SQL server will return the rows (which it will PROBABLY order by ascending alphaNumberic order) You can see that there is no way to join the tables because you can't tell which dates should go with which times. You can change it to this:


DECLARE @dates TABLE ([Id] INT PRIMARY KEY, [date] CHAR(10))
DECLARE @times TABLE ([dateId] INT, [time] CHAR(5))

INSERT @dates ([Id], [date])
      SELECT 1, '22.12.2008'
UNION SELECT 2, '10.01.2009'
UNION SELECT 3, '04.04.2004'
UNION SELECT 4, '01.01.1995'

INSERT @times ([dateId], [time])
      SELECT 4, '14:51'
UNION SELECT 1, '15:12'
UNION SELECT 2, '10:00'
UNION SELECT 3, '00:01'

SELECT * FROM @dates
SELECT * FROM @times

SELECT
	d.[date]
	, t.[time]
	, [date] + ' ' + [time] AS [VARCHAR DATETIME]
	, CONVERT(DATETIME, [date] + ' ' + [time], 104) AS [PROPER DATETIME]
FROM
	@dates d
	JOIN @times t ON t.[dateId] = d.[ID]

You should see from this example that will dates and time you have to mess around with the data to get what you want. it would have been far better to start with a DATETIME this leads on to:

4) SQL server is picky about date formats -- depending on the LOCALE of the server (and database) then different formats are used for dates. For example in the US it is YYYY-DD-MM. in the UK : YYYY-MM-DD. There are 2 ISO standard strings that you can use so that you don't have to worry about locale they are:


-- SHORT FORM ('YYYYMMDD') (date only -- time is auto 00:00:00

-- LONG FORM ('YYYY-MM-DDTHH:MM:SS) (date and time).

I hope this helps you.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources