Solved

MySQL Query vs. JOIN

Posted on 2011-02-11
7
249 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
Comment Utility
"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
Comment Utility
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
Comment Utility
OK. that makes sense. I thought I was missing something. Thanks for the input.
0
 
LVL 26

Expert Comment

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

Featured Post

Why spend so long doing email signature updates?

Do you spend loads of your time carrying out email signature updates? Not very interesting are they? Don’t let signature updates get you down. Let Exclaimer Cloud - Signatures for Office 365 make managing email signatures a breeze.

Join & Write a Comment

Learn to move / copy / export exchange contacts to iPhone without using any software. Also see the issues in configuration of exchange with iPhone to migrate contacts.
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
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…
The video tutorial explains the basics of the Exchange server Database Availability groups. The components of this video include: 1. Automatic Failover 2. Failover Clustering 3. Active Manager

743 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now