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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 401
  • Last Modified:

MySQL JOIN Query Error

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
melvint91
Asked:
melvint91
  • 2
1 Solution
 
melvint91Author Commented:
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
 
_agx_Commented:
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
 
Tomas Helgi JohannssonCommented:
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
 
melvint91Author Commented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now