CODEWITHSUNDEEP
sqlsql-servert-sqljoinselect
squatman
squatman

Reputation: 71

Two JOINS from same Table

I have two tables Document and Xref.

Xref is a means of linking a document to another document.

The Xref Table stores the id of the document (from the documents table) and it's linked xref (also from the documents table)

I want to retrieve a list of filenames for the Xrefs that are linked to a particular Document.

Please see attached image for a basic representation of my Tables.

The following SQL statement retrieves the (xref)filename I want, but I want to be able to use the filename not it's id to retrieve this.

Any help would be appreciated. Thank you.

enter image description here

Upvotes: 2

Views: 161

Answers (5)

GMB
GMB

Reputation: 222402

You need to JOIN both table. This should do it :

SELECT d.filename
FROM 
    Document d
    INNER JOIN Xref x ON x.xref_id = d.id
WHERE x.document_id = ?

You want to replace the question mark with the id of the document whose filename you want to find.

If you want to search by document name instead of document id then you need one more JOIN :

SELECT d.filename
FROM 
    Document d
    INNER JOIN Xref x ON x.xref_id = d.id
    INNER JOIN Document d2 ON d2.id = x.document_id
WHERE d2.filename = ?

NB : this assumes that every document has a linked document in table Xref. If you want NOT to filter out documents without Xref, you want to use « LEFT JOIN » instead of « INNER JOIN ».

Upvotes: 1

Shawn
Shawn

Reputation: 4786

If you want to keep documents that don't have a sub-document, then use a LEFT OUTER JOIN to get all main documents.

Also, I used a sub-select to link the sub-documents to the xref table, then linked that back to the main document query. 

SELECT d1.id AS main_docid, d1.filename AS main_filename
    , s1.sub_docid AS sub_docid, s1.sub_filename AS sub_filename
FROM document d1 
LEFT OUTER JOIN (
    SELECT x.document_id AS main_docid 
        , d2.id AS sub_docid
        , d2.filename AS sub_filename
    FROM xref x 
    INNER JOIN document d2 ON x.xref_id = d2.id
) s1 ON d1.id = s1.main_docid
WHERE d1.filename = ??????? /* Whatever filename you are searching for. */

If you want to search by a sub_filename, then change your WHERE statement to WHERE s1.sub_filename = ???????

Upvotes: 0

Joakim Danielson
Joakim Danielson

Reputation: 51861

Join twice against Xref, once to get the ref file filename and once to search on filename for main document

SELECT d2.filename
FROM Document d
JOIN Xref x ON d.id = x.document_id
JOIN Document d2 ON d2.id = x.xref_id
WHERE d.name = ?

For example

SELECT d2.filename
FROM Document d
JOIN Xref x ON d.id = x.document_id
JOIN Document d2 ON d2.id = x.xref_id
WHERE d.name = 'D001'

returns

filename
X001

Upvotes: 2

EpsilonTal
EpsilonTal

Reputation: 457

You need to SELECT your main data and then use the JOIN on some field or column.

LEFT JOIN will keep data from the left group.

INNER JOIN will result with the matches only.

Need to make sure, is the filename unique? Anyway,

If you want to use LEFT JOIN in order to keep the lefts with a null value, use:

SELECT DOCUMENT.filename FROM Xref XREF
LEFT JOIN Document DOCUMENT ON XREF.document_id = DOCUMENT.id
-- Filter by filename:
--WHERE DOCUMENT.filename = ? OR XREF

Otherwise, I would suggest choosing INNER JOIN in order to get the matched rows:

SELECT DOCUMENT.filename FROM Xref XREF
INNER JOIN Document DOCUMENT ON XREF.document_id = DOCUMENT.id
-- Filter by filename:
--WHERE DOCUMENT.filename = ?

Result:
| filename |
| D001 |
| D002 |

SELECT * FROM - will result in displaying every column, you need to define what columns will be displayed on your new view.

EDIT Regarding your comment, let's say you have two options for the input, you can use:

SELECT DOCUMENT.filename FROM Xref XREF
INNER JOIN Document DOCUMENT ON XREF.document_id = DOCUMENT.id
-- Filter by filename:
WHERE DOCUMENT.filename = ? XREF.id = ?

Upvotes: 0

The Impaler
The Impaler

Reputation: 48770

The other solutions are fine for a single-level search. If you want a multi-level search you can use a CTE, assuming there are no cycles in the data:

with
x as (
  select id, filename from document where filename = 'mydoc1.txt'
  union all
  select d.id, d.filename
  from x
  join xref r on r.document_id = x.id
  join document d on d.id = r.xref_id
)
select * from x;

Upvotes: 0

Related Questions