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

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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources