The following questions were taken from various database-related newsgroups on UNIX. Responses are shown as links to each question.
I have the following problem :
there are three tables ...
customer (nr int key, ....)
registration (regcode char, customer_nr int, reg_date date)
registrationcode (regcode char, artikel char)
There could be a customer who has a registration and therefor a registrationcode. there could be also a customer who has no registration and no registrationcode and a registration- code which has no customer asigned.
Now I´m looking for some way (in SQL) to show customer-data and registration-code - no matter if there is no customer or no registration code for each customer / registrationcode.
I would be very glad, if somebody could help me.
This SQL gives only all customers with and without registration- code (Code from Adabas D):
select c.nr,rc.recode from customer c, registration r, registrationcode rc where c.nr = r.customer_nr (+) and r.regcode (+) = rc.regcode
I am hoping someone can help me spot my error with this SELECT statement
Tables: SAO ------ SAOID Other info SAOPatent (two foreign keys) ------------------ SAOID PatentID Patents ----------- PatentID PatentTitle PatentHolderID PatentHolders ---------------------- PatentHolderID PatentHolderNameSo, this is a four-table join and given that I want to see the PatentTitle, SAOID and PatentHolderName for a given PAtentHolderID I entered:
SELECT PatentTitle, SAOID, PatentHolderName FROM SAO, SAOPatent, Patents, PatentHolders WHERE 0=0 AND Patents.PatentHolderID = PatentHolders.PatentHolderID AND SAO.SAOID = SAOPatent.SAOID AND Patents.PatentID = SAOPatent.PatentID AND PatentHolderID = 1I figured I'd get a list of all the PatentTitles where the PatentHOlder was PatentHolderID=1. Instead I am getting a list of about 28 records, all the same.
One thing I don't understand is that I should be getting a list of about 7 different patents associated with PatentHOlderID=1. Instead I am getting 28. I also find it curious that I have exactly 4 records in the PatentHolders database the co-incidence of 7*4=28 is too close for comfort.
I have 2 tables:
Article ------- ArtNr Bez ------------------- 111111 Artikel1 222222 Artikel2 333333 Artikel3 Basket ------ BNr Article ------------------ 1 1111111 2 2222222I want one select-statement that gets all artcle and if the basket for a special person has this article or not.
The result should look like that
ArtNr Bez In_Basket_for_Person_1 ----------------------------------------- 111111 Article1 True or something like that 222222 Article2 False or something like that 333333 Article3 False or something like that
I have two tables and I want to retrieve just the rows from the first table that don't match the second table. How can I do that? I tried using this query but it returns a cartesian product.
SELECT Table1.AccountDate FROM Table1 inner join table2 on table1.accountdate <> table2.accountdate
I hope someone can help give me an SQL statement to get the following:-
I have 2 tables, Emp and Dept.
Comments (3 fields) --- Staff_id Dept_no Staff_Comments 12345A S45 some comments 12345A S45 some other comments 99999Q G33 also comments Dept (2 fields) ---- Dept_no Dept_name S45 Architecture S46 Science G33 EconomicsI would like to know the statement where I can find the number of staff in > department 'G33' who has only made ONE comment (meaning only one entry in the > COMMENT TABLE)
how can i select the 20 biggest files from my table????????
table files ------------- naam varchar2(50) size integer
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
Return to example 1
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));
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;