Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MySQL Query vs. JOIN

Posted on 2011-02-11
7
Medium Priority
?
255 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
7 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

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

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

There are times when we need to generate a report on the inbox rules, where users have set up forwarding externally in their mailbox. In this article, I will be sharing a script I wrote to generate the report in CSV format.
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
The basic steps you have just learned will be implemented in this video. The basic steps are shown to configure an Exchange DAG in a live working Exchange Server Environment and manage the same (Exchange Server 2010 Software is used in a Windows Ser…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…

670 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