question Urgent Help On Sql

[security]

Hello guys
Konchem kindha unna Sql pyna help kaavali..meeku vasthey ikkada post cheyandi
Thanks


Suppose you have the two tables defined below (document, and document_line).



We'd like a query that will select all of the document ids which DO NOT have lines that are incomplete(document_line.incomplete = TRUE). Please note, the query should return ids for 'empty' documents (ie. no lines whatsoever in the document_line table). The query should return the list of ids with no duplicates.



We'd like as many variants as you can come up with. Please try to provide different solutions using the following SQL features: NOT EXISTS, NOT IN, LEFT JOIN, EXCEPT, GROUP BY, HAVING



Here is an example solution:

SELECT document.id FROM document WHERE (SELECT COUNT(*) FROM document_line WHERE document_line.document_id = document.id AND

incomplete) =0;





Here are the tables:

CREATE TABLE document(

id INTEGER NOT NULL PRIMARY KEY

);

CREATE TABLE document_line(

id INTEGER NOT NULL,

document_id INTEGER NOT NULL REFERENCES document(id),

line_number INTEGER NOT NULL,

incomplete BOOLEAN NOT NULL

);

[massmaharaj]

use intersect , or minus 

[riashli]

Ltt

[loveindia]

Assuming incomplete = 1 means incomplete document.

 

SELECT DISTINCT a.id AS DocumentID
FROM Document a

WHERE NOT EXISTS (SELECT 1 FROM Document_Line b ON b.document_id = a.id AND a.incomplete = 0)

 

SELECT DISTINCT a.id AS DocumentID
FROM Document a

WHERE EXISTS (SELECT 1 FROM Document_Line b ON b.document_id = a.id AND a.incomplete = 1)

 

SELECT DISTINCT a.id AS DocumentID FROM Documents a

INTERSECT

SELECT b.document_id FROM Document_Line b WHERE b.incomplete = 1

 

 

Car lo unna so for now ivi... Inka left join vaadochu, inner join... many other ways.... enjoy.... 

[Darshwana]

Assuming incomplete = 1 means incomplete document.

 

SELECT DISTINCT a.id AS DocumentID
FROM Document a

WHERE NOT EXISTS (SELECT 1 FROM Document_Line b ON b.document_id = a.id AND a.incomplete = 0)

 

SELECT DISTINCT a.id AS DocumentID
FROM Document a

WHERE EXISTS (SELECT 1 FROM Document_Line b ON b.document_id = a.id AND a.incomplete = 1)

 

SELECT DISTINCT a.id AS DocumentID FROM Documents a

INTERSECT

SELECT b.document_id FROM Document_Line b WHERE b.incomplete = 1

 

 

Car lo unna so for now ivi... Inka left join vaadochu, inner join... many other ways.... enjoy.... 

sHa_clap4  sHa_clap4  sHa_clap4

[gudachari007]

select distinct d.id as document_id from document d

INNER JOIN document_line dl on dl.document_id = d.id and dl.incomplete = 0

[gudachari007]

select document_id from document_line GROUP BY document_id , incomplete having incomplete = 0

[jamesbond]

Assuming incomplete = 1 means incomplete document.

 

SELECT DISTINCT a.id AS DocumentID
FROM Document a

WHERE NOT EXISTS (SELECT 1 FROM Document_Line b ON b.document_id = a.id AND a.incomplete = 0)

 

SELECT DISTINCT a.id AS DocumentID
FROM Document a

WHERE EXISTS (SELECT 1 FROM Document_Line b ON b.document_id = a.id AND a.incomplete = 1)

 

SELECT DISTINCT a.id AS DocumentID FROM Documents a

INTERSECT

SELECT b.document_id FROM Document_Line b WHERE b.incomplete = 1

 

 

Car lo unna so for now ivi... Inka left join vaadochu, inner join... many other ways.... enjoy....