Solved

MySQL JOIN Query Error

Posted on 2014-03-21
4
395 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
[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
  • 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

Get HTML5 Certified

Want to be a web developer? You'll need to know HTML. Prepare for HTML5 certification by enrolling in July's Course of the Month! It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

This article discusses how to create an extensible mechanism for linked drop downs.
There are times when I have encountered the need to decompress a response from a PHP request. This is how it's done, but you must have control of the request and you can set the Accept-Encoding header.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

622 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