SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 join columns from different tables
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

faebsi
Starting Member

10 Posts

Posted - 01/29/2009 :  15:38:31  Show Profile  Reply with Quote
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.

Edited by - faebsi on 01/29/2009 17:12:58

tonymorell10
Yak Posting Veteran

USA
90 Posts

Posted - 01/29/2009 :  15:42:30  Show Profile  Reply with Quote
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
Go to Top of Page

sodeep
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/29/2009 :  15:42:41  Show Profile  Reply with Quote
We need to see sample data and expected output.
Go to Top of Page

faebsi
Starting Member

10 Posts

Posted - 01/29/2009 :  15:50:09  Show Profile  Reply with Quote
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?
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 01/29/2009 :  16:06:48  Show Profile  Reply with Quote
quote:
Originally posted by sodeep

We need to see sample data and expected output.



And DDL for the tables....

Terry

-- Procrastinate now!
Go to Top of Page

faebsi
Starting Member

10 Posts

Posted - 01/29/2009 :  16:10:33  Show Profile  Reply with Quote
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.
Go to Top of Page

tosscrosby
Aged Yak Warrior

USA
676 Posts

Posted - 01/29/2009 :  16:20:20  Show Profile  Reply with Quote
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!

Edited by - tosscrosby on 01/29/2009 16:20:53
Go to Top of Page

faebsi
Starting Member

10 Posts

Posted - 01/29/2009 :  16:27:07  Show Profile  Reply with Quote
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..
Go to Top of Page

sital
Yak Posting Veteran

89 Posts

Posted - 01/29/2009 :  23:21:57  Show Profile  Reply with Quote
Try using the join operator.

I think that will help you solve your problem.
Go to Top of Page

faebsi
Starting Member

10 Posts

Posted - 01/30/2009 :  01:33:37  Show Profile  Reply with Quote
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..)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/30/2009 :  01:42:53  Show Profile  Reply with Quote
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
Go to Top of Page

faebsi
Starting Member

10 Posts

Posted - 01/30/2009 :  01:49:03  Show Profile  Reply with Quote
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?
Go to Top of Page

RickD
Slow But Sure Yak Herding Master

United Kingdom
3608 Posts

Posted - 01/30/2009 :  08:49:54  Show Profile  Reply with Quote
http://www.learn-sql-tutorial.com/DatabaseBasics.cfm

Look under Relationships.
Go to Top of Page

Transact Charlie
Flowing Fount of Yak Knowledge

United Kingdom
3451 Posts

Posted - 02/02/2009 :  07:36:08  Show Profile  Visit Transact Charlie's Homepage  Reply with Quote
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

Edited by - Transact Charlie on 02/02/2009 07:37:51
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.3 seconds. Powered By: Snitz Forums 2000