| Author |
Topic |
|
simplyrichard
Starting Member
4 Posts |
 Posted - 2009-08-29 : 22:48:30
|
| I need to select all people with the code number 55 then from that group of people I only want to see the people from the first select code 55 but who also have had a code of 44 and 32.Query 1:Select employee_id FROM employees where codenum = '55'Query 2:Select employee_id FROM QUERY1 where codenum = '44' OR '32'I know where is a way to write this as one query with a sub query but I can seem to get it right. I would like to use IN cause for the codenum.Any help would be great!Rick |
|
|
Nageswar9
Aged Yak Warrior
600 Posts |
Posted - 2009-08-30 : 00:22:55
|
| Hi, Try this onceSelect employee_id FROM employees where codenum = '55'union Select employee_id FROM QUERY1 where codenum = '44' OR codenum = '32' |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-08-30 : 01:59:35
|
do you mean this?Select employee_id FROM employees where codenum in ('55','44','32') group by employee_id having count(distinct codenum)=3 |
|
|
|
simplyrichard
Starting Member
4 Posts |
Posted - 2009-08-30 : 10:43:49
|
| Maybe this might help:name | employee_id | codenum | department | reasonBob | 50 | 300 | service | retarted lab computerBob | 50 | 255 | service | lunch breakMary | 23 | 255 | service | cleaningWhen I run the query I only want to see codenum 300 but I want to see everyone with a codenum 300 and all other records that the person with a 300 codenum have. I dont want to see someone with a codenum 255 unless they had a record with a codenum 300 at somepoint. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-30 : 10:54:35
|
[code]select * from table tjoin(select * from table where codenum = 300)dt on t.employee_id = dt.employee_idorder by employee_id, codenum desc[/code]
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
simplyrichard
Starting Member
4 Posts |
Posted - 2009-08-30 : 13:36:41
|
| You are close to what I am looking for but not yet... I think I have a better way to explain.Query 1: Get all employees with codenum 50 <--- Think of this as a table of people with only codenum of 50.Query2: Get all records WHERE employee_ID IN Query1 WHERE codenum = 255SELECT * FROM employees WHERE codenum = '50' <--- I need to use the results of this query in a where clause to filter the database again to get only the records belonging to those in the result of the first query.Remember they will have a codenum = 50 but they will also have other records with different codenums and that is the list I need.Employees can have any number of records with different codenums. As the supervisor of my department I only want to see employees with codenum of 255 but I want to see all of their other records because they had a codenum 255.I hope this helps... it is super confusing... |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-30 : 14:14:04
|
Sorry but I think, my query does exact what you want.Did you try it?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
simplyrichard
Starting Member
4 Posts |
Posted - 2009-08-30 : 14:24:45
|
| I dont understand your query very well because of the t and dt. I also don't see where I put my 2nd critera for the codenum. I need to say show me all with codenum 50 and then in the 2nd critera I need to say which codenums again I want to see.I cant try it until tomorrow but I will know it when I see it. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2009-09-02 : 05:59:23
|
| t and dt are just aliases used for tables. i think webfred's query does what you asked for |
|
|
|
|
Please help write this query....