August 11, 2011 at 12:47 pm
========================
Table structure
======================
create table test (
country int,
zone int,
branch int,
user1 varchar(20),
)
i have two records
country,zone ,branch ,user
9 1 1 farooq
9 1 1 jawwad
i want this output
country zone branch user user2
9 1 1 farooq jawwad
using self join
August 11, 2011 at 12:52 pm
Sounds like homework. What have you tried and where are you getting stuck?
August 11, 2011 at 1:08 pm
agree this sounds like homework... appreciate your resourcefulness. Hope your professor doesnt check sql server central.
select a.country,a.zone,a.branch,a.user1 as 'user', b.user1 as user2
from test a, test b
where a.user1='farooq'
and b.user1='jawwad'
August 11, 2011 at 1:20 pm
You might want to read up in left join.
August 11, 2011 at 1:23 pm
NJ-DBA (8/11/2011)
select a.country,a.zone,a.branch,a.user1 as 'user', b.user1 as user2from test a, test b
where a.user1='farooq'
and b.user1='jawwad'
Um, don't use this code. It's outdated, deprecated, and will disappear from SQL Server in the future without warning.
August 11, 2011 at 1:31 pm
... which he would read about in books online.
August 11, 2011 at 1:36 pm
Brandie Tarvin (8/11/2011)
NJ-DBA (8/11/2011)
select a.country,a.zone,a.branch,a.user1 as 'user', b.user1 as user2from test a, test b
where a.user1='farooq'
and b.user1='jawwad'
Um, don't use this code. It's outdated, deprecated, and will disappear from SQL Server in the future without warning.
It's not that it's deprecated, that syntax is not (you can't really deprecate a cross join, it's *= that's been removed). It's that it won't work properly if there are multiple matching rows in the tables. It also doesn't answer the question. The teacher wants a self-join (though no idea on what), that query uses a cross join.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 11, 2011 at 1:49 pm
For the record, I thought it was deprecated and gave him that answer on purpose!... didnt realize though that it is not technically a "self join".
August 11, 2011 at 1:55 pm
NJ-DBA (8/11/2011)
For the record, I thought it was deprecated and gave him that answer on purpose!... didnt realize though that it is not technically a "self join".
Don't feel bad, she has to tech edit a few of my replies more often that I'd like to admit! 😉
August 11, 2011 at 11:03 pm
myquery
=============
select distinct a.country,a.zone,a.branch,a.user1,' ' as 'user2'
from test a ,test b
where
a.country=b.country
anda.zone=b.zone
and a.branch=b.branch
guys i have this two records of users of same country ,zone ,branch
e.g
9,1,1,farooq
9,1,1,jawwad
now i want ouput in one row
e.g
9,1,1,farooq,jawwad
my above query is not working.... self joing is the solution but m nt getting the query ryt!!
August 11, 2011 at 11:16 pm
hey guys i have figure it out.....and got the correct query thanx anyways 🙂
August 11, 2011 at 11:28 pm
faruk.arshad (8/11/2011)
hey guys i have figure it out.....and got the correct query thanx anyways 🙂
Great, forum etiquette would have you post your solution so that others may learn. Also, you may also learn as others may show you other (perhaps better) alternatives to your solution.
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply