Help with select - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with select

Author

Topic

maevr
Posting Yak Master

169 Posts

Posted - 2010-06-17 : 01:53:52

I have a library and I ONLY want to show the persons that have borrowed books with the language 'English'.

Persons may have borrowed several books with different language but I only want to view the Persons that borrowed books in English.

This is an assignment and I dont expect anyopne to give me an solution but any tips or hints would be good since I am stuck.

Tables:

PERSON
id (pk)
name

LENDING
id (pk)
person_id (fk)
item_id (fk)

BOOK
id (pk)
title

BOOK_LANGUAGE
book_id (pk)(fk)
language_id (pk)(fk)

LANGUAGE
id (pk)
language

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 02:41:11

I assume than BOOK.id is the same as LENDING.item_id? In that case:

Join all the tables together and add a "WHERE LANGUAGE.language = 'English'" at the end. A join is written like this

SELECT p.name, ... 
FROM PERSON p 
  INNER JOIN LENDING l 
    ON p.id = l.person_id

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

maevr
Posting Yak Master

169 Posts

Posted - 2010-06-17 : 02:53:43

Hi and thanks for the fast reply.

Your solution is exactly as the one I made myself, but does this solve the case that I ONLY want to show the Persons that ONLY borrowed books in english.

I do not want to view Persons that have borrowed books in english and german for example.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 03:01:59

What does the query you have so far look like?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

maevr
Posting Yak Master

169 Posts

Posted - 2010-06-17 : 03:44:03

Missed a table in previous post (ITEM, look at all tables below

My select:
select a.name, f.language from PERSON a
inner join LENDING b on a.id = b.person_id
inner join ITEM c on b.item_id = c.id
inner join BOOK d on c.book_id = d.id
inner join BOOK_LANGUAGE e on d.ID = e.book_id
inner join LANGUAGE f on e.LANGUAGE_ID = f.ID
and f.LANGUAGE like 'english'

PERSON
id (pk)
name

LENDING
id (pk)
person_id (fk)
item_id (fk)

ITEM
id (pk)
book_id (fk)

BOOK
id (pk)
title

BOOK_LANGUAGE
book_id (pk)(fk)
language_id (pk)(fk)

LANGUAGE
id (pk)
language

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 04:04:06

Does this work? Just created it on the fly...:

;with cte AS (
	select a.name, f.language 
	from PERSON a 
		inner join LENDING b on a.id = b.person_id
		inner join ITEM c on b.item_id = c.id
		inner join BOOK d on c.book_id = d.id
		inner join BOOK_LANGUAGE e on d.ID = e.book_id
		inner join LANGUAGE f on e.LANGUAGE_ID = f.ID)
SELECT *
FROM cte AS cte1
WHERE LANGUAGE = 'english' 
	AND NOT EXISTS (SELECT 1 FROM cte AS cte2 WHERE cte1.name = cte2.name AND cte2.language <> 'english')

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

maevr
Posting Yak Master

169 Posts

Posted - 2010-06-17 : 04:20:38

It seems to work, can you please explain how it works?

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-06-17 : 04:39:57

Well...it basically creates sort of an inline view (actually a common table expression, hence the cte-name) of your original query. You can basically just imagine that you take the results of the query and insert everything in to a new table. The only difference is that a cte is a "virtual table".

Then it first selects all the names that have lent books in english from the cte (exactly as your query) but then it also adds the NOT EXISTS which removes all names that have lent books in other languages than in english. Notice the cte1.name = cte2.name in the subselect, which joins the names that have lent books in english with the subselect.

Not sure if this was a good explanation...

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources