MySQL Query vs. JOIN

I am trying to get some data from our MySQL DB & I cannot seem to find the right combination of JOINs that gives me the best results.

Here is the layout - there are 3 tables:

Table 1 - PROJECTS
--------------------
PROJECT-ID
PROJECT-DESC
PROJECT-START-DATE
...

Table 2 - USERS
--------------------
USER-ID
NAME
EMAIL
PHONE
...

Table 3 - PROJXREF
-------------------------
ID
USER-ID
PROJECT-ID
ACTIVE
ROLE
...

I need all of the fields from the 1st 2 tables & some of the fields from #3. I have tried several variations of the SELECT:

SELECT P.PROJECT-ID, P.PROJECT-DESC, P.PROJECT-START-DATE, U.NAME, U.EMAIL, X.ROLE
FROM PROJECTS AS P JOIN PROJXREF AS X ON (X.PROJECT-ID = P.PROJECT-ID)
JOIN USERS AS U ON (X.USER-ID = U.USER-ID)
WHERE (P.PROJECT-START-DATE BETWEEN '2011-01-01 00:00:00' AND '2011-01-07 00:00:00') LIMIT 0,19

I am getting good date results & only info from these fields, but since there are multiple users per project, I am getting duplicate lines of the P. fields for each distict U. field.

I know this is probably very easy, I just can't seem to wrap my brain around it.

According to SELECT VERSION(), I am using MySQL v5.1.48.
fyr3byt3Asked:
Who is Participating?
 
jar3817Connect With a Mentor Commented:
"I am getting duplicate lines of the P. fields for each distict U. field."

That's exactly how it's supposed to work if you have multiple related records. You can only get rid of duplicates if EVERY column is the same as another. Since you have more than 1 person mapping to a single project (1:many relationship) it's going to list each user and the project related to that user, hence listing the same project more than once.
0
 
jar3817Commented:
Give us some sample data and what results you're trying to get to make sure we're all on the same page.
0
 
fyr3byt3Author Commented:
OK. that makes sense. I thought I was missing something. Thanks for the input.
0
 
jar3817Commented:
I think the points should be assigned. What he wanted wasn't possible. I explained this and why...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.