Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MySQL Query vs. JOIN

Posted on 2011-02-11
7
Medium Priority
?
256 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
4 Comments
 
LVL 26

Accepted Solution

by:
jar3817 earned 2000 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

On September 18, Experts Exchange launched the first installment of the Help Bell, a new feature for Premium Members, Team Accounts, and Qualified Experts. The Help Bell will serve as an additional tool to help teams increase question visibility.
Are you looking for the options available for exporting EDB files to PST? You may be confused as they are different in different Exchange versions. Here, I will discuss some options available.
In this Micro Video tutorial you will learn the basics about Database Availability Groups and How to configure one using a live Exchange Server Environment. The video tutorial explains the basics of the Exchange server Database Availability grou…
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
Suggested Courses

782 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