Click here to Skip to main content
14,265,708 members
Rate this:
Please Sign up or sign in to vote.
See more:
i have two tables tblUserDetail and tblOrder.
the field of these are below
tblUserDetail-
id(int) username(nvarchar) orderid(nvarchar)
1 john 1
2 smith 2,3

tblOrder-
orderid(int) trainno(int)
1 12520
2 12521
3 12522


i want the following ouput
orderid username trainno
1 john 12520
2 smith 12521
3 smith 12522

i use the following join query
SELECT ,tblOrder.OrderId tblUserDetail.UserName, tblOrder.TrainNo
FROM tblOrder INNER JOIN tblUserDetail ON   ( tblUserDetail.OrderId)=convert(nvarchar, tblOrder.OrderId)

that's output is
orderid username trainno
1 john 12520

the last two rows in not fetch. please suggest me new query or edit this query so i get my desired output.
Posted
Updated 5-Dec-13 0:55am
v2
Comments
   
What type of Relationship you have between these tables?

tblUserDetail to tblOrder (1:N) ?
Arvind Labhi 5-Dec-13 7:34am
   
YES I HAVE 1:N RELATION
   
Please check my answer. :)
Madhu Nair 5-Dec-13 7:45am
   
I don't think it is possible to show data the way you wanted as you are storing order id comma separated instead of separate rows
Rate this:
Please Sign up or sign in to vote.

Solution 1

As you have a 1:N Relation from tblUserDetail to tblOrder, so you should have a Column in tblOrder table storing UserID, instead of having a Column orderid in tblUserDetail table.

So, the tables should look like below...

tblUserDetail
id(int)  username(nvarchar) 
1          john           
2          smith

tblOrder
orderid(int) trainno(int)  UserID(int)
1              12520           1
2              12521           2
3              12522           2

Now, for the output you needed, the query will be something like below...
SELECT 
    tblOrder.OrderId, 
    tblUserDetail.UserName, 
    tblOrder.TrainNo
FROM 
    tblOrder 
INNER JOIN 
    tblUserDetail 
ON  tblUserDetail.id = tblOrder.UserID
Comments
Maciej Los 5-Dec-13 17:47pm
   
Good advice ;)
+5!
   
Thanks a lot Maciej Los... :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

HI
Please follow these steps..

Create an user defined funtion as below..


create FUNCTION [dbo].[Split](@String varchar(8000), @Delimiter char(1))
returns @temptable TABLE (DU varchar(8000))
as
begin
   declare @idx int
    declare @slice varchar(8000)

    select @idx = 1
        if len(@String)<1 or @String is null  return

    while @idx!= 0
    begin
        set @idx = charindex(@Delimiter,@String)
        if @idx!=0
           set @slice = left(@String,@idx - 1)
        else
            set @slice = @String

        if(len(@slice)>0)
            insert into @temptable(DU) values(@slice)

       set @String = right(@String,len(@String) - @idx)
        if len(@String) = 0 break
    end
return
end


--select * from dbo.split('1,2',',')


________________________________________________________________________________________________

Use this below query to perform your action...



 declare @tble table 
 ( id int ,
 username nvarchar(50),
 orderid nvarchar(50) )

 declare  @id int , @user nvarchar(50) , @orderid nvarchar(50)
 
DECLARE @cur    CURSOR
SET @cur = CURSOR FAST_FORWARD
 FOR SELECT * FROM tblUserDetail
OPEN @cur
FETCH NEXT
FROM @cur INTO @id,@user,@orderid
WHILE @@FETCH_STATUS = 0
BEGIN

 declare @tmp table ( id int)
 delete @tmp
   insert into @tmp select * from  dbo.split(@orderid,',')
   
    declare @idd int
    declare @cur_inner cursor 
    set @cur_inner = cursor fast_forward
    for select * from @tmp 
    open @cur_inner 
    fetch next from @cur_inner into @idd 
    WHILE @@FETCH_STATUS = 0
BEGIN
 
  insert into @tble (id,orderid ,username) values ( @id, @idd ,@user) 

  
fetch next from @cur_inner into @idd 
END
CLOSE @cur_inner
DEALLOCATE @cur_inner 
 
 
 
FETCH NEXT
FROM @cur INTO @id,@user,@orderid
END
CLOSE @cur
DEALLOCATE @cur

select t.id,t.orderid,t.username,ord.trainno  from @tble t inner join tblorder ord 
on t.orderid = ord.orderid
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

There are several ways to achieve that. If you ask, it means you did not read CP QA rules[^].

Please, use SearchBox[^] at the top-right corner of this site next time.
   
Rate this:
Please Sign up or sign in to vote.

Solution 4

you have to go with some tricky way with this type of query..
<pre lang="sql">SELECT
tblOrder.OrderId,
tblUserDetail.UserName,
tblOrder.TrainNo
FROM tblOrder
INNER JOIN tblUserDetail ON
'#' + REPLACE(tblUserDetail.OrderId, ',','#') + '#' like '%#' + convert(nvarchar(100), tblOrder.OrderId) + '#%'
   

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




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100