Databases
StackOverflow2013
Postsdbo
POComplex SQL query (suggestions needed)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POComplex SQL query (suggestions needed)
    primarykey
    data
    text
    <p>Could someone throw me ideas about how to resolve the following issue:</p> <p>I have pictures, videos and tags in my database. Tags can be associated to pictures and videos. I have to query database for tag IDs and count that are modified before 7 days or earlier and sort them by count of associations they have to picture and video assets.</p> <p>So the idea is that at the end I can output the tags that were associated to pictures or videos the most during last 7 days. I wrote down the DB structure with the fields I have</p> <pre><code>VIDEO ID PICTURE ID PICTURE_ATTRMAPPING CS_OWNERID (ID of picture) CS_ATTRID ( will store ID of the tag attribute (picture_tag or video_tag)) ASSETVALUE ( will store ID OF THE associated tag) VIDEO_ATTRMAPPING CS_OWNERID (ID of video) CS_ATTRID ( will store ID of the tag attribute (picture_tag or video_tag)) ASSETVALUE ( will store ID OF THE associated tag) TAG ID UPDATEDATE ATTRIBUTES (picture_tag attribute ID is stored here) ID NAME </code></pre> <p>So we can see that tags, videos, picture and attributes are stored in separate tables. We can tell that tag is referenced by video or picture (in other words, video/picture has a tag association) if VIDEO/PICTURE_ATTRMAPPING table has record with video or picture tag attribute id in CS_ATTRID column and id of the tag in ASSETVALUE column. </p> <p>I am assuming it will be query with sub-queries, so I started to break this task in sub-tasks and figure out how to obtain all the information that's needed.</p> <p>I will definitely have to get the ID of the tag attributes for video and picture objects:</p> <pre><code>SELECT id FROM ATTRIBUTES WHERE NAME = 'picture_tag' OR NAME = 'video_tag' </code></pre> <p>Also an example of how I can query for tags that are n days old:</p> <pre><code>SELECT id FROM TAG WHERE updateddate BETWEEN TO_DATE('2013-08-20 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND CURRENT_DATE </code></pre> <p>This probably isn't that complex as it seems, however, I am not sure about how the whole query should look like and with what to start. Could someone throw an idea or sample?</p> <p>I am using oracle, but am familiar with mysql too, so samples from any of the DBMSs would be great. Let me know if I made myself clear enough.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJavatar
    1. USJavatar
    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
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

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