BOTTOM JOIN tables?

tomandhannah

I have several xref tables in my database with only two columns: category_id and another column for the specific kind of content. For example, the other column might be lesson_id or article_id, but there is always a category_id column.

I want to JOIN these tables in a SELECT, but only add the rows from the other tables at the bottom of the exisiting table.

I'm trying to get an overall count for how many lessons and articles are associated with one particular category by using only one query. I've tried messing with the JOIN but can't seem to get it right.

SELECT lesson_id.lesson_categories AS content_id, article_id.article_categories AS content_id JOIN article_categories WHERE category_id = '45'

Obviously, this query doesn't work.
Ideally, output would look something like this:

category_id content_id
45 56
45 67
45 34
45 78
45 11
45 23

In this case, the content_id could be from any of the other xref tables as an article_id or lesson_id, but it wouldn't matter which... as long as it returns the correct number of rows.

Anyone know how to do this?

cvsherri

well...

withoout more information here is what i can guess that you have

CREATE TABLE [article] (
	[article_id] [int] NULL ,
	[catagory_id] [int] NULL ,
	[article_name] [char] (10) NULL 
)

CREATE TABLE [catagory] (
	[catagory_id] [int] NULL ,
	[catagory_name] [char] (10) NULL 
) 

CREATE TABLE [lesson] (
	[lesson_id] [int] NULL ,
	[lesson_name] [char] (10)  NULL 
) 

CREATE TABLE [xref_lesson] (
	[catagory_id] [int] NULL ,
	[lesson_id] [int] NULL 
)

if this being the case, i crate the following quesy to show which articles are associated to the lesson


SELECT DISTINCT  lesson.lesson_id, article.catagory_id, article.article_id
FROM    article INNER JOIN
             catagory ON article.catagory_id = catagory.catagory_id INNER JOIN
             xref_lesson ON catagory.catagory_id = xref_lesson.catagory_id INNER JOIN
              lesson ON xref_lesson.lesson_id = lesson.lesson_id
WHERE   lesson.lesson_id = 1

and thie next will only displat the catagories associated to the lesson


SELECT DISTINCT lesson.lesson_id, article.catagory_id
FROM    article INNER JOIN
             catagory ON article.catagory_id = catagory.catagory_id INNER JOIN
             xref_lesson ON catagory.catagory_id = xref_lesson.catagory_id INNER JOIN
              lesson ON xref_lesson.lesson_id = lesson.lesson_id
WHERE   lesson.lesson_id = 1

hope it helps.. If you need more help, plese post more info. such as the tables structure

cvsherri

also, it would be good to know what databse you are using, MySQL, MSSQL, other