How to construct a query

[bdichiara]

I have 2 tables and I would like to run 1 query to pull data out of both tables. I guess it would be some sort of join. I have 1 table with a huge list of songs, all with a category associated with each song.

I have another table that has a list of categories that the user wants to show up in their list (kind of like a filter).

How do I run 1 query to get ALL the categories in table1 (using group by, so there's only 1 of each) and then a custom field in the query that says 1 or 0 if it was found in the other table (table2).

The output would be something like this:
Category1 1
Category2 1
Category3 1
Category4 1
Category5 0
Category6 1
Category7 0

The data in table1 might look like this

blah Category1
blah Category1
blah Category1
blah Category2
blah Category2
blah Category3
blah Category3
blah Category3
blah Category4
blah Category4
blah Category5
blah Category6
etc...

And the data will only list the category 1 time, and if it does show up, then the new field in the query should be "1" otherwise "0"

_______________
_brian.

 

[r937]

select t1.category
     , case when t2.category is null 
            then 0 else 1 end   as found_in_t2
  from (
       select distinct category
         from table1
       ) as t1
left outer
  join table2 as t2
    on t2.category = t1.category

r937.com | rudy.ca