We help IT Professionals succeed at work.

SQL Not distinct problem

Andrew Barrington
on
1,007 Views
Last Modified: 2008-01-09
Hi there

I am having some trouble will an SQL statement. What I am trying to do is determine if a group of records where inputted by a different user.

Table layout (Materials)  

Job_Num | Mat_ID | Mat_Price | User_ID | Date_Added
--------------------------------------------------------------------
       1      |       23     |      2.50     |       3       |   2007-01-31
       1      |       24     |      21.50   |       3       |   2007-02-01
       1      |       27     |      2.50     |       4       |   2007-01-31
       2      |       21     |      2.50     |       5       |   2007-01-31
       2      |       09     |      2.50     |       5       |   2007-01-31
       2      |       10     |      2.50     |       5       |   2007-01-31
       3      |       23     |      2.50     |       5       |   2007-01-31
       3      |       23     |      2.50     |       7       |   2007-01-31

What I am trying to do is to write an SQL statement to return all the material records that have different User_ID’s.  

something like

select ID From materials_4_job Where JOB_ID='1' and Count(User_ID) > 1 Group By User_ID

I know this will not work but I hope it gives you some Idea of what I am after

Any help with this would be great


Thanks

Mac
Comment
Watch Question

select mat_ID From materials_4_job
Where JOB_ID='1'
Group By mat_ID
having count(*) > 1

if you want for all the jobs

select mat_id , job_id from materials_4_job
group by mat_id, job_id
having count(*) > 1
Information Technology Specialist
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.