CODEWITHSUNDEEP
sqlsql-servert-sql
Mat Richardson
Mat Richardson

Reputation: 3606

SQL Dynamic join?

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.

Upvotes: 1

Views: 2943

Answers (5)

roman
roman

Reputation: 117380

SQL FIDDLE

select
    m.ID, m.name, m.multiple, dl.idlink,
    dl.documentName
from mainrecord as m
    left outer join documentlinks as dl on dl.IDlink = m.id
where
    m.multiple = 1 or 
    not exists (select * from documentlinks as t where t.idlink = m.id and t.id < dl.id)

Upvotes: 0

TechDo
TechDo

Reputation: 18629

How about this:

select * from mainrecord a inner join documentlinks b on a.Id=b.IDLink 
where b.id=(case 
   when a.multiple=1 then b.id 
   else (select max(id) from documentlinks c where c.IDLink=b.IDLink) end)

Upvotes: 0

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

Another solution (tested at SQL-Fiddle):

SELECT m.*, 
       d.id as did, d.documentName, d.IDLink
FROM mainrecord AS m
  JOIN documentlinks AS d
    ON  d.IDLink = m.id 
    AND m.multiple = 1 

UNION ALL

SELECT m.*, 
       d.id as did, d.documentName, d.IDLink
FROM mainrecord AS m
  JOIN
    ( SELECT d.IDLink
           , MAX(d.id) AS did
      FROM mainrecord AS m
        JOIN documentlinks AS d
          ON  d.IDLink = m.id 
          AND m.multiple = 0
      GROUP BY d.IDLink
    ) AS g
    ON g.IDLink = m.id 
  JOIN documentlinks AS d
    ON  d.id = g.did

ORDER BY id, did ;

Upvotes: 2

PHeiberg
PHeiberg

Reputation: 29811

This will probably do:

SELECT mainrecord.name, documentlinks.documentname
FROM documentlinks
INNER JOIN mainrecord ON mainrecord.id = documentlinks.IDLink AND multiple = 1

UNION

SELECT mainrecord.name, documentlinks.documentname
FROM (SELECT max(id) id, IDLink FROM documentlinks group by IDLink) maxdocuments
INNER JOIN documentlinks ON documentlinks.id = maxdocuments.id
INNER JOIN mainrecord ON mainrecord.id = documentlinks.IDLink AND multiple = 0

Upvotes: 1

shahkalpesh
shahkalpesh

Reputation: 33474

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

Upvotes: 2

Related Questions