new-b query hell :-) - 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

new-b query hell :-)

Author

Topic

d-e-f
Starting Member

8 Posts

Posted - 2011-09-14 : 14:30:21

Hello all DB gurus! I have been pounding my head on the desk all day trying to perfect a select statement to no avail.... any help would be much appreciated!!!

Table1
ID
Status = Closed

Table2
ID
Closure = Paid

I need to return all ID's matching this criteria: Table1 IDs that have a status value of 'Closed' that also do not have a closure value of 'Paid' in Table2

Thanks All

memorykills
Starting Member

18 Posts

Posted - 2011-09-14 : 14:36:09

SELECT table1.id
FROM table1
LEFT OUTER JOIN table2
on table1.id = table2.id
and table2.closure = 'paid'
where
table1.status = 'closed'
and table2.id is null

d-e-f
Starting Member

8 Posts

Posted - 2011-09-14 : 15:03:00

BEAUTIFUL!!! I owe you a drink (or 3) memorykills

d-e-f
Starting Member

8 Posts

Posted - 2011-09-23 : 10:30:54

another requirement....

Table1
ID
Status = Closed

Table2
ID
Closure = Paid

I need to return all ID's matching this criteria: Table1 IDs that have a status value of 'Closed' that also EXIST in Table2 but do not have a closure value of 'Paid' in Table2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 10:33:25

[code]
SELECT reqd columns...
FROM Table1 t1
INNER JOIN Table2 t2
ON t2.ID = t1.ID
AND t1.Status='Closed'
AND t2.Closure <> 'Paid'
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

d-e-f
Starting Member

8 Posts

Posted - 2011-09-23 : 11:23:35

i'm getting almost 10 times the number of records returned with you r suggection visakhm, and select distinct dosent help to pare it back :-(

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 11:25:52

that means the tables are related in one to many form. please show some sample data from both and explain which one record you want to return out of duplicates.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

d-e-f
Starting Member

8 Posts

Posted - 2011-09-23 : 11:56:05

Here are the all the values from one ID:

ID Status Closure TimeStamp
3733125 Closed Discounted 00:15.9
3733125 Closed Collection 01:23.1
3733125 Closed WriteOff 08:59.5

I'm only interested in getting one ID in the data returned - '3733125' in this case

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-23 : 12:12:56

and what should values for Closure and Timestamp that you want to see with the result?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

d-e-f
Starting Member

8 Posts

Posted - 2011-09-23 : 13:11:40

TimeStamp values are not needed

the criteria of distinct Table1.ID records with Table1.Status = 'Closed' which do not have Table2.Closure = 'Paid' values is still what i'm looking to get

there also are however, Table1.ID records that do not have any Table2.ID records at all - those are what i am now needing to exclude from the results

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 07:01:13

"select distinct dosent help to pare it back"

Avoid using DISTINCT as a band-aid if at all possible, its very inefficient for SQL Server.

This may be what you want:


SELECT reqd columns...
FROM Table1 AS t1
WHERE     t1.Status='Closed'
      AND EXISTS
      (
          SELECT *
          FROM Table2 AS t2a
          WHERE     t2a.ID = t1.ID
      )
      AND NOT EXISTS
      (
          SELECT *
          FROM Table2 AS t2b
          WHERE     t2b.ID = t1.ID
                AND t2b.Closure <> 'Paid'
      )

d-e-f
Starting Member

8 Posts

Posted - 2011-09-28 : 11:16:20

the requirement changed again... don't you love it when that happens?

i'm all set for now - thanks everyone for their assistance!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources