0

I am trying to do an inner join on four tables in MySQL. The table names are:

question (id_question, question_text, id_standard)
standard (id_standard, standardtext)
organisation_standard(id_organisation,id_organisation,id_standard)
organisation (id_organisation, organisation_name)

This is my query and it's giving me repetitive values:

select distinct a.question_text, d.organisation_name
from question a
inner join standard b on a.id_standard = b.id_standard
inner join organisation_standard c on b.id_standard= c.id_standard
inner join organisation d on c.id_organisation = d.id_organisation
where a.id_standard = 18;

How can I avoid the repetitive values?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
istiak
  • 1
  • 2
    And your question is...? – Marc B Aug 05 '14 at 22:02
  • Why do you join the table `standard` if you don't select anything from it and the tables `question` and `organisation_standard` also contain the column `id_standard`? – Tom Aug 05 '14 at 22:04
  • Welcome to the Stack Overflow community! Stack Overflow is a _question_ and _answer_ site for professional and enthusiast programmers. The emphasis being: a posted question should be an actual _question_. (But thanks, I guess, for posting the current status of your latest development efforts.) – spencer7593 Aug 05 '14 at 22:10
  • yes organisation_standard also contains the column id_standard. and adding standard table was a mistake in the first place but it is not affecting the query so i left it like this for the time being. – istiak Aug 05 '14 at 22:13
  • What datatype is question_text? If it is of an old BLOB type like text, or ntext the distinct would not work – kristof Aug 12 '14 at 15:42

1 Answers1

-1

What you need is a left join and not an inner join change the inner joins into left joins and you will get just one row:

select distinct
    a.question_text, d.organisation_name
from
    question a
        left join
    standard b ON a.id_standard = b.id_standard
        left join
    organisation_standard c ON b.id_standard = c.id_standard
        left join
    organisation d ON c.id_organisation = d.id_organisation
where
    a.id_standard = 18
group by a.id_standard;

This diagram from another so answer gives the difference between the different joins enter image description here

Community
  • 1
  • 1
Japheth Ongeri - inkalimeva
  • 4,909
  • 6
  • 29
  • 46