CODEWITHSUNDEEP
mysqlsql
zac
zac

Reputation: 4918

Get main table records only from join with two other tables

I have 3 tables "job_messages" contain 3 records and these all I want along with names from other 2 tables that are linked using foreign keys

here are the 3 tables:

enter image description here

Here are the SQL

SELECT 
  `job_messages`.`job_message_id`,
  `job_messages`.`job_time`,
  `job_messages`.`job_type`,
  `job_messages`.`work_group_id`,
  `job_messages`.`location_id`,
  `job_messages`.`patient_name`,
  `job_messages`.`room_no`,
  `job_messages`.`test_taken`,
  `job_messages`.`worker_id`,
  `job_messages`.`notes`,
  `work_group`.`work_group`,
  `locations`.`location_name`
FROM
  `job_messages`
  INNER JOIN `work_group` ON (`job_messages`.`work_group_id` = `work_group`.`work_group_id`)
  INNER JOIN `locations` ON (`work_group`.`work_group_id` = `locations`.`work_group_id`)

The problem I get 12 rows from this select along with repeated records and all I want are the main 3 records from "job_messages", what the correct SQL should be ?

Upvotes: 0

Views: 24

Answers (2)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48197

You say your table job_messages contain 3 rows, but you dont say how many rows are in the other two tables.

Your query looks ok, so i will guess the problem is the data.

try this querys.

SELECT `work_group_id`, count(*)
FROM `work_group`
GROUP BY `work_group_id`
HAVING count(*) > 1

SELECT `work_group_id`, count(*)
FROM `locations`
GROUP BY `work_group_id`
HAVING count(*) > 1

if any of those query return result you have a problem and that cause the duplicated rows.

EDIT

If you have multiple locations try using this

FROM       `job_messages`
INNER JOIN `work_group` 
        ON (`job_messages`.`work_group_id` = `work_group`.`work_group_id`)
INNER JOIN `locations` 
        ON (`job_messages`.`location_id` = `locations`.`location_id`)

OR

FROM       `job_messages`
INNER JOIN `work_group` 
        ON (`job_messages`.`work_group_id` = `work_group`.`work_group_id`)
INNER JOIN `locations` 
        ON (`work_group`.`work_group_id` = `locations`.`work_group_id`)
       AND (`job_messages`.`location_id` = `locations`.`location_id`)

Upvotes: 1

genespos
genespos

Reputation: 3311

Have you tryed changing Joining locations?

 INNER JOIN `locations` USING (`location_id`)

Or, if you prefer to use ON:

INNER JOIN `locations` ON (`job_messages`.`location_id` = `locations`.`location_id``)

Upvotes: 2

Related Questions