CODEWITHSUNDEEP
phpmysqlinner-join
Ellery
Ellery

Reputation: 179

How to Stop Row Returning Twice MYSQL

I have a database setup like this:

Table: Sections
section_id | section_name
  i23kj    |    News 
  o492q    |   Events

Table: Subsections
sub_section_id | section_id | sub_section_name 
    q123l      |   i23kj    | Washington Post
    z765a      |   i23kj    |    NY Times
    w439m      |   o492q    |   Launch Party

I want a page to print the following information:

News
Washington Post
Ny Times

Events
Launch Party

I'm using the query:

SELECT Sections.section_id, Sections.section_name, Subsections.sub_section_id, Subsections.sub_section_name 
FROM Sections INNER JOIN Subsections 
ON Sections.section_id=Subsections.section_id

However this returns:

News
Washington Post

News
Ny Times

Events
Launch Party

How can I change my query to only display the Section once and the Subsections under that section

Upvotes: 2

Views: 82

Answers (1)

void
void

Reputation: 7890

you need to use subquery,just tested it see the DEMO:

create table sections
(
  section_id varchar(10),
  section_name varchar(50)
);
create table subsections
(
  sub_section_id varchar(10),
  section_id varchar(10),
  sub_section_name varchar(50)
);
insert into sections 
       values('i23kj','News'),
             ('o492q','Events');
insert into subsections
       values('q123l','i23kj','Washington Post'),
             ('z765a','i23kj','NY Times'),
             ('w439m','o492q','Launch Party');

and the result of below query is exactly what you wanted in question:

select case 
       when q2.sub_section_id is null then section_name
       else sub_section_name end
from 

(select * from ( select Sections.section_id, Sections.section_name,
       null as sub_section_id, null as sub_section_name 
FROM   Sections
union
SELECT Sections.section_id, Sections.section_name,
       Subsections.sub_section_id, Subsections.sub_section_name 
FROM   Sections INNER JOIN Subsections 
       ON Sections.section_id=Subsections.section_id) as q 
       order by q.section_id,q.sub_section_id) as q2

Upvotes: 1

Related Questions