2

SQL Dynamic join?

 1 year ago
source link: https://www.codesd.com/item/sql-dynamic-join.html
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.

SQL Dynamic join?

advertisements

Please see http://sqlfiddle.com/#!3/2506f/2/0

I have two tables. One is a general record, and the other is a table containing related documents that link to that record.

In my example I've created a straightforward query which shows all records and their associated documents. This is fine, but I want a more complex situation.

In the 'mainrecord' table there is a 'multiple' field. If this is 0, then I only want the most recent document from the documents table (that is, with the highest ID). If it is 1, I want to join all linked documents.

So, rather than the result of the query being this:-

ID  NAME    MULTIPLE    DOCUMENTNAME    IDLINK
1   One     1           first document    1
1   One     1           second document   1
2   Two     0           third document    2
2   Two     0           fourth document   2
3   Three   1           fifth document    3
3   Three   1           sixth document    3

It should look like this:-

ID  NAME    MULTIPLE    DOCUMENTNAME    IDLINK
1   One     1           first document    1
1   One     1           second document   1
2   Two     0           fourth document   2
3   Three   1           fifth document    3
3   Three   1           sixth document    3

Is there a way of including this condition into my query to get the results I'm after. I'm happy to explain further if needed.

Thanks in advance.


WITH myData
AS
(SELECT mainrecord.*, documentlinks.documentName, documentlinks.idlink,
Row_number()
                  OVER (
                    partition BY mainrecord.ID
                    ORDER BY mainrecord.ID ASC) AS ROWNUM
FROM mainrecord INNER JOIN documentlinks
ON mainrecord.id = documentlinks.idlink)
SELECT *
FROM mydata o
WHERE multiple = 0 AND rownum =
(SELECT max(rownum) FROM mydata i WHERE i.id = o.id)
UNION
SELECT *
FROM myData
WHERE multiple = 1

http://sqlfiddle.com/#!3/2506f/57


About Joyk


Aggregate valuable and interesting links.
Joyk means Joy of geeK