Solved

MySQL Query vs. JOIN

Posted on 2011-02-11
7
251 Views
Last Modified: 2012-06-21
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.
0
Comment
Question by:fyr3byt3
  • 3
7 Comments
 
LVL 26

Accepted Solution

by:
jar3817 earned 500 total points
ID: 34872162
"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
 
LVL 26

Expert Comment

by:jar3817
ID: 34872173
Give us some sample data and what results you're trying to get to make sure we're all on the same page.
0
 

Author Comment

by:fyr3byt3
ID: 34872202
OK. that makes sense. I thought I was missing something. Thanks for the input.
0
 
LVL 26

Expert Comment

by:jar3817
ID: 35116193
I think the points should be assigned. What he wanted wasn't possible. I explained this and why...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Find out what you should include to make the best professional email signature for your organization.
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
This video discusses moving either the default database or any database to a new volume.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question