February 1, 2012 at 6:27 am
I have two tables.suppose i have table1 columns-Marks int and in table2 -comments varchar(10) and i have different no of rows in both tables.I want to combine and display of two tables in a single result.Eg: output is: marks column,comments column in single result. i have tried by adding identity columns in two tables and using full join i'm getting results. Is there any way we can combine these two tables without using joins for getting the result?
February 1, 2012 at 6:34 am
dinesh8804 (2/1/2012)
I have two tables.suppose i have table1 columns-Marks int and in table2 -comments varchar(10) and i have different no of rows in both tables.I want to combine and display of two tables in a single result.Eg: output is: marks column,comments column in single result. i have tried by adding identity columns in two tables and using full join i'm getting results. Is there any way we can combine these two tables without using joins for getting the result?
Hello and welcome to SSC!
I'd like to be able to help you, but it seems that the DDL and sample data that you helpfully provided has become detached from your post at the time you posted your question!! It happens, so we won't worry over-much about it.
Hopefully it will magically reappear. In the mean-time, it would be extremely useful if you could read this article[/url] about the best way to post DDL and sample data then edit your post to include it. That way, the unpaid volunteers of this wonderful forum will be able to provide you with tested, working code.
Thanks!
February 1, 2012 at 6:37 am
Use a UNION ALL --> in case you want all records from both the tables.
Use a UNION --> in case you want to eliminate duplicate records.
February 1, 2012 at 7:42 am
arun1_m1 (2/1/2012)
Use a UNION ALL --> in case you want all records from both the tables.Use a UNION --> in case you want to eliminate duplicate records.
That won't work, since he needs them to be joined as columns (side-by-side), not as appended rows, per the original description.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 7:45 am
dinesh8804 (2/1/2012)
I have two tables.suppose i have table1 columns-Marks int and in table2 -comments varchar(10) and i have different no of rows in both tables.I want to combine and display of two tables in a single result.Eg: output is: marks column,comments column in single result. i have tried by adding identity columns in two tables and using full join i'm getting results. Is there any way we can combine these two tables without using joins for getting the result?
There are ways to do that without using Join operators, but they usually just take a lot more work to get the same result.
Usually (almost always) it's easier to just use Join for this.
The best way to do it will depend on what exactly you want. Is there something in either table that indicates which rows in it go with which rows in the other table, or something you know makes the rows match each other, or do you want each row in the first table joined to every row in the second table?
As mentioned, knowing what columns are in the tables would definitely help us figure out the best way to do this.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 1, 2012 at 11:13 am
Question still is - why would you WANT to? As in - why would you not use the best and most obvious methods?
Knowing the structure doesn't hurt, but having some background on the odd requirement might also help.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 2, 2012 at 1:02 am
create table t1(marks int)
create table t2(comments varchar(10))
insert into t1 values(100)
insert into t1 values(200)
insert into t2 values('d')
insert into t2 values('i')
I have created two tables t1 and t2.i have different two columns in tables..Now i want to combine these two results without usin join.
alter table t1 add no int identity(1,1)
alter table t2 add no1 int identity(1,1)
i have added two identity columns for the purpose of joining those two tables.
if i have using this this query i have getting the result:
select t1.no,t1.marks,t2.no1,t2.comments from t1 full join t2 on t1.no=t2.no1
and my result is:
no marks no1 comments
11001d
22002i
i want to combine those two tables without using joins or without adding identity columns.. Is it possible to combine those two tables without using joins?
February 2, 2012 at 2:22 am
BEGIN TRAN
CREATE TABLE t1 (marks INT)
CREATE TABLE t2 (comments VARCHAR(10))
INSERT INTO t1
VALUES (100)
INSERT INTO t1
VALUES (200)
INSERT INTO t2
VALUES ('d')
INSERT INTO t2
VALUES ('i')
--The results here can't be guaranteed because SQL Server has no
--physical order that you can rely on. Instead, you need to add
--a foreign key to each set which allows you to join.
--For giggles, here is a way that will allow you to do what you're asking
--without a key, BUT IT IS NOT A GUARANTEED RESULT SO SHOULD NOT BE
--USED IN PRODUCTION
SELECT
MAX(CASE WHEN coltype = 'marks' THEN col ELSE NULL END) AS marks,
MAX(CASE WHEN coltype = 'comments' THEN col ELSE NULL END) AS comments
FROM (SELECT CAST(marks AS VARCHAR(200)) AS col, 'marks' AS coltype,
(SELECT COUNT(*) FROM t1 cnt WHERE cnt.marks <= a.marks) AS rowNumber
FROM t1 a
UNION ALL
SELECT CAST(comments AS VARCHAR(200)) AS col, 'comments' AS coltype,
(SELECT COUNT(*) FROM t2 cnt WHERE cnt.comments <= a.comments) AS rowNumber
FROM t2 a) combine
GROUP BY rowNumber
ROLLBACK
Makes sure you read the comments, this code should not be used.
Result: -
marks comments
------- ---------
100 d
200 i
February 3, 2012 at 6:31 am
dinesh8804 (2/2/2012)
create table t1(marks int)create table t2(comments varchar(10))
insert into t1 values(100)
insert into t1 values(200)
insert into t2 values('d')
insert into t2 values('i')
I have created two tables t1 and t2.i have different two columns in tables..Now i want to combine these two results without usin join.
alter table t1 add no int identity(1,1)
alter table t2 add no1 int identity(1,1)
i have added two identity columns for the purpose of joining those two tables.
if i have using this this query i have getting the result:
select t1.no,t1.marks,t2.no1,t2.comments from t1 full join t2 on t1.no=t2.no1
and my result is:
no marks no1 comments
11001d
22002i
i want to combine those two tables without using joins or without adding identity columns.. Is it possible to combine those two tables without using joins?
It is possible to do this, but it's not simple.
Relational databases aren't spreadsheets. There is no "first row", unless there is something in the table that you can use to determine which row is first.
So, honestly, I have to say what you have defined isn't a database. It's two pages in a spreadsheet. Use it that way, don't bother with a database. Excel will what you're looking for better than a relational database (SQL Server, Oracle, MySQL, PostreSQL, whatever).
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2012 at 8:49 am
have you tried cross join...simply
select * from tableA,tableB
February 3, 2012 at 9:23 am
ashutosh.kumarpandey (2/3/2012)
have you tried cross join...simplyselect * from tableA,tableB
That (a) won't give the results he outlined, and (b) uses a Join, even if it avoids using that word.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 3, 2012 at 9:35 am
I know but a) cross join does not require any common key between the two table b) it will increase the number of column in the output which I though is the requirement otherwise he could have simply used UNION
February 3, 2012 at 9:53 am
ashutosh.kumarpandey (2/3/2012)
I know but a) cross join does not require any common key between the two table b) it will increase the number of column in the output which I though is the requirement otherwise he could have simply used UNION
Hmmm, let's look at the OPs requirements shall we?
dinesh8804 (2/2/2012)
create table t1(marks int)create table t2(comments varchar(10))
insert into t1 values(100)
insert into t1 values(200)
insert into t2 values('d')
insert into t2 values('i')
I have created two tables t1 and t2.i have different two columns in tables..Now i want to combine these two results without usin join.
alter table t1 add no int identity(1,1)
alter table t2 add no1 int identity(1,1)
i have added two identity columns for the purpose of joining those two tables.
if i have using this this query i have getting the result:
select t1.no,t1.marks,t2.no1,t2.comments from t1 full join t2 on t1.no=t2.no1
and my result is:
no marks no1 comments
11001d
22002i
i want to combine those two tables without using joins or without adding identity columns.. Is it possible to combine those two tables without using joins?
BEGIN TRAN
CREATE TABLE t1 (marks INT)
CREATE TABLE t2 (comments VARCHAR(10))
INSERT INTO t1
VALUES (100)
INSERT INTO t1
VALUES (200)
INSERT INTO t2
VALUES ('d')
INSERT INTO t2
VALUES ('i')
PRINT 'Your query'
SELECT * FROM t1,t2
PRINT 'My query'
SELECT
MAX(CASE WHEN coltype = 'marks' THEN col ELSE NULL END) AS marks,
MAX(CASE WHEN coltype = 'comments' THEN col ELSE NULL END) AS comments
FROM (SELECT CAST(marks AS VARCHAR(200)) AS col, 'marks' AS coltype,
(SELECT COUNT(*) FROM t1 cnt WHERE cnt.marks <= a.marks) AS rowNumber
FROM t1 a
UNION ALL
SELECT CAST(comments AS VARCHAR(200)) AS col, 'comments' AS coltype,
(SELECT COUNT(*) FROM t2 cnt WHERE cnt.comments <= a.comments) AS rowNumber
FROM t2 a) combine
GROUP BY rowNumber
/***********************---------Results--------
************************
Your query
========================
marks comments
----------- ----------
100 d
200 d
100 i
200 i
========================
========================
My query
========================
marks comments
-------- ------------
100 d
200 i
========================
========================
OP Requirement
========================
no marks no1 comments
1 100 1 d
2 200 2 i
========================
========================
\***********************/
PRINT 'Correcting my query to include the "no" and "no1" data'
SELECT
rowNumber AS [no],
MAX(CASE WHEN coltype = 'marks' THEN col ELSE NULL END) AS marks,
rowNumber AS [no1],
MAX(CASE WHEN coltype = 'comments' THEN col ELSE NULL END) AS comments
FROM (SELECT CAST(marks AS VARCHAR(200)) AS col, 'marks' AS coltype,
(SELECT COUNT(*) FROM t1 cnt WHERE cnt.marks <= a.marks) AS rowNumber
FROM t1 a
UNION ALL
SELECT CAST(comments AS VARCHAR(200)) AS col, 'comments' AS coltype,
(SELECT COUNT(*) FROM t2 cnt WHERE cnt.comments <= a.comments) AS rowNumber
FROM t2 a) combine
GROUP BY rowNumber
/***********************---------Results--------
************************
========================
My query
========================
no marks no1 comments
----------- ------- ------- ------------
1 100 1 d
2 200 2 i
========================
========================
\***********************/
ROLLBACK
As I said before though, the results here can't be guaranteed because SQL Server has no physical order that you can rely on. Instead, we need to add a foreign key to each set which allows you to join.
February 3, 2012 at 10:26 am
To do this without joins, you can use this deprecated, but still useful sintaxis:
SELECT a.*, b.*
FROM Table1 a, Table2 b
Best regards!
February 3, 2012 at 10:59 am
Narud (2/3/2012)
To do this without joins, you can use this deprecated, but still useful sintaxis:
SELECT a.*, b.*FROM Table1 a, Table2 b
Best regards!
Oh for the love of. . .
1) That is still a join
2) It doesn't produce the results that the OP wanted anyway
See this code: -
BEGIN TRAN
CREATE TABLE t1 (marks INT)
CREATE TABLE t2 (comments VARCHAR(10))
INSERT INTO t1
VALUES (100)
INSERT INTO t1
VALUES (200)
INSERT INTO t2
VALUES ('d')
INSERT INTO t2
VALUES ('i')
PRINT 'Your query'
SELECT * FROM t1,t2
PRINT 'My query'
SELECT
MAX(CASE WHEN coltype = 'marks' THEN col ELSE NULL END) AS marks,
MAX(CASE WHEN coltype = 'comments' THEN col ELSE NULL END) AS comments
FROM (SELECT CAST(marks AS VARCHAR(200)) AS col, 'marks' AS coltype,
(SELECT COUNT(*) FROM t1 cnt WHERE cnt.marks <= a.marks) AS rowNumber
FROM t1 a
UNION ALL
SELECT CAST(comments AS VARCHAR(200)) AS col, 'comments' AS coltype,
(SELECT COUNT(*) FROM t2 cnt WHERE cnt.comments <= a.comments) AS rowNumber
FROM t2 a) combine
GROUP BY rowNumber
/***********************---------Results--------
************************
Your query
========================
marks comments
----------- ----------
100 d
200 d
100 i
200 i
========================
========================
My query
========================
marks comments
-------- ------------
100 d
200 i
========================
========================
OP Requirement
========================
no marks no1 comments
1 100 1 d
2 200 2 i
========================
========================
\***********************/
PRINT 'Correcting my query to include the "no" and "no1" data'
SELECT
rowNumber AS [no],
MAX(CASE WHEN coltype = 'marks' THEN col ELSE NULL END) AS marks,
rowNumber AS [no1],
MAX(CASE WHEN coltype = 'comments' THEN col ELSE NULL END) AS comments
FROM (SELECT CAST(marks AS VARCHAR(200)) AS col, 'marks' AS coltype,
(SELECT COUNT(*) FROM t1 cnt WHERE cnt.marks <= a.marks) AS rowNumber
FROM t1 a
UNION ALL
SELECT CAST(comments AS VARCHAR(200)) AS col, 'comments' AS coltype,
(SELECT COUNT(*) FROM t2 cnt WHERE cnt.comments <= a.comments) AS rowNumber
FROM t2 a) combine
GROUP BY rowNumber
/***********************---------Results--------
************************
========================
My query
========================
no marks no1 comments
----------- ------- ------- ------------
1 100 1 d
2 200 2 i
========================
========================
\***********************/
ROLLBACK
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply