SQL question - sub-query results in more than one row.?

Question

Hello guys, im new to sql. i need help.

I have two tables:
1. "bulan" with column "month"
http://imgur.com/8DXRQ.png
2. "assignment" with columns "vessel_id" and "month"
http://imgur.com/pU3fr.png

I want to do this:

SELECT month FROM bulan WHERE month <> (SELECT month FROM assignment WHERE vessel_id='v01')

which the resulting should be "february" and "may".

but it return "ERROR Sub query returns more than one row"

I am stumped. any help guys?

Answer 1

Both above answers pretty good - all I would change is



a) add DISTINCT for the NOT IN subselect (no reason to have the same value occur more than once)

b) SELECT 1 instead of SELECT * in the NOT EXISTS subselect (no need to retrieve actual data rows - doing so just adds unnecessary overhead and all you care is whether it's there or not)

Answer 2

Replace the "<>" with "NOT IN" as in the first answer, or use a correlated subquery, e.g.



select month from bulan a

where not exists (select * from assignment b where a.month=b.month and b.vessel_id='v01');



Depending on which database you are using and the size of the tables you are querying, a correlated subquery may be faster than the un-correlated subquery (NOT IN).



There is a difference between these two queries if the result of the subquery can be null. If that is true, the query using NOT IN will not return any rows at all. For that reason, I prefer to use the correlated subquery.

Answer 3

you're comparing each and each man or woman row against the traditional volume due for a individual bill for the time of all distributors and in easy terms grouping for each seller those man or woman invoices above that wide-spread. in case you stumble on visualizing the computations perplexing, try making a digital table first and then do each and every thing from there: FROM distributors v connect Invoices i ON v.VendorID = i.VendorID connect (opt for VendorID, InvoiceTotal - PaymentTotal - CreditTotal AS BalanceDue FROM Invoices) b ON v.VendorID connect b.VendorID you could now purely communicate with BalanceDue rather of repeating the computation numerous cases.

Answer 4

replace the "<>" with "Not In". Hope that works.



SELECT month FROM bulan WHERE month Not In (SELECT month FROM assignment WHERE vessel_id='v01')