General SQL question

[transparent]

I have 3 tables:


• tbl_document

which has rows:

document_id
document_name
etc

• tbl_document_group_intersect

which has rows:

document_id
group_id

• tbl_document_department_intersect

which has rows:

document_id
department_id


From these tables I want to return document information (ie document_id, document_name) for documents that DONT exist in tables:

• tbl_document_department_intersect
• tbl_document_group_intersect

How would I do this?
Cheers

 

[aolb]

I think it's something like this:

SELECT *
FROM tbl_document
LEFT JOIN tbl_document_group_intersect
ON tbl_document. document_id = tbl_document_group_intersect. document_id
LEFT JOIN tbl_document_department_intersect
ON tbl_document. document_id = tbl_document_group_intersect. document_id
WHERE tbl_document_group_intersect. document_id = null OR
tbl_document_group_intersect. document_id = null

 

[CMR]

Try the following query:

select * from tbl_document
where not exists (select * from tbl_document_group_intersect where tbl_document_group_intersect.document_id = tbl_document.document_id)
and not exists ( select * from tbl_document_department_intersect where tbl_document_department_intersect.document_id = tbl_document.document_id)

Hope this helps

CMR

 

[Fampofo]

Hi try this script.....

SELECT distinct tbl_document.* FROM tbl_document
WHERE document_id NOT IN
(SELECT document_id from tbl_document_group_intersect WHERE document_id is not null)
UNION
SELECT distinct tbl_document.* FROM tbl_document
WHERE document_id NOT IN
(SELECT document_id from tbl_document_department_intersect
WHERE document_id is not null)

Note: that the null values in the query is important for this to work. Any FK field in the slave tables must either have a value(PK) from the tbl document or NULL.

good luck
Frank

 

[transparent]

Cheers for the help.

I was hoping to find a method that didn't use not exists or not in as I have been informed that they are slow.

I really appreciate your suggestions.