Solved

MySQL JOIN Query Error

Posted on 2014-03-21
4
369 Views
Last Modified: 2014-03-22
I'm joining 2 tables, but I continue to get double duplicate records for a specific id (1)... I've tried using group and Distinct, but nothing is working...  When I use Group BY programs.program_id or schedule.program_id, Only 1 record is selected when two should be displayed because of different start_date and dow

Here are tables:

DB Table: Programs            
id:                            1                                        2
program_id:           WPROG-24-001                   WPROG-24-011
program_name:      Web App Dev I            Web App Dev II
            
            
Db Table: Schedule            
schedule_id:  1                           3
program_id:      1                           1
start_date:      5-May-14        7-May-14
dow:              Mondays            Thursdays
price               $35                   $35

Here's the query I'm using:

$sql = "select *
            From programs JOIN schedule ON programs.id = schedule.program_id
            Where programs.id = '$id'
            ";

I tried this one also:

$sql = "select programs.id, programs.program_id, programs.program_name, programs.program_detail, schedule.schedule_id, schedule.start_date, schedule.location, schedule.dow,  schedule.price, schedule.comments
            From programs INNER JOIN schedule ON programs.id = schedule.program_id
            Where programs.id = '$id'
            ";
0
Comment
Question by:melvint91
  • 2
4 Comments
 

Author Comment

by:melvint91
ID: 39946801
I forgot to mention, I have numerous records in the schedule table and about 4 entries with program_id: 1. . .I only want the 4 records returned, but I'm getting about 8 returned which are duplicates
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39946803
What result are you expecting?  I created this sqlfiddle using the sample data above. When I run your first query, it shows exactly the result I'd expect, two rows:

ID 	PROGRAM_ID 	PROGRAM_NAME 	SCHEDULE_ID 	START_DATE 	DOW 	PRICE
1 	WPROG-24-001 	Web App Dev I 	1 	May, 05 2014 00:00:00+0000 	Mondays 	35
1 	WPROG-24-001 	Web App Dev I 	3      May, 07 2014 00:00:00+0000 	Thursdays 	35

Open in new window


What result were you expecting instead - and why?
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39947320
Hi!

Execute this query on your table

select programs.id, count(*) as number_of_programs_with_same_id
From programs 
group by  programs.id 
having count(*) > 1

Open in new window


This query tells you that you have duplicate program-id's when you are expecting it to be unique and lists all ids that have more than one programs behind each id.
Make sure that the columns id, program_id  and program_name form a primary key and the id column has also a  unique index to prevent duplicates if that is what you want.

Regards,
     Tomas Helgi
0
 

Author Closing Comment

by:melvint91
ID: 39947402
After verifying _agx_ query on sqlfiddle and inserting additional rows and running my initial query above directly from Mysql. . . the results returned were accurate. . .It has to be my PHP code causing duplicate returns somehow..
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

896 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

18 Experts available now in Live!

Get 1:1 Help Now