SQL table joining issue

Question

0.00/5 (No votes)

See more:

Hi,
I have two tables and I have tried inner joins in SQL, but instead of showing four lines, it shows eight lines.
please check the tables and desired output as below.

SQL

SELECT
LocID,
     ScheduledCompletion
FROM  table1
GROUP BY
      LocID,
      ScheduledCompletion

 SELECT
LocID1,
     ScheduledCompletion1
 FROM  table2
 GROUP BY
       LocID1,
       ScheduledCompletion1

LocID   ScheduledCompletion
100 14/01/2013 16:30
100 29/01/2013 16:30
100 30/01/2013 16:30
100 14/02/2013 16:30



LocID1  ScheduledCompletion1
100 07/12/2012 20:30
100 17/01/2013 20:04
100 29/01/2013 23:27
100 31/01/2013 20:26

Desired output

LocID	ScheduledCompletion	LocID1	ScheduledCompletion1
100	14/01/2013 16:30	           100	07/12/2012 20:30
100	29/01/2013 16:30	           100    17/01/2013 20:04
100	30/01/2013 16:30	           100	29/01/2013 23:27
100	14/02/2013 16:30	           100	31/01/2013 20:26

Would really appreciate any help. Thanks in advance.

Posted 17-Jan-14 13:06pm

chan200uk

Updated 17-Jan-14 13:39pm

CHill60

v2

Add a Solution

Comments

Yvan Rodrigues 17-Jan-14 19:19pm

I don't see what the common denominator is between the left and right sets?

Peter Leow 18-Jan-14 8:28am

This question will go away once you fixed your database design flaw.

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Accepted Answer · 2014-01-17T14:30:00

You can do inner join like this

SQL

SELECT
    LocID,   ScheduledCompletion,LocID1,  ScheduledCompletion1
    FROM  table1 t1  inner join  table2   t2 on t1.LocID = t2.LocID1

But the above query will return 16 Rows, since the
table1 contains 4 rows with LocID as 100 and
table2 contains 4 rows with LocID1 as 100

so it will Perform 4*4 operation and returns 16 Rows
------------------------------------------------------------------------------------------------------------------
TO get the result which u need you can try like this..

SQL

declare @table1 table ( LocID int , ScheduledCompletion varchar(33))
declare @table2 table ( LocID1 int , ScheduledCompletion1 varchar(33))

insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'aa')
insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'bb')
insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'cc')
insert into @table1 ( LocID, ScheduledCompletion) values ( 100 , 'dd')

insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'ee')
insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'ff')
insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'gg')
insert into @table2 ( LocID1, ScheduledCompletion1) values ( 100 , 'hh')




      ;with T1 (RowNum,LocID,ScheduledCompletion) as (
    select row_number() over (order by LocID) RowNum, LocID , ScheduledCompletion
    from @table1
),
T2 (RowNum,LocID1,ScheduledCompletion1)as (
    select row_number() over (order by LocID1) RowNum, LocID1 , ScheduledCompletion1
    from @table2
)
select a.LocID, a.ScheduledCompletion , b.LocID1 , b.ScheduledCompletion1
from T1 a
   inner join T2  b on a.RowNum = b.RowNum

ShivKrSingh · Accepted Answer · 2014-01-17T22:59:00

hello,
As much as I can see there is no relation between both result. but we can bring desire output by the following code. you need to declare to table for run time and from them you can take desire result. here he the solution for it.

SQL

Declare @tab1 table (ID int, LocID int , ScheduledCompletion varchar(100))
insert into @tab1
Select * from (select ROW_NUMBER() over(order by locID) as ID, locid, ScheduledCompletion from a1) as tab1

Declare @tab2 table (ID int, LocID1 int , ScheduledCompletion1 varchar(100))
insert into @tab2
Select * from (select ROW_NUMBER() over(order by locID1) as ID, locID1, ScheduledCompletion1 from a2) as tab2

select LocID, ScheduledCompletion, ScheduledCompletion1 from @tab1 t1 full outer join @tab2 t2 on t1.ID = t2.ID

GOOD LUCK..

SQL table joining issue

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0