It may be impossible if you want to get the result set from a single SQL statement. But by using such set operators as UNION you can meet the target. The statement has been listed below and it has been written in Sybase format.
select c.nr, r.regcode from customer c, registration r where c.nr *= r.customer_nr UNION select r.customer_nr, rc.regcode from registrationcode rc, registration r where rc.regcode *= r.regcode
You're right. The number of returned records is not a coincidence. You are getting a Cartesian join (every record in one table joined with every record in another table). You are using the wrong syntax in your query. The way to specify that you are joining tables based on equality in certain fields is by using a JOIN statement. The syntax is
SELECT field1, field2 FROM table1 INNER JOIN table2 ON table1.field3 = table2.field3;This returns records where the values of field3 are the same in both tables. LEFT JOIN will return rows from table1 even if there's no matching value in table2. RIGHT JOIN is the opposite. Since you are using four tables you need a series of three nested joins. For three tables it could look like this:
FROM (t1 LEFT JOIN t2 ON t1.f1 = t2.f1) LEFT JOIN t3 ON t2.f2 = t3.f2;This one executes the join in parens first.
BTW, it would have been really fun with 5000 and 10000 records instead of 4 and 7! ;-)
SELECT ArtNr, Bex, "True" FROM Article WHERE ArtNr IN (SELECT Article FROM Basket) UNION SELECT ArtNr, Bex, "False" FROM Article WHERE ArtNr NOT IN (SELECT Article FROM Basket); OR: SELECT Article.*, Basket.Article FROM Article, OUTER Basket WHERE ArtNr = Basket.Article INTO TEMP fred; UPDATE fred SET Article = "TRUE" WHERE Article IS NOT NULL; UPDATE fred SET Article = "FALSE" WHERE Article IS NULL; SELECT * FROM fred; DROP TABLE fred;
SELECT Article.*, ArtNr In (SELECT Article FROM Basket WHERE BNr =1) AS In_Basket_For_Person_1 FROM Article;
SELECT Table1.AccountDate, Table2.AccountDate FROM Table1 LEFT JOIN Table2 ON Table1.AccountDate = Table2.AccountDate WHERE (((Table2.AccountDate) Is Null));
select distinctrow t1.* from (((t1 left join t2 on t1.id = t2.id) left join t3 on t1.id = t3.id) left join t4 on t1.id = t4.id) left join t5 on t1.id = t5.id where t2.id is null and t3.id is null and t4.id is null and t5.id is null
select count(*) SEQ, a.BAR from FOO a, FOO b where a.BAR >= b.BAR group by a.BAR
as i read this, you want one numeric result that is the total number staff members who have one and only one comment. if so:
select count(*) from (select staff_id from comments where dept_no = 'G33' group by staff_id having count(*) = 1)i'm pretty sure that you can also can use nested group functions, too, something like:
select count(count(*)) from comments where dept_no = 'G33' group by staff_id having count(*) = 1not in front of a SQL prompt to check, though. or you could try:
select count(distinct staff_id) from comments where dept_no = 'G33' and staff_id not in (select staff_id from comments where dept_no = 'G33' group by staff_id having count(*) > 1)there's a million of 'em.....
select * from files a where 20 < (select count(*) from files b where a.size < b.size) order by size desc;Perhaps you will have to replace '<' by '<=' or '>' by '>='. This is a quite straight forward approach but it should work.
Here are some of the possible SQL sollutions:
Sollution 1. (*very slow* on large tables): ------------ SELECT naam, size FROM files a WHERE 20 >= (SELECT COUNT(size) FROM files b WHERE b.size >= a.size) ORDER BY a.size DESC; Sollution 2. (much faster than previous one) ------------ SELECT a.naam, a.size FROM files a, (SELECT rowid x, -1*size y FROM files GROUP BY -1*size, rowid) b WHERE a.rowid = b.x AND rownum <= 10; Sollution 3. (the fastest one) ------------ SELECT a.naam, a.size FROM files a , dual WHERE -1*a.size = DECODE(dual.dummy(+),'X',NULL,NULL) AND rownum <= 10;
the following should do the trick. In fact, I tested it and it works. The only problem is that there may be more film makers with the maximum number of films (hence the "in" operator). If you want just one of them, add rownum=1 to the outer where condition.
select maker_name from film_maker where filmmaker_id in ( select filmmaker_id from film group by filmmaker_id having count(*)= ( select max(count(*)) from film group by filmmaker_id));