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
 DUPLICATE VALUE WHEN JOINING

Author  Topic 

sathesh
Starting Member

1 Post

Posted - 2014-08-27 : 04:31:09
I am getting output two times but my table has single data then how to remove duplicate my SQL query is:
select nonregister.email as firstname,nonregister.email ,appliedjobstatuswithresumes.description,appliedjobstatuswithresumes.applieddate from nonregister , appliedjobstatuswithresumes where appliedjobstatuswithresumes.nonregid IN (select nonregid from nonregister where nonregid IN (select nonregid from [appliedjobstatuswithresumes]
WHERE [jobid] = '100'
AND [stageorderid] IN (SELECT [stageorderid] FROM [jobstages] WHERE [stageid] = '1' AND [jobid] = '100'))) and nonregister.nonregid IN (select nonregid from nonregister where nonregid IN (select nonregid from [appliedjobstatuswithresumes]
WHERE [jobid] = '100'
AND [stageorderid] IN (SELECT [stageorderid] FROM [jobstages] WHERE [stageid] = '1' AND [jobid] = '100'))) GROUP BY nonregister.email,nonregister.email,appliedjobstatuswithresumes.description,appliedjobstatuswithresumes.applieddate

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-08-27 : 08:29:15
It's pretty hard to see what's going on here for several reasons:

1. You didn't post CREATE TABLE statements for the tables involved
2. You didn't post INSERT INTO statements to populate the tables with test data
3. You didn't post the results you are getting
4. You didn't post the results you want.
5. The query you posted is very difficult to read as is. I pasted a reformatted query below

Reformatted query:


SELECT nonregister.email AS firstname
,nonregister.email
,appliedjobstatuswithresumes.description
,appliedjobstatuswithresumes.applieddate
FROM nonregister
,appliedjobstatuswithresumes
WHERE appliedjobstatuswithresumes.nonregid IN (
SELECT nonregid
FROM nonregister
WHERE nonregid IN (
SELECT nonregid
FROM [appliedjobstatuswithresumes]
WHERE [jobid] = '100'
AND [stageorderid] IN (
SELECT [stageorderid]
FROM [jobstages]
WHERE [stageid] = '1'
AND [jobid] = '100'
)
)
)
AND nonregister.nonregid IN (
SELECT nonregid
FROM nonregister
WHERE nonregid IN (
SELECT nonregid
FROM [appliedjobstatuswithresumes]
WHERE [jobid] = '100'
AND [stageorderid] IN (
SELECT [stageorderid]
FROM [jobstages]
WHERE [stageid] = '1'
AND [jobid] = '100'
)
)
)
GROUP BY nonregister.email
,nonregister.email
,appliedjobstatuswithresumes.description
,appliedjobstatuswithresumes.applieddate


Here is an attempt at simplification. I've had to make some assumptions and cannot test this since I do not have the table defs or data:


SELECT nonregister.email AS firstname
,nonregister.email
,appliedjobstatuswithresumes.description
,appliedjobstatuswithresumes.applieddate
FROM nonregister nr
join appliedjobstatuswithresumes ajr
on nr.nonregid = ajr.nonregid
join jobstages js
on js.stageorderid = ajr.stageorderid
where js.stageid = '1'
and js.jobid = '100'

GROUP BY nr.email
,ajr.description
,ajr.applieddate


Perhaps use this as a starting point and let's see where that get's us. But it you're still stuck, please post the missing items I listed above.
Go to Top of Page
   

- Advertisement -