SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 DUPLICATE VALUE WHEN JOINING
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sathesh
Starting Member

India
1 Posts

Posted - 08/27/2014 :  04:31:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

1109 Posts

Posted - 08/27/2014 :  08:29:15  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000