Jordan Jordan - 2 months ago 6
SQL Question

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).


Answer

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
Comments