sql query problem urgenttt - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql query problem urgenttt

Author

Topic

pkuchaliya
Starting Member

49 Posts

Posted - 2008-07-12 : 05:54:39

hi,

I have table like

create table xyz(id varchar(50),title varchar(50),skills varchar(50))

create table detail(id varchar(50),profileid varchar(50),applied_by datetime)

values in xyz :
insert into xyz values
('PANJOB000000000001','Sr. Software Developer','Asp.net ,C#,VB.net, Sql Server 2000/2005/2008')

insert into xyz values
('PANJOB000000000002','Software Engineer','ASP.NET, C#, Java')

insert into xyz values
('PANJOB000000000003','Web Designer','HTML, JAVASCRIPT, CSS')

insert into xyz values
('PANJOB000000000004','Web Programmer','HTML, JAVASCRIPT, CSS')

insert into xyz values
('PANJOB000000000005','designer','HTML, JAVASCRIPT, CSS')

and values in detail:-
insert into detail values('PANJOB000000000001','piku','2007-07-12')

insert into detail values('PANJOB000000000002','sanjeet','2007-07-10')

insert into detail values('PANJOB000000000003','piku','2007-07-06')

piku name record in detail table with "PANJOB000000000001" and "PANJOB000000000003" ids and sanjeet "PANJOB000000000002" id

but in xyz contail "PANJOB000000000001","PANJOB000000000002","PANJOB000000000003"
,"PANJOB000000000004","PANJOB000000000005"

and i need the result:-

'PANJOB000000000002' 'Software Engineer' 'ASP.NET/C#/Java' 'piku'
'PANJOB000000000004','Web Programmer','HTML,JAVASCRIPT,CSS','piku'
'PANJOB000000000005','designer','HTML,JAVASCRIPT,CSS','piku'
'PANJOB000000000001','Sr. Software Developer','Asp.net,C#,VB.net,Sql Server2000/2005/2008','sanjeet'
'PANJOB000000000002','Software Engineer','ASP.NET,C#,Java','sanjeet'
'PANJOB000000000004','Web Programmer','HTML, JAVASCRIPT, CSS','sanjeet'
'PANJOB000000000005','designer','HTML,JAVASCRIPT,CSS','sanjeet'

pankaj

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-12 : 08:19:23

[code]SELECT m.*
FROM
(
SELECT n.Name,i.*
FROM
(SELECT DISTINCT Name FROM detail)n
CROSS JOIN xyz i
)m
LEFT JOIN detail d
ON d.id=m.id
AND d.Name=m.Name
WHERE d.id IS NULL
AND d.Name IS NULL[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources