CODEWITHSUNDEEP
sqloracle-databaseoracle11goracle-sqldeveloper
Poena
Poena

Reputation: 79

Using joins & then using only a subquery?

Okay so I'm doing some SQL revision and I am supposed to do a query to "use a join and not using a subquery, list the publishers who publish psychology books." and came up with this:

SELECT DISTINCT p.pub_name, t.category
FROM publishers p
INNER JOIN titles t
ON p.pub_id=t.pub_id
WHERE t.category='psychology';

The next part is to do it using a subquery but I'm not sure how you would do that without using joins in the subquery itself? Also how would I print out every second author?

The link is a pastebin to the database.

And this is the schema(I'm aware of the problem with the schema in regard to the discount table, not my schema).

Upvotes: 1

Views: 60

Answers (1)

vanlee1987
vanlee1987

Reputation: 181

You can use an IN clause:

SELECT 
    p.pub_name
FROM 
    publishers p
WHERE 
    p.pub_id IN (SELECT pub_id FROM titles t WHERE t.category = 'psychology)

Alternatively an EXISTS clause which is a little more complex but typically performs better:

SELECT 
    p.pub_name
FROM 
    publishers p
WHERE 
    EXISTS (SELECT 1 FROM titles t WHERE t.pub_id = p.pub_id AND t.category = 'psychology)

To get every second author:

SELECT 
    a.au_fname,
    a.au_lname
FROM 
    publishers p
JOIN
    titles t ON t.pub_id = p.pub_id
JOIN
    title_author ta ON ta.title_id = t.title_id
JOIN
    authors a ON a.au_id = ta.au_id
WHERE 
    t.category = 'psychology'
    AND a.au_ord = 2

Upvotes: 4

Related Questions