[Resolved] Help with a strange join?

[jasonla]

I know about the Modified PreOrder Tree Traversal method, but that's too complicated for my needs.

Any help with my question is much appreciated.

I have a table of sections, such as news, sports, opinion, arts & entertainment, e.g.

section_id, PK
section_name
<other fields>

The data looks like this:

1    |    News
2    |    Opinion
3    |    Sports
4    |    Arts & Entertainment
etc

And I have a table of beats, or subsections, with a foreign key to the subsection's parent PK.

beat_id
beat_name
beat_section_id

And the data looks like this

1    |    Local Government    |    1
2    |    Campus Issues       |    1
3    |    Staff Editorial     |    2
4    |    Columnists          |    2
5    |    Men's Basketball    |    3
etc

I'm trying to create a SQL query that will return results such as this:

section_id   |   section_name   |   beat_name     | beat_id
4            |   Arts and Enter.|(NULL)           |  (NULL)
1            |   News           |(NULL)           |  (NULL)
1            |   News           | Local News      |   1
1            |   News           | Campus Issues   |   2
2            |   Opinion        |(NULL)           |  (NULL)
2            |   Opinion        | Staff Editorial |   3
2            |   Opinion        | Columnists      |   4

Basically, I've tried this:

SELECT s.section_id, s.section_name, b.beat_name, b.beat_id
FROM section s
LEFT JOIN beat b on s.section_id = b.beat_section_id
ORDER BY s.section_order

I'm close to my goal, expect I get this:

section_id   |   section_name   |   beat_name     | beat_id
1            |   News           | Local News      |   1
1            |   News           | Campus Issues   |   2
2            |   Opinion        | Staff Editorial |   3
2            |   Opinion        | Columnists      |   4
4            |   Arts & Enter   | (NULL)          |  (NULL)

A section without a beat association, such as Arts & Enter., show up correctly, e.g. section info with NULL beat info.

But for a section such as News, which has beats associated with it, it doesn't return the a row of JUST News info, without a beat association. e.g.:

1            |   News            | (NULL)          |  (NULL)

Help?

[Lars_Berg]

SELECT s.section_id, s.section_name, b.beat_name, b.beat_id,s.section_order
FROM section s
inner join beat b on s.section_id = b.beat_section_id
union all
select s.section_id,s.section_name,
cast(null as <typeOf beatName>),
cast(null as <typeOf beatId),
s.section_order
ORDER BY s.section_order

[jasonla]

Lars, thank you, thank you, thank you! I really appreciate your quick and informative help.

I modified the query you gave me and it's working exactly the way I need it to.

(SELECT s.section_id, s.section_name, b.beat_name, b.beat_id, b.beat_order, s.section_order
FROM section s
inner join beat b on s.section_id = b.beat_section_id)
union all
(select s.section_id,s.section_name,
cast(null as unsigned ),
cast(null as unsigned ),
cast(null as unsigned ),
s.section_order
FROM section s
ORDER BY s.section_order)

The formatting is not quite as clean as some people might say, but it works.

Again, thank you so much.