Attach two rows from one table

Question

I currently have this query setting:

SELECT topic.content_id, topic.title, image.location FROM mps_contents AS topic INNER JOIN mps_contents as image ON topic.content_id = image.page_id WHERE topic.page_id = (SELECT page_id FROM mps_pages WHERE page_short_name = 'foo' ) AND image.display_order = '1' 

This is because I want to combine two rows from one table into one row. This is a simplified table setup.

 ----------------------------------------------------------- | page_id | content_id | title | location | display_order | ----------------------------------------------------------- | 1 | 200 | Foo | NULL | 200 | | 200 | 201 | Bar | jpg.jpg | 1 | ----------------------------------------------------------- 

And basically I want this result

 --------------------------------- | content_id | title | location | --------------------------------- | 200 | Foo | jpg.jpg | --------------------------------- 

So, Foo is the 200th question on page 1 ( Foo considered as a subpage, and all its contents are stored in one table), and Bar is its recognized image (shown only because it is the first image)

The above query effectively concatenates the two lines (it returns my desired result), but also returns an additional line corresponding to the original line for Foo , i.e. the location is NULL.

I hope someone can help me prevent the request from returning this extra string.

Answer 1

WHERE ... AND location IS NOT NULL