Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Subquery not working 1

Status
Not open for further replies.
duncdude

duncdude

Programmer
Jul 28, 2003
1,979
GB
Hi

Can anyone please help me with this... i can't seem to get it to work!?

Code: 
SELECT parexel_staff.name, parexel_skills.skill, parexel_activity.date
               FROM   parexel_staff, parexel_skills, parexel_activity
               WHERE  parexel_staff.name = parexel_activity.name
               AND    parexel_activity.skill = parexel_skills.skill
               AND    parexel_skills.skill = 'CGI'
               AND    CURRENT_DATE [red]>[/red] (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
               AND    parexel_staff.name [blue]NOT IN[/blue]
               (
               SELECT parexel_staff.name
               FROM   parexel_staff, parexel_skills, parexel_activity
               WHERE  parexel_staff.name = parexel_activity.name
               AND    parexel_activity.skill = parexel_skills.skill
               AND    parexel_skills.skill = 'CGI'
               AND    CURRENT_DATE [red]<[/red] (parexel_activity.date + INTERVAL parexel_skills.retake MONTH)
               );


Kind Regards
Duncan
 
Sort by date Sort by votes
Oh dear, I think i've just read that subselects are not supported by MySQL yet. Is there an alternative way of achieving this?


Kind Regards
Duncan
 
Upvote 0 Downvote
Are you using MySQL 4.1?

Sub-selects are only supported in 4.1 (currently alpha).

-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
How about something like:
[tt]
SELECT st1.name, sk1.skill, a1.date
FROM
parexel_staff st1
JOIN parexel_activity a1 ON st1.name=a1.name
JOIN
parexel_skills sk1
ON
a1.skill=sk1.skill
AND sk1.skill = 'CGI'
AND
a1.date <
CURDATE() - INTERVAL sk1.retake MONTH
LEFT JOIN
(
parexel_staff st2
JOIN parexel_activity a2 ON st2.name=a2.name
JOIN
parexel_skills sk2
ON
a2.skill=sk2.skill
AND sk2.skill = 'CGI'
AND
a2.date >
CURDATE() - INTERVAL sk2.retake MONTH
)
ON st1.name=st2.name
WHERE st2.name IS NULL
[/tt]

I can't wait for version 4.1 to get stable!


-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
Hi Tony

Thank you very much for your reply

Unfortunately I have tried it and i get:-

MySQL said:

You have an error in your SQL syntax near 'ON st1.name = a1.name
JOIN parexel_skills sk1 ON a1.skill = sk1.skill AND sk1.sk' at line 3


Does this make any sense?


Kind Regards
Duncan
 
Upvote 0 Downvote
I'm using version 3.23.56

this is my dilemma...

I have this data:=

Duncan Carr|CGI|1998-01-01
Duncan Carr|CGI|2000-02-03
Gill Bessey|BLS|2001-03-22
Helen Carr|ALS|2003-10-10
Helen Carr|BLS|2004-05-25
Nicole Dunn|ALS|1997-02-03

I am looking to find the records where there is a gap of more than say 3 months between the current date and the date in the last column. BUT i would like it to NOT include the name of the person at all if there is a date within that duration. Helen Carr would be displayed in the above because there is a n older record also. Can this be done?


Kind Regards
Duncan
 
Upvote 0 Downvote
I can't understand how you'd get the syntax error that was reported. Did you copy the code correctly?

Regarding the explanation of your requirements, you only have one table there - is this the same problem, or something different? Also, your example of Helen Carr seems to contradict your stated requirements.

Can you make things clearer?

-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
I have 4 tables:-

parexel_staff
Fiona Allen|PM
Elizabeth Avery|QC
Gill Bessey|BD
Stephen Blowers|PM
Elena Blyth|PM

parexel_jobs
C|clinical
PM|project management
QC|quality control
BD|business development

parexel_skills
BLS|basic life support|3
GCP|good clinical practice6
ALS|advanced life support|36
CGI|common gateway interface|60

parexel_activity
Duncan Carr|CGI|1998-01-01
Duncan Carr|CGI|2000-02-03
Gill Bessey|BLS|2001-03-22
Helen Carr|ALS|2003-10-10
Helen Carr|BLS|2004-05-25
Nicole Dunn|ALS|1997-02-03

I would like to be able to find anyone who has not attended a class within the retake time period

Helen Carr would be a problem because she has completed a course within the retake time period but there is also an older record which the search would find and display. Is there a way to omit the person completely if just one record shows they have completed the skill?


Kind Regards
Duncan
 
Upvote 0 Downvote
How about the following:
[tt]
SELECT a.name,a.skill,sk.retake,max(a.date) d
FROM
parexel_activity a
JOIN parexel_skills sk USING (skill)
GROUP BY a.name,a.skill
HAVING d<CURDATE()-INTERVAL sk.retake MONTH
[/tt]


-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
Tony

Thank you very much for your help

I'm still having no joy though

Can you e-mail me your address to tektipsdude at hotmail.com
(sorry about being cryptic - i get told off for posting my address)

If I give you my login/password info maybe you could have a quick look for me?


Kind Regards
Duncan
 
Upvote 0 Downvote
Duncan -

I've sent you my details, but I charge for off-list work, as detailed.

Here's an adjustment to the SQL:[tt]

SELECT
a.name,
max(a.date>curdate()-INTERVAL sk.retake MONTH) m
FROM
parexel_activity a
JOIN parexel_skills sk USING (skill)
GROUP BY a.name HAVING m=0
[/tt]
which gives the following result:[tt]
+-------------+--------+
| name | m |
+-------------+--------+
| Gill Bessey | 0 |
| Nicole Dunn | 0 |
+-------------+--------+
[/tt]

-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
Tony

I LOVE your solution - and would love to see it working here

BUT, for some reason, it still gives me an error!?

MySQL said:

You have an error in your SQL syntax near 'USING ( skill )
GROUP BY a.name
HAVING m = 0 LIMIT 0, 30' at line 5

I am using phpMyAdmin - MyServerWorld - any ideas?


Kind Regards
Duncan
 
Upvote 0 Downvote
Tony

I really do appreciate your help. I promise you i'm not after a free-bee. I have answered 780 questions on the Perl site in less than a year. I do have a kind heart. I am simply trying to integrate MySQL with Perl and am looking for a bit of help. I thought I was doing O.K. then I found that MySQL - prior to 4.1 - does not support subselects. Nightmare! I'm completely baffled that the SQL 'box' within the phpMyAdmin web-page is kicking up a fuss with the SQL statements you have written. From my old Delphi SQL days the subselects were acceptable. This is driving me nuts!!!


Kind Regards
Duncan
 
Upvote 0 Downvote
I can't understand the syntax error message (it works fine at my end, using MySQL 4.0.18), but you could try:
[tt]
SELECT
a.name,
max(a.date>curdate()-INTERVAL sk.retake MONTH) m
FROM
parexel_activity a
JOIN parexel_skills sk ON a.skill=sk.skill
GROUP BY a.name HAVING m=0
[/tt]

If that doesn't work, try copying and pasting the code from this page.

-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
another error:-

MySQL said:

You have an error in your SQL syntax near 'ON a.skill = sk.skill
GROUP BY a.name
HAVING m = 0 LIMIT 0, 30' at line 4

... i've copied and pasted all the code from your posts?

Thanks for your patience!


Kind Regards
Duncan
 
Upvote 0 Downvote
How about:
[tt]
SELECT
a.name,
max(a.date>curdate()-INTERVAL sk.retake MONTH) m
FROM
parexel_activity a,parexel_skills sk
WHERE a.skill=sk.skill
GROUP BY a.name HAVING m=0
[/tt]

Anybody else out there have any idea why the previous code was rejected?

-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
Tony

That worked! Why? I don't get it at all - BUT i'm bloody happy!

Just copied & pasted as all with all your previous code. Any ideas why the previous should fail and this code be O.K.?

A well-earned star for you!

Thanks dude!


Kind Regards
Duncan
 
Upvote 0 Downvote
Hi Tony

Would you be kind enough to explain how the GROUP BY a.name HAVING m=1 works?

The records returned are
Gill Bessey
Nicole Dunn

I notice if I change the 1 to a 0 I get
Duncan Carr
Helen Carr

Is this a boolean thing? i.e. do all the records that satisfy the max(a.date>curdate()-INTERVAL sk.retake MONTH) m get tagged as 1 and then you are requesting the ones that did NOT satisfy the match - flagged as 0


Kind Regards
Duncan
 
Upvote 0 Downvote
The manual is not very clear on the matter, but it looks like the t1 JOIN t2 ON ... syntax might not have been supported before version 4.0.11. I'll have to take a note of that.


-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
The syntax[tt]
SELECT
a.name,a.date>curdate()-INTERVAL sk.retake MONTH) m
FROM
parexel_activity a,parexel_skills sk
WHERE a.skill=sk.skill[/tt]
would return each record with a field m containing either 1 or 0: 1 if the date is within the period, 0 otherwise.
Then,[tt]
SELECT
a.name,
max(a.date>curdate()-INTERVAL sk.retake MONTH) m
FROM
parexel_activity a,parexel_skills sk
WHERE a.skill=sk.skill[/tt][tt]
GROUP BY a.name[/tt]
returns the maximum value of m for each name: 1 if they had any date within the period, 0 otherwise.
Then,[tt]
SELECT
a.name,
max(a.date>curdate()-INTERVAL sk.retake MONTH) m
FROM
parexel_activity a,parexel_skills sk
WHERE a.skill=sk.skill
GROUP BY a.name[/tt][tt] HAVING m=0[/tt]
eliminates the records where m is not 0.

Hope that makes things clear.



-----
ALTER world DROP injustice, ADD peace;
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

waubain
  • Locked
  • Question
mysqli_multi_query not not returning any results
Replies
2
Views
558
waubain
waubain
madcelt
  • Locked
  • Question
Select query not showing error but not showing result either
Replies
2
Views
291
madcelt
madcelt
C_C_K
  • Locked
  • Question
return groupod by dates from subquery 1
Replies
8
Views
355
feherke
feherke
Katerine
  • Locked
  • Question
Fulltext index not returning new records
Replies
6
Views
319
Katerine
Katerine
Bigsin
  • Locked
  • Question
Query (if not exist) in 3 tables 1
Replies
2
Views
191
Bigsin
Bigsin

Part and Inventory Search

Sponsor

Back
Top