Geek asks and answers

Select Intersection / Join Tables

Many times just finding the right article and reading it on StackOverflow helped me through many things that I did not know what to do, but for the first time, I think I need to write it. I was looking for the right answer to this problem, but I could not find it.

So bugger is that I created 2 tables and one intersection table to link them.

The idea is so simple (I'm almost confused that I can not solve this problem).

One BBS article MAY have REQUIRED files. This means that one article can have an attachment or not. One article may have several attachments.

What I tried to do is

Get a list of articles with all the information about your attachments, but without duplicate lines.

Ok ... I tried installing DDL, but I did not format it correctly ...

create table article(
id PK
some other stuff...
)

create table attachment(
id PK
physical_file_name 
etc...
)


create table article_attachment(
id PK(synthetic)
article_id FK
attachment_ID FK
)

, , . (, )

, , DBA SQL, ...

?

-p.s. - - ...

with refined_table as(

  select file_id, row_number() over(partition by id order by id desc) as seq
  from consumer_file

)
select * 
from consumer_info ci 
left outer join consumer_file cf on cf.consumer_id = ci.id
left outer join refined_table rt on rt.file_id = cf.file_id
where rt.seq =1

, .

.  ORA-00979: GROUP BY . "" ( , ).

  with refined_table as(

      select file_id, row_number() over(partition by id order by id desc) as seq
      from consumer_file

    )

?

:)

SELECT 
    CI.id as article_id, 
    DF.id as attachment_id,  
    DF.PHYSICAL_NAME as file_name 
FROM 
    CONSUMER_INFO CI
LEFT OUTER JOIN 
    CONSUMER_FILE CF ON CF.CONSUMER_ID = CI.ID
LEFT OUTER JOIN 
    DEFAULT_FILE DF ON CF.FILE_ID = DF.id
GROUP BY 
   CI.ID
+3
3

1- > N → , :

create table attachment(
   id PK
   article_id FK 
   physical_file_name 
   etc...
)

, N > N.

: , - N ↔ N ( ), GROUP BY GROUP article_id, :

SELECT 
    article.id as article_id, 
    attachment.id as attachment_id,  
    attachment.physical_file_name as file_name 
FROM 
    article 
LEFT OUTER JOIN 
    article_attachment ON article_id = article.id 
LEFT OUTER JOIN 
    attachment ON attachment_id = attachment.id
GROUP BY 
    article_id
+5

, . , , , , :

SELECT *
  FROM ARTICLE a
  LEFT OUTER JOIN (SELECT ARTICLE_ID, MIN(ATTACHMENT_ID) AS ATTACHMENT_ID
                     FROM ARTICLE_ATTACHMENT
                     GROUP BY ARTICLE_ID) aa
    ON (aa.ARTICLE_ID = a.ID)
  LEFT OUTER JOIN ATTACHMENT t
    ON (t.ID = aa.ATTACHMENT_ID)

.

0

, , :

select
  art.article_id, art.article_stuff,
  att.attachment_id, att.attachment_stuff
from
  article art,
  article_attachment aat,
  attachment att
where
  art.article_id = &article_id
  and aat.article_id = art.article_id
  and att.attachment_id = aat.attachment_id
  and rownum < 2;

rownum < 2 , article-article_attachment-attachment.

0

More articles:


All Articles