[SOLVED] Select data where there is no match...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

[SOLVED] Select data where there is no match...

Post by JayBird »

i have 2 tables

Table 1

Code: Select all

CREATE TABLE `gha_files_sections` (                                                                                                                                                 
                      `id` int(10) NOT NULL auto_increment,                                                                                                                                             
                      `section_name` varchar(255) default NULL,                                                                                                                                         
                      PRIMARY KEY  (`id`)                                                                                                                                                               
                    ) ENGINE=InnoDB DEFAULT CHARSET=latin1
and...

Code: Select all

CREATE TABLE `gha_files_subsections` (                                                                                                                                                                                                               
                         `id` int(2) NOT NULL auto_increment,                                                                                                                                                                                                               
                         `subsection_name` varchar(255) default NULL,                                                                                                                                                                                                       
                         `cid` int(2) default NULL,                                                                                                                                                                                                                         
                         `order` int(2) default NULL,                                                                                                                                                                                                                       
                         PRIMARY KEY  (`id`)                                                                                                                                                                                                                                
                       ) ENGINE=InnoDB DEFAULT CHARSET=latin1
Currently i have this query

Code: Select all

SELECT s.id, s.section_name, ss.cid, ss.subsection_name
		FROM ghaintranet.gha_files_sections AS s, ghaintranet.gha_files_subsections AS ss
		WHERE (ss.cid = s.id) OR (s.id = 1)
This selects all the main sections and their sub sections. Problem is, some of the main sections DO NOT have sub sections, so these main sections do not get selected in the query.

Is there some way i can do the query above, but modify it to add main sections that dont have a subsection!?

Or, will i have to have 2 querys?
Last edited by JayBird on Tue May 31, 2005 5:54 am, edited 1 time in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Query should look like (untested)

Code: Select all

SELECT s.id, s.section_name, ss.cid, ss.subsection_name
FROM ghaintranet.gha_files_sections AS s
LEFT OUTER JOIN ghaintranet.gha_files_subsections AS ss
ON s.id = ss.cid
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Thanks my man, seems to have worked a treat :D
Post Reply