Databases
StackOverflow2013
Postsdbo
POSelect from intersection / junction tables
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSelect from intersection / junction tables
    primarykey
    data
    text
    <p>Many times just find the right article and reading it on StackOverflow helped me through a lot of stuff that I didn't know what to do with but for the first time, I think I need to write one. I've been searching for the correct answer on this prob but I've failed to find one.</p> <p>So the bugger is that I've created 2 tables and one intersection table to relate them.</p> <p>The idea behind is so simple(I'm almost embarrassed that I can't solve this one).</p> <p>One BBS article MAY have SOME attached files. That means one article could have an attachment or not. One article can have multiple attachments.</p> <p>What I've been trying to do is</p> <p>get the list of articles with all the information on its attachments but without duplicate rows.</p> <p>Ok... I tried to put up the DDL but I coulndn't format it correctly...</p> <pre><code>create table article( id PK some other stuff... ) create table attachment( id PK physical_file_name etc... ) </code></pre> <p>and here is the intersection</p> <pre><code>create table article_attachment( id PK(synthetic) article_id FK attachment_ID FK ) </code></pre> <p>I want to select all the articles whether it has or hasn't any attachments but if one article has multiple attachments I only need one of the attachments. (it doesn't matter which one of them)</p> <p>Yes it sounds dumb but there is no DBA or SQL developer around here so I have to do all the things... pretty messed up I'm trying my best</p> <p>any wise ideas?</p> <p>Thanks in advance</p> <p>-p.s. - I've tried something like...</p> <pre><code>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 </code></pre> <p>this but I don't really understand how it works </p> <p>UPDATE</p> <p>Here is what I tried at first. It keeps giving me ORA-00979:not a GROUP BY expression I looked for the solution for a long time. A few people suggested using aggregation function or "Hint" (if that's what it is...)</p> <pre><code> with refined_table as( select file_id, row_number() over(partition by id order by id desc) as seq from consumer_file ) </code></pre> <p>I mean this </p> <p>Hmm any ideas ?</p> <p>Many thanks all of you : )</p> <pre><code>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 </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USAscendant
    1. USAscendant
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSelect from intersection / junction tables
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POSelect from intersection / junction tables
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSelect from intersection / junction tables
      1. This table or related slice is empty.
      1. VTUpMod
    1. COalso using " group by article_id" gave me "invalid identifier" error. It seems valid to me though haha.
      singulars
      1. POSelect from intersection / junction tables
      1. USAscendant
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload