| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-11-12, 10:36
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 6
|
|
|
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 10:43.
|
02-11-12, 10:53
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
>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.
|
|
02-11-12, 11:34
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,205
|
|
|
|
Quote:
|
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).
|
|
02-11-12, 12:23
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 6
|
|
Quote:
Originally Posted by anacedent
>
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.
|
|
02-11-12, 12:30
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 6
|
|
Quote:
Originally Posted by tonkuma
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.
|
|
02-11-12, 12:48
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
02-11-12, 13:22
|
|
Registered User
|
|
Join Date: Feb 2012
Posts: 6
|
|
Quote:
Originally Posted by anacedent
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
|
|
02-11-12, 13:35
|
|
Registered User
|
|
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 13:38.
Reason: Mistake...
|
02-11-12, 15:23
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
02-11-12, 16:18
|
|
Registered User
|
|
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
|
|
02-11-12, 16:37
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,416
|
|
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.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Help with SQL Query, multiple options
