Solved

MySQL JOIN Query Error

Posted on 2014-03-21
4
367 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 24

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
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 create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

708 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