Results 1 to 11 of 11
  1. #1
    Join Date
    Feb 2012
    Posts
    6

    Unanswered: Help with SQL Query, multiple options

    Hi,

    I'm just wondering if someone could provide me with some advice on a query problem. This is for an assignment and I'm running into various issues due to flawed logic, I think. Take for example the following query in the database I'm working on:

    SELECT Writer.PID,Staff.SID,Staff.DID
    FROM Writer JOIN Staff
    ON Staff.SID = Writer.SID
    WHERE PID IN (
    SELECT PID
    FROM Writer
    GROUP BY PID
    HAVING COUNT(*) > 3
    )


    It will return something like so:

    PID SID DID
    49 203 122
    49 202 122
    49 201 122
    49 200 122
    49 199 122
    49 204 122
    49 205 122
    49 206 122
    190 460 114
    190 459 146
    190 458 105
    190 457 119
    190 456 126
    190 461 100
    234 169 113
    234 170 114
    234 171 114
    234 172 114
    234 173 115
    ......


    The requirement at hand is I'm meant to return Papers (PID) written by more than 3 staff (SID) that come from different departments. E.g. PID 49 will not be included in the final result as there is more than one SID from the same DID. However, PID 190 will as there is more than 3 SID and they're all from different DID.

    The first thing I did was load up OUTER queries using IN however I get to a point where I think it's all right but this method returns PIDs of SIDs where they were a part of writing a paper by themselves. Various combinations lead me into similar problems.

    Any thoughts/help on constructing the correct SQL query for this?

    Thanks in advance.
    Last edited by burde; 02-11-12 at 11:43.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Any thoughts/help on constructing the correct SQL query for this?

    It is easier to write SQL when you have actual tables & data to test against.

    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The requirement at hand is I'm meant to return Papers (PID) written by more than 3 staff (SID) that come from different departments. E.g. PID 49 will not be included in the final result as there is more than one SID from the same DID. However, PID 190 will as there is more than 3 SID and they're all from different DID.
    If any staff didn't belong to more than one department(it is likely by looking into your small samle),
    it might be better to ask department(DID) than to ask staff (SID).

  4. #4
    Join Date
    Feb 2012
    Posts
    6
    Quote Originally Posted by anacedent View Post
    >
    It is easier to write SQL when you have actual tables & data to test against.

    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    I have attached a text file with what you request. I think this is what you asked for.
    Attached Files Attached Files

  5. #5
    Join Date
    Feb 2012
    Posts
    6
    Quote Originally Posted by tonkuma View Post
    If any staff didn't belong to more than one department(it is likely by looking into your small samle),
    it might be better to ask department(DID) than to ask staff (SID).
    That's definitely the case and exactly what I thought. So I tried to group up the DID and count then do an outer query on that but get the result I described.

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SQL> create view one2one as select w.pid pid, w.sid sid , s.did did
      2  from staff s, writer w
      3  where w.sid = s.sid;
    
    View created.
    
    SQL> select pid, count(did) from one2one
      2  having count(did) > 3
      3  group by pid;
    
    PID		     COUNT(DID)
    -------------------- ----------
    1021			      4
    178			      5
    185			      4
    260			      4
    274			      4
    329			      4
    357			      4
    386			      4
    441			      4
    795			      4
    836			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    4083			      4
    4351			      5
    2577			      4
    2927			      4
    2973			      4
    3695			      4
    4766			      4
    4920			      5
    4636			      4
    1294			      9
    1366			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    944			      4
    1035			      4
    201			      4
    206			      5
    438			      4
    445			      4
    3997			      4
    4232			      4
    1930			      4
    4714			     10
    1022			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    182			      4
    11			      5
    446			      6
    489			      4
    511			      5
    574			      4
    612			      5
    621			      4
    676			      4
    837			      4
    1527			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    1528			      5
    2312			      5
    2407			     10
    3050			      6
    3234			      4
    3677			      4
    2021			      4
    2025			      4
    1315			      6
    886			      4
    937			      5
    
    PID		     COUNT(DID)
    -------------------- ----------
    49			      8
    514			      5
    589			      4
    603			      5
    809			      4
    825			      4
    855			      4
    3999			      4
    1561			      4
    1563			      4
    1718			      5
    
    PID		     COUNT(DID)
    -------------------- ----------
    2226			      4
    2737			      4
    3230			      4
    3336			      4
    3399			      4
    3485			      4
    4828			      4
    4900			      4
    4396			      5
    4576			      4
    1282			      5
    
    PID		     COUNT(DID)
    -------------------- ----------
    1371			      4
    983			      4
    234			      7
    241			      4
    261			      5
    56			      4
    362			      4
    442			      6
    444			      4
    449			      9
    488			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    525			      4
    582			      7
    614			      5
    660			      4
    749			      4
    3897			      4
    4089			      4
    1697			      4
    4393			      5
    4418			      5
    1206			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    1347			      4
    213			      4
    232			      4
    276			      4
    5			      5
    7			      4
    344			      4
    3937			      4
    4049			      4
    4333			      4
    1645			      7
    
    PID		     COUNT(DID)
    -------------------- ----------
    2237			      5
    2384			      4
    2632			      4
    3091			      4
    3848			      4
    4855			      4
    2027			      4
    894			      4
    935			      4
    164			      4
    170			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    228			      4
    275			      5
    360			      4
    410			      6
    467			      4
    559			      4
    594			      4
    1564			      4
    1580			      4
    1623			      4
    1904			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    2920			      4
    4634			      4
    4647			      4
    1189			      4
    1203			      4
    1247			      4
    1400			      4
    939			      4
    190			      6
    262			      5
    273			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    280			      5
    358			      4
    4126			      4
    4207			      4
    1503			      7
    1476			      5
    3749			      4
    3753			      4
    
    151 rows selected.
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2012
    Posts
    6
    Quote Originally Posted by anacedent View Post
    Code:
    SQL> create view one2one as select w.pid pid, w.sid sid , s.did did
      2  from staff s, writer w
      3  where w.sid = s.sid;
    
    View created.
    
    SQL> select pid, count(did) from one2one
      2  having count(did) > 3
      3  group by pid;
    
    PID		     COUNT(DID)
    -------------------- ----------
    1021			      4
    178			      5
    185			      4
    260			      4
    274			      4
    329			      4
    357			      4
    386			      4
    441			      4
    795			      4
    836			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    4083			      4
    4351			      5
    2577			      4
    2927			      4
    2973			      4
    3695			      4
    4766			      4
    4920			      5
    4636			      4
    1294			      9
    1366			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    944			      4
    1035			      4
    201			      4
    206			      5
    438			      4
    445			      4
    3997			      4
    4232			      4
    1930			      4
    4714			     10
    1022			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    182			      4
    11			      5
    446			      6
    489			      4
    511			      5
    574			      4
    612			      5
    621			      4
    676			      4
    837			      4
    1527			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    1528			      5
    2312			      5
    2407			     10
    3050			      6
    3234			      4
    3677			      4
    2021			      4
    2025			      4
    1315			      6
    886			      4
    937			      5
    
    PID		     COUNT(DID)
    -------------------- ----------
    49			      8
    514			      5
    589			      4
    603			      5
    809			      4
    825			      4
    855			      4
    3999			      4
    1561			      4
    1563			      4
    1718			      5
    
    PID		     COUNT(DID)
    -------------------- ----------
    2226			      4
    2737			      4
    3230			      4
    3336			      4
    3399			      4
    3485			      4
    4828			      4
    4900			      4
    4396			      5
    4576			      4
    1282			      5
    
    PID		     COUNT(DID)
    -------------------- ----------
    1371			      4
    983			      4
    234			      7
    241			      4
    261			      5
    56			      4
    362			      4
    442			      6
    444			      4
    449			      9
    488			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    525			      4
    582			      7
    614			      5
    660			      4
    749			      4
    3897			      4
    4089			      4
    1697			      4
    4393			      5
    4418			      5
    1206			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    1347			      4
    213			      4
    232			      4
    276			      4
    5			      5
    7			      4
    344			      4
    3937			      4
    4049			      4
    4333			      4
    1645			      7
    
    PID		     COUNT(DID)
    -------------------- ----------
    2237			      5
    2384			      4
    2632			      4
    3091			      4
    3848			      4
    4855			      4
    2027			      4
    894			      4
    935			      4
    164			      4
    170			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    228			      4
    275			      5
    360			      4
    410			      6
    467			      4
    559			      4
    594			      4
    1564			      4
    1580			      4
    1623			      4
    1904			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    2920			      4
    4634			      4
    4647			      4
    1189			      4
    1203			      4
    1247			      4
    1400			      4
    939			      4
    190			      6
    262			      5
    273			      4
    
    PID		     COUNT(DID)
    -------------------- ----------
    280			      5
    358			      4
    4126			      4
    4207			      4
    1503			      7
    1476			      5
    3749			      4
    3753			      4
    
    151 rows selected.
    
    SQL>
    This is similar to what I get but in a different fashion. This still isn't producing what I need as PID 49 is there but PID 49 has people from the same department that wrote. I.e. my original table of results:

    PID SID DID
    49 203 122
    49 202 122
    49 201 122
    49 200 122
    49 199 122
    49 204 122
    49 205 122
    49 206 122

  8. #8
    Join Date
    Feb 2012
    Posts
    6
    Actually, I think I may have worked it out. The count with distinct gave me an idea:

    SELECT PID
    FROM Writer JOIN Staff
    ON Writer.SID = Staff.SID
    HAVING COUNT (DISTINCT DID) > 3
    GROUP BY PID

    Thoughts?

    -------------

    Scrap that, wont work. So frustrating.
    Last edited by burde; 02-11-12 at 14:38. Reason: Mistake...

  9. #9
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
      1  select lpad(to_char(pid),4) PID, count(distinct(did))
      2  from one2one
      3  having count(distinct(did)) >3
      4  group by pid
      5* order by 1
    SQL> /
    
    PID		 COUNT(DISTINCT(DID))
    ---------------- --------------------
     164				    4
     178				    4
     190				    6
     234				    4
     261				    5
     274				    4
     275				    4
     276				    4
     329				    4
     344				    4
     358				    4
    
    PID		 COUNT(DISTINCT(DID))
    ---------------- --------------------
     362				    4
     386				    4
     449				    5
     511				    5
     514				    4
     525				    4
     559				    4
     582				    7
     589				    4
     809				    4
     837				    4
    
    PID		 COUNT(DISTINCT(DID))
    ---------------- --------------------
     983				    4
    1294				    8
    1315				    6
    1580				    4
    1718				    4
    1904				    4
    2226				    4
    2312				    4
    2384				    4
    2407				    4
    2632				    4
    
    PID		 COUNT(DISTINCT(DID))
    ---------------- --------------------
    2737				    4
    3050				    6
    3234				    4
    3485				    4
    3695				    4
    3749				    4
    3753				    4
    4089				    4
    4126				    4
    4207				    4
    4232				    4
    
    PID		 COUNT(DISTINCT(DID))
    ---------------- --------------------
    4351				    5
    4393				    5
    4396				    4
    4418				    5
    4647				    4
    4714				    9
    4828				    4
    4920				    5
    
    52 rows selected.
    
    SQL>
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  10. #10
    Join Date
    Feb 2012
    Posts
    6
    After plugging away I think I've finally figured it out. It was the multi-column grouping condition I was missing out in the inner query:

    SELECT PID
    FROM Writer
    WHERE PID NOT IN(
    SELECT PID
    FROM Writer JOIN Staff
    ON Writer.SID = Staff.SID
    GROUP BY PID,DID
    HAVING COUNT(*) > 1
    )
    GROUP BY PID
    HAVING COUNT(*) > 3

  11. #11
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    I think my SQL is correct & yours is not
    Code:
    select * from writer
    where pid in ( select pid
        from one2one
        having count(distinct(did)) >3
        group by pid
    minus
    select pid
    FROM   writer
    WHERE  pid NOT IN(SELECT pid
                      FROM   writer
                             join staff
                               ON writer.sid = staff.sid
                      GROUP  BY pid,
                                did
                      HAVING COUNT(*) > 1)
    GROUP  BY pid
    HAVING COUNT(*) > 3
    )
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •