How to SELECT with several conditions? (WHERE ... AND ... IN (...))

For example: in my database I have 3 tables: COMPANY, COUPON and COMPANY_COUPON. COMPANY table has fields: ID and NAME, COUPON table has: ID, TITLE and TYPE, COMPANY_COUPON table has: ID of the COMPANies and ID of the COUPONs that they own.

So, in java to get all coupons of the company I use command:

SELECT coupon_id FROM company_coupon WHERE company_id = ?

And put it into Collection.

But I need something to get all coupons of the company by the type, something like:

SELECT * FROM company_coupon WHERE company_id = 1 AND coupon_id = (SELECT * FROM coupon WHERE type = camping)

of course this one is not working, but I'm looking for something like that.

I know that i can get all coupons of the company and put them into Collection and then just delete all coupons that not equals to the specified type, but is there any way to do this process in database by SQL commands?

Solution

You might want to use WHERE IN here:

SELECT *
FROM COMPANY_COUPON
WHERE COMPANY_ID = 1 AND COUPON_ID IN (SELECT ID FROM COUPON WHERE TYPE = 'CAMPING');

You could also use EXISTS, which is probably the best way to write your logic:

SELECT cc.*
FROM COMPANY_COUPON cc
WHERE
    cc.COMPANY_ID = 1 AND
    EXISTS (SELECT 1 FROM COUPON c WHERE c.TYPE = 'CAMPING' AND c.ID = cc.COUPON_ID);

Using EXISTS might outperform doing a join between the two tables, because the database can stop as soon as it finds the very first match.