Selecting rows that only contain TRUE values

[omegatemplar]

I have a database with a 15 columns
1 is a title
14 other are true/false categories containing genre types

For example:

BookName | Scifi |Fantasy |Mystery
Star Trek | True | True | False

How would I select the book name and all the values that are true for that specific book so that I get back

BookName | Scifi | Fantasy
Star Trek | True | True

Thanks.

 

[mrdenny]

That's your basic select statement.

select BookName, SciFi, Fantasy
from Table
where Fantasy = 'True'
  and SciFi = 'True'

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[omegatemplar]

Ah, thats one solution that I know but what I am really trying to ask is that lets say I take a book name and I want to find out what genre(s) it is in, meaning I only want to list the columns in the row that have a true value without necessarily knowing the column names.

SELECT * FROM Table WHERE BookName = 'SomeTitle' would get me the columns with false results also, is there a way to do it so that you only ge the true results?

 

[mrdenny]

You'll want to do this on the front end.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[juliane26]

this is not possible to accomplish using SQL on your table.

mrdenny is right: it is more a way to represent the result later in the frontend, but not how to select.
Like you represent only the genres with TRUE.

(There is no clause saying: select this column only if ...)

Still, what would be possible is something like

select bookname, 'SciFi' from books where scifi=TRUE and bookname ='your name'
union all
select bookname, 'Mystery' from books where mystery=TRUE and bookname ='your name'
union all
select bookname, 'Fantasy' from books where fantasy=TRUE and bookname ='your name'

actually the bookname doesn't need to be returned, you know it already.
be careful: code (new union all clause) and ddl (new column) have to be changed for a new genre.


Juliane