| Author |
 Topic  |
|
|
faebsi
Starting Member
10 Posts |
 Posted - 01/29/2009 : 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. |
Edited by - faebsi on 01/29/2009 17:12:58
|
|
|
tonymorell10
Yak Posting Veteran
USA
90 Posts |
Posted - 01/29/2009 : 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/29/2009 : 15:42:41
|
| We need to see sample data and expected output. |
|
|
|
faebsi
Starting Member
10 Posts |
Posted - 01/29/2009 : 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
USA
676 Posts |
Posted - 01/29/2009 : 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 - 01/29/2009 : 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
USA
676 Posts |
Posted - 01/29/2009 : 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! |
Edited by - tosscrosby on 01/29/2009 16:20:53 |
|
|
|
faebsi
Starting Member
10 Posts |
Posted - 01/29/2009 : 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 - 01/29/2009 : 23:21:57
|
Try using the join operator.
I think that will help you solve your problem. |
|
|
|
faebsi
Starting Member
10 Posts |
Posted - 01/30/2009 : 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
India
47023 Posts |
Posted - 01/30/2009 : 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 - 01/30/2009 : 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
United Kingdom
3560 Posts |
|
|
Transact Charlie
Flowing Fount of Yak Knowledge
United Kingdom
3425 Posts |
Posted - 02/02/2009 : 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 |
Edited by - Transact Charlie on 02/02/2009 07:37:51 |
|
|
| |
Topic |
|
join columns from different tables
