View that returns all in one table & NOT in other

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

View that returns all in one table & NOT in other

Author

Topic

alecl
Starting Member

13 Posts

Posted - 2002-05-10 : 02:22:50

I can't seem to find a way to the view vsections do what I like. I'd like to be able to do something like:

select book_id, chapter_id, section_id, title from vsections where book_id=5 and chapter_id=1 and course_id = 1000

and have it return all of the sections for that course that aren't hidden (no entries in section hidden)
(subquery example of what result I want returned, but isn't suitable for use in the view because it would need the course_id inside the view):
SELECT book_id, chapter_id, section_id, title
FROM sections
WHERE (section_id NOT IN
(SELECT section_id
FROM sectionshidden
WHERE course_id = 1000 AND book_id = 5 AND chapter_id = 1)) AND (book_id = 5) AND (chapter_id = 1)

I realize this can be accomplished with a stored procedure or using parameters, but I want it as a regular view so it can neatly be used in joins existing in a lot of legacy code.

Any ideas?

--

CREATE TABLE [dbo].[sections] (
[book_id] [int] NOT NULL ,
[chapter_id] [smallint] NOT NULL ,
[section_id] [smallint] NOT NULL ,
[title] [char] (80) NULL ,
[status] [smallint] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[sectionshidden] (
[course_id] [int] NOT NULL ,
[book_id] [int] NOT NULL ,
[chapter_id] [int] NOT NULL ,
[section_id] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE VIEW dbo.vsections
AS
SELECT s.book_id, s.chapter_id, s.section_id, s.title, s.status, h.course_id
FROM dbo.sections s LEFT OUTER JOIN
dbo.sectionshidden h ON s.book_id = h.book_id AND s.chapter_id = h.chapter_id AND s.section_id = h.section_id

--

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-10 : 03:08:09

just add a

WHERE sectionshidden.section_id IS NULL

to that query and it should help

Damian

alecl
Starting Member

13 Posts

Posted - 2002-05-10 : 09:59:18

It's not the section_id. I'm looking at a particular course. That would give me all the sections not removed from ANY course.

alecl
Starting Member

13 Posts

Posted - 2002-05-10 : 10:46:57

quote:

It's not the section_id. I'm looking at a particular course. That would give me all the sections not removed from ANY course.

I meant it's not just the section_id

To clarify, the course_id needs to come from outside the view in the where clause.

Merkin
Funky Drop Bear Fearing SQL Dude!

4970 Posts

Posted - 2002-05-10 : 11:01:03

My using section_id was just a way of filtering that side of the join.

Did you try that code ? If I understand you correctly, that may do it. If it doesn't, then I have not understood you properly

Damian

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-10 : 11:12:02

Merkin is right (at least I understand your problem the same way he does)

Another way to skin the same cat is . .


CREATE VIEW dbo.vsections 
AS 
SELECT s.book_id, s.chapter_id, s.section_id, s.title, s.status, h.course_id 
FROM dbo.sections s
WHERE not exists (
    SELECT 1
    FROM dbo.sectionshidden h
    WHERE ON s.book_id = h.book_id AND s.chapter_id = h.chapter_id AND s.section_id = h.section_id )

<O>

alecl
Starting Member

13 Posts

Posted - 2002-05-10 : 14:16:51

add section_id is null will make a section disappear if ANY course has it removed and I need it removed only if the course I'm looking at has it removed.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-10 : 15:03:02

quote:
add section_id is null will make a section disappear if ANY course has it removed and I need it removed only if the course I'm looking at has it removed.

I don't mean to be rude, but I'm having trouble believing this statement. You have provided your DDL (CREATE TABLE) statements, which is GREATLY appreciated. Could you provide us with some sample DML (INSERT) statements that would illustrate the challenge you're having? This would make it easier for us to tune the query.

And how does the system determine "the course I'm looking at"?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-10 : 15:23:41

quote:

add section_id is null will make a section disappear if ANY course has it removed and I need it removed only if the course I'm looking at has it removed.

alecl, a couple things are pretty clear. One, you did not try Merkin's code. Two, didn't try my code. Three, you are thinking iteratively and not set based. SQL doesn't have a "course I'm looking" . . . sql looks at sections and sectionshidden as sets of data.

select tablea from tablea left join tableb on tablea.col = tableb.col where tableb.col is null -> is a very standard way of saying 'Gimme the set of records in tablea that do not exists in tableb' . . . the view ddl that I gave you does excactly the same thing and was provided because it's a more natural 'english' readable query. What you are asking for your view is 'what are the sections(tablea) that are not hidden(in tableb)'.

Now, please, your question is sql 101 . . . and Merkin, AjarnMark and myself have already taken this class and done quite well, thank you. You question has been asked and answered (twice). If you implemented the suggestions and are not getting the expected result, then you asked the question wrong. If you didn't implement the suggestions you should do that before telling Merkin it won't work.

<O>

alecl
Starting Member

13 Posts

Posted - 2002-05-10 : 16:28:50

I apologize if I haven't been clear on what I'm asking. I understood the solutions posted and the theory behind them (I'm past SQL101, thanks), but when you throw the courses, it's not as simple. I'm beginning to think that I cannot abstract this into a view because of the course_id and I'll just have to put the join with sections and sectionshidden everywhere.

Anyway, here's some sample data:

INSERT INTO sections (book_id, chapter_id, section_id, title, status)
VALUES (5, 1, 1, 's1', 1)
GO
INSERT INTO sections (book_id, chapter_id, section_id, title, status)
VALUES (5, 1, 2, 's2', 1)
GO
INSERT INTO sections (book_id, chapter_id, section_id, title, status)
VALUES (5, 1, 3, 's3', 1)
GO

INSERT INTO sectionhidden (course_id, chapter_id, section_id)
VALUES (1000, 1, 1)
GO

INSERT INTO sectionhidden (course_id, chapter_id, section_id)
VALUES (1000, 1, 2)
GO

INSERT INTO sectionhidden (course_id, chapter_id, section_id)
VALUES (1001, 1, 1)
GO

Now, if I do

select * from vsections where course_id=7

using Merkin's version of vsections, you get only the "s3" section returned, but that's not the result I want because course 7 does NOT have any sections hidden so all 3 sections should be returned.

In the very first post the subquery example shows how the course fits into things.

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-05-10 : 18:14:20

Your sample data does not work with your CREATE TABLE statements in your initial post. Book_ID is defined as NOT NULL, so you need something in your INSERT INTO sectionhidden statements. (BTW, you left out the S also.)

So, which needs to be fixed? The Table Def or the INSERT statement?

(YES, I actually tried it and actually got an error message.)

Edited by - AjarnMark on 05/10/2002 18:14:54

alecl
Starting Member

13 Posts

Posted - 2002-05-11 : 18:07:22

Sorry. Should be:

INSERT INTO sectionshidden (course_id, book_id, chapter_id, section_id)
VALUES (1000, 5, 1, 1)
GO

INSERT INTO sectionshidden (course_id, book_id, chapter_id, section_id)
VALUES (1000, 5, 1, 2)
GO

INSERT INTO sectionshidden (course_id, book_id, chapter_id, section_id)
VALUES (1001, 5, 1, 1)
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources