Question re: SELECT DISTINCT 1

[Livia8]

Hi,

I know very little about sql and Toad, so this will most likely sound like another stupid question.

I'm trying to run the following query:

SELECT * FROM FA_CATEGORIES_VL, FA_CATEGORY_BOOKS WHERE ENABLED_FLAG <> 'N'

My problem is that the FA_CATEGORIES_VL table has one column called CATEGORY_ID, and if I run the query I get multiple rows for the same category_ID.

I know how to use "DISTINCT" when I check a single column or a specific list of columns (e.g. SELECT DISTINCT FROM FA_CATEGORIES_VL.CATEGORY_ID FROM FA_CATEGORIES_VL, FA_CATEGORY_BOOKS WHERE ENABLED_FLAG <> 'N'), but not when I want to see all the columns in both tables.

Could someone suggest a solution, please? Any other way to streamline the query would also be helpful, I know that tables can be joined but I have no idea how to do it, I'm afraid.

Many thanks.

 

[Andrzejek]

Looks to me that FA_CATEGORIES_VL and FA_CATEGORY_BOOKS are separate tables, right?
If so, you need to connect them by a field (fields?) Primary Key field to Foreign Key field

You need something like (a guess here):
[tt]
SELECT FA_CATEGORIES_VL.*, FA_CATEGORY_BOOKS.*
FROM FA_CATEGORIES_VL INNER JOIN FA_CATEGORY_BOOKS
ON FA_CATEGORIES_VL.FIELD = FA_CATEGORY_BOOKS.Field
WHERE FA_CATEGORY_BOOKS.ENABLED_FLAG <> 'N'
[/tt]
" I know that tables can be joined [they HAVE to be joined] but I have no idea how to do it, I'm afraid."
You MUST know how to do it, read about it.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Andrzejek]

Since you have mentioned TOAD. You can learn a lot about how to join tables in your SQL by using Query Builder in TOAD:

You can add objects (tables, views, etc.) and the SELECT statement is built for you (if you establish PK / FK relations between tables). Or, you can drag-and-drop field(s) from one table to another to define the relation between objects.

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Livia8]

Thank you very much, Andy, much appreciated, I'll try that script.

I'm afraid my version of TOAD is ancient (7.6.0.11) and doesn't seem to have a query builder function - not that I can see, anyway.

This thread is the result of my not having had any formal training in this and having to pick things up on the job while being left to my own devices for the most part (staff shortages and whatnot). I know very little so I try to learn things as I go along, but even looking things up on the internet can get very confusing. I'm just glad I haven't broken anything, yet...

Thanks for the link to that website, too, it'll be very useful.

 

[Andrzejek]

The example SQL I gave you can also be written as:
[tt]
SELECT FA_CATEGORIES_VL.*, FA_CATEGORY_BOOKS.*
FROM FA_CATEGORIES_VL, FA_CATEGORY_BOOKS
WHERE FA_CATEGORIES_VL.FIELD = FA_CATEGORY_BOOKS.Field
AND FA_CATEGORY_BOOKS.ENABLED_FLAG <> 'N'[/tt]

That's using pre-1999 approach, not used much anymore, but it shows you how you can connect two tables. One way or another, you still need to know how to relate the tables.

You can find some SQL Query Builder applications on the Net

Or, get the (free) Query Builder in Oracle SQL Developer 3.0

---- Andy

"Hmm...they have the internet on computers now"--Homer Simpson

 

[Nick_Evans1]

If you need to connect to database, you can try data connectivity components.

 

[SkipVought]

Livia,

Everyone, at one time or another, has been a novice at (you name it)

One of the benefits of Tek-Tips is that here there are lots of once-upon-a-time novices that have accumulated some knowledge and are willing and able to share it.

Here's a tip that may help you.

[pre]
SELECT
v.*
, b.*
FROM
FA_CATEGORIES_VL v
, FA_CATEGORY_BOOKS b
WHERE v.FIELD = b.Field
AND b.ENABLED_FLAG <> 'N'
[/pre]

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

You Matter...
unless you multiply yourself by the speed of light squared, then...
You Energy!