10 Replies - 488 Views - Last Post: 13 January 2014 - 10:00 AM Rate Topic: -----

#1 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

SQL select query help!

Posted 10 January 2014 - 09:48 AM

I'm stuck with this problem at work, I know there must be a solution but I can't get my head round it

I need to select records from one table based on another (sounds a lot more simple than it is). I've got data like this:

Table 1 (Header)
HeadGUID Status Name
1        1      Foo
2        1      Bar
3        1      Xyz
4        0      Zzz



Table 2 (Item)
HeadGUID ItemGUID Status
1        10       1
2        20       1
2        30       0
3        40       0
4        50       0

I need my query to return the fields Header.HeadGUID & Header.Name, and it should only return 3 (Xyz). It's where all Items have a status of 0, for a given HeadGUID. I don't want any headers where Header.Status = 0, and I don't want any headers where there are ANY Items.Status = 1. I only want the records where Header.Status = 1 and all Items.Status = 0.

I can't figure out this "all items.status = 0" part. The closest I could come was returning records with "any items.status = 0", and this generates tons of data I don't need. I feel it should be possible with the joins, but I rarely use anything other than an inner join, so I'm unsure of the others. I did wonder if I should be solved with a union, but couldn't figure that out either

A little help please!

Edit - This is my best code:
select t1.HeaderGUID, t1.Name 
        from Header t1
        inner join Item t2 on t1.HeaderGUID = t2.HeaderGUID
        where t1.Status = 1 and t2.Status = 0

This is wrong though, as it also returns "Bar" :(

This post has been edited by chemicalfan: 10 January 2014 - 09:50 AM


Is This A Good Question/Topic? 0
  • +

Replies To: SQL select query help!

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9571
  • View blog
  • Posts: 36,242
  • Joined: 12-June 08

Re: SQL select query help!

Posted 10 January 2014 - 09:59 AM

Any particular reason you are using an inner join?

Of course it would return bar - there's still a condition (since there are two lines) where it meets your conditions.

What you may want to do is add to your WHERE clause something akin to: AND HeaderGUID is not in a list of all selected HeaderGUID - from the Item table - that have a Status = 0.

That should pick out all your Status = 0 - even if they have other statuses!
Was This Post Helpful? 1
  • +
  • -

#3 andrewsw  Icon User is online

  • It's just been revoked!
  • member icon

Reputation: 3808
  • View blog
  • Posts: 13,508
  • Joined: 12-December 12

Re: SQL select query help!

Posted 10 January 2014 - 10:28 AM

And you fields are named HeadGUID not HeaderGUID.
Was This Post Helpful? 0
  • +
  • -

#4 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5930
  • View blog
  • Posts: 12,853
  • Joined: 16-October 07

Re: SQL select query help!

Posted 10 January 2014 - 08:02 PM

For your Item table:
select HeadGUID
    from Item
    group by HeadGUID
    having sum(Status)=0



Simply, add up all the status values for a given HeadGUID and any with a total status of 0 meets the first criteria. That would get you 3 and 4.

If I understand correctly, you also what to check Header status, like so:
select t1.HeadGUID, t1.Name 
    from Header t1
        inner join (
            select HeadGUID
                from Item
                group by HeadGUID
                having sum(Status)=0
        ) t2 
            on t1.HeadGUID = t2.HeadGUID
    where t1.Status = 1



Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#5 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

Re: SQL select query help!

Posted 13 January 2014 - 07:09 AM

Thanks for the replies all - obviously this was example/pseudo code...the Header/Head mix-up was just a typo!

Unfortunatly, the status fields aren't actually numerical or even bool - they are strings! This rules out the sum route :(

Modi's solution looks good, but doesn't work for some reason (it too, returns "Bar"). I'm going to dig into the data manually and look for reasons why. In the live environment, there are over 20000 records, with 1000s in each combination, so I have to figure out this script somehow!

Thanks for the input so far anyway, I'm going to play around a bit and see what I can find
Was This Post Helpful? 0
  • +
  • -

#6 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5930
  • View blog
  • Posts: 12,853
  • Joined: 16-October 07

Re: SQL select query help!

Posted 13 January 2014 - 08:10 AM

View Postchemicalfan, on 13 January 2014 - 10:09 AM, said:

Unfortunatly, the status fields aren't actually numerical or even bool - they are strings! This rules out the sum route


Nah. As absurd as strings seem to be here, it's simple to fix. If your numbers aren't numbers, translate em.

Try this:
select HeadGUID
    from Item
    group by HeadGUID
    having sum(case when Status='1' then 1 else 0 end)=0


Was This Post Helpful? 1
  • +
  • -

#7 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

Re: SQL select query help!

Posted 13 January 2014 - 08:48 AM

Doh, I was sure that would have cracked it, but now it throws a syntax error :(

Quote

Line 4: Incorrect syntax near '.'

Line 4 is the sub-select line....I'm going to play a bit more to see if it occurs as a select on its own
Was This Post Helpful? 0
  • +
  • -

#8 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

Re: SQL select query help!

Posted 13 January 2014 - 09:09 AM

Ok, if I lose the "dbo." at the start of the "join name", and also on the right side of the join, it returns data! But....it still looks like the wrong data (Foo, in this case) :(/>

This post has been edited by chemicalfan: 13 January 2014 - 09:27 AM

Was This Post Helpful? 0
  • +
  • -

#9 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

Re: SQL select query help!

Posted 13 January 2014 - 09:21 AM

Whoa, hold the phone!!

Flipping the 0 & 1 in the CASE WHEN seems to have done the trick! So:

select HeadGUID
from Item
group by HeadGUID
having sum(case when Status='Closed' then 0 else 1 end)=0 


Gonna check through the data, but we could be onto a winner!!
Thanks very much!!
Was This Post Helpful? 0
  • +
  • -

#10 baavgai  Icon User is offline

  • Dreaming Coder
  • member icon

Reputation: 5930
  • View blog
  • Posts: 12,853
  • Joined: 16-October 07

Re: SQL select query help!

Posted 13 January 2014 - 09:37 AM

Happy to help.

Looks like your status text is inconsistent.

Try this to see what you've got:
select Status, count(*)
  from Item
  group by Status


Was This Post Helpful? 1
  • +
  • -

#11 chemicalfan  Icon User is offline

  • D.I.C Head

Reputation: 4
  • View blog
  • Posts: 88
  • Joined: 16-October 09

Re: SQL select query help!

Posted 13 January 2014 - 10:00 AM

Did you mean header status or item status? As, header status is either 'Active' or 'Inactive', and item status is 'Open' or 'Closed'?

Off home now, will pick this up tomorrow. Thanks again for your help!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1