SELECT * FROM Questions WHERE Type = 'Stupid'

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'

mister_gosh: May 24, 2002

Just the thread I need!

I need some help. I have a table called relations. It stores an OBJECT, its PARENT, and the objects VERSION. So, for example,

code:

OBJECT  PARENT  VERSION AREA
============================
4567    1234    98989   1
4567    4356    98989   1
4522    4356    91102   1
4522    4356    91102   2

I want a result set that contains distinct VERSIONs, I don't care which PARENT I get, but I need it. I only want results from AREA 1. So I want my results to be:

4567 1234 98989
4522 4356 91102

My query is:

code:

SELECT object, parent, version from relations where area='1';

Which gives me the results:
4567 1234 98989
4567 4356 98989
4522 4356 91102

Just throwing a distinct command after the select distinct obviously won't do it. I've tried constructing a nested join but can't get it right (I keep putting things in the wrong order, like - select distinct version from relations in (select...)).

Can anyone help? I'm totally lost.

mister_gosh fucked around with this message at 03:48 on Dec 6, 2007

# ¿ Dec 6, 2007 03:45

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 19:30

mister_gosh: May 24, 2002

Satan's Scallion posted:

The cheap way around this is to just use max() or min() to get the one you want. Like:
code:
SELECT r.object, max(r.parent), r.version
  FROM relationships r
  WHERE r.area = 1
  GROUP BY r.object, r.version;
This will work as long as you only have the single un-grouped column you want data from, if you have multiple then you're going to need an inline view and a join back to the ungrouped table.

Works perfectly!

# ¿ Dec 6, 2007 05:16

mister_gosh: May 24, 2002

$5 paypal to whoever can fix this:

Now I have a situation where I'm retrieving an OBJECT.

Once I have the number, I want to get the OBJECT parent but only if it is of TYPE 201.

So for example, I'm working with OBJECT 4567. Since there are multiple PARENT entries, I only want to get the one that is of TYPE 201 but that info is in a separate table.

TABLE RELATIONS:

code:

OBJECT  PARENT  VERSION AREA
============================
4567    1234    98989   1
4567    5678    98989   1

TABLE INFOPOOL:

code:

ID      TYPE  
=============
1234    201  
5678    202

This has to be simple, I'm just not getting it. I'm thinking it's a join, but then it needs to be part of a WHERE clause.

mister_gosh fucked around with this message at 05:10 on Dec 7, 2007

# ¿ Dec 7, 2007 05:06

mister_gosh: May 24, 2002

Satan's Scallion posted:

You mean like
code:
SELECT r.object, r.parent, r.version
  FROM relations r
  JOIN infopool i
    ON r.parent = i.id
 WHERE i.type = 201
?

Sounds like you just need a primer on basic SQL joins, there has to be a million of them out there.

Edit: please don't paypal me anything

In this instance, I know as I construct the query the OBJECT value, in this case 4567, so I want to include that in the conditions to ensure I only get one or so results.

I also failed to mention area needs to be included in this test. This appears to work:

code:

SELECT r.object, r.parent, r.version
  FROM relations r
  JOIN infopool i
    ON r.parent = i.id
 WHERE i.type = 201
   AND r.object='4567'
   AND r.area='1'

Thanks for the free code! I'm just overwhelmed with other work and didn't have the mental capacity to deal with this at the moment. I'm pretty sure I'm getting it now. Thanks again!

# ¿ Dec 7, 2007 05:34

mister_gosh: May 24, 2002

I want to get a result set through a recursively selecting query.

Table: relationships

code:

parent child
123    124

parent child
124    125
124    126

parent child
125    127
125    128
125    129

parent child
126    130
126    131

parent child
127    188

So, what I would want to end up with is 124,125,126,127,128,129,130,131,188 in my result set.

Put simply, I want all of the descendents of 123 according to this table (I should note that by descendents, I do mean that loosely, I don't think there is anything within the sql table linking them together other than the parent and child names).

At the time of execution, the only name/number I know is 123. Is there a way to do this?

Thanks for any input in advance!

# ¿ Feb 21, 2008 21:26

mister_gosh: May 24, 2002

var1ety posted:

Oracle has a CONNECT BY operator that lets you do this.

If I couldn't do that I would write a recursive row-returning function to crawl the tree.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The above link has info on MySQL approaches to handling nested data.

This works perfectly. Thanks!!

# ¿ Feb 21, 2008 21:47

mister_gosh: May 24, 2002

var1ety posted:

Oracle has a CONNECT BY operator that lets you do this.
code:
    select c
    from foo
    connect by (foo.p = prior foo.c)
    start with foo.p = 123
    order by 1
code:
    124
    125
    126
    127
    128
    129
    130
    131
    188
If I couldn't do that I would write a recursive row-returning function to crawl the tree.

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

The above link has info on MySQL approaches to handling nested data.

This worked perfectly until I encountered a query that was expecting over 31000 results.

The query essentially just sits there.

I tried looking at your link, but the mysql keywords aren't compatible with Oracle 10g.

Can you (or anyone) steer me towards something that might be workable? Since this query worked until it reached a certain query that needed too many results, I wonder if there is a way to break the query up. SQL/querying doesn't seem too hard of a concept, but now that I dig in here, I give you guys a lot of credit for the possible complexity in situations where nesting and over conditions are a concern.

Thanks in advance for any input. My original post on this was on page 13 (right in the middle).

# ¿ Apr 7, 2008 20:04

mister_gosh: May 24, 2002

I'm creating some tables to track the number of times user's log in to my database and to track how often certain tools are executed.

I think these are my tables: User (includes location and product their affiliated to), Product, Tool, Login

A typical month may show:

John is located in Sarasota, FL, works with Acme FX (product), logged in to the database once every day in January (twice on 1/15), executed the Grow tool on 1/11, 1/14 and twice on 1/26, executed the Kill Whitey tool on 1/2 and five times on 1/25.

Jane is located in North Pole, Greenland and ...

Based on this stuff, I eventually want to write an application which can extract information such as usage from Florida, John's usage, a certain tools usage, etc.

My question is, how do I model this? I want to track the Grow application on a day to day basis. I want to track that a certain user used it, on which days, etc. I want to pull up the number of logins for all users for 2008, or just John and just for January 2009. Do I create another table called Usage with many date types? Do I create a monthly table with rows for 1-31?

Any links to creating this type of database table?

mister_gosh fucked around with this message at 22:12 on Jan 26, 2009

# ¿ Jan 26, 2009 22:07

mister_gosh: May 24, 2002

Are there any good free or cheap database modeling tools out there like Embarcadero or Toad? I can't justify spending close to or over $1000 on something like that.

Triple Tech posted:

mister_gosh: Not sure tool is, but you probably want a table called "events" or something. Whatever your atomic unit of use is. Are you differentiating the difference between an application login and the use of a specific tool? Or does the mere use of a tool imply an immediate login? Anyway, you'll have

events: id, user_id, product_id, date_time

This will tell you who used what product and when. The questions you ask on top of that (what events happened in January, who used it the most, who used it last year, etc) sit on top of a table that looks like this. The table is simple, asking the questions and developing the application are the hard parts.

Thanks, I'll try it out!

# ¿ Jan 27, 2009 14:29

mister_gosh: May 24, 2002

mister_gosh posted:

I'm creating some tables to track the number of times user's log in to my database and to track how often certain tools are executed.

I think these are my tables: User (includes location and product their affiliated to), Product, Tool, Login

A typical month may show:

John is located in Sarasota, FL, works with Acme FX (product), logged in to the database once every day in January (twice on 1/15), executed the Grow tool on 1/11, 1/14 and twice on 1/26, executed the Kill Whitey tool on 1/2 and five times on 1/25.

Jane is located in North Pole, Greenland and ...

Based on this stuff, I eventually want to write an application which can extract information such as usage from Florida, John's usage, a certain tools usage, etc.

My question is, how do I model this? I want to track the Grow application on a day to day basis. I want to track that a certain user used it, on which days, etc. I want to pull up the number of logins for all users for 2008, or just John and just for January 2009. Do I create another table called Usage with many date types? Do I create a monthly table with rows for 1-31?

Any links to creating this type of database table?

Ok, Triple Tech, et al, this is what I came up with. I'm not completely sure if these are technically foreign keys or not (I'm self taught and sort of just beginning in RDBMS stuff).

Anyways, based on some analysis, I think I will average about 12,000 events a month. Does this model seem to support that? I'm guessing by everything being a char or int type that this won't fill much data, but I would like to avoid creating any bottlenecks or other problems. Any guess as to how big the initial allotment of space should be? How does a table with eventually 500,000 rows with simple types sound? Normal?

The user table will max out at about 100 rows, the location table will be about 20 rows, the product table about 20 rows and the tool table about 30 rows, so it's just the events I'm concerned with.

As each event occurs, I will create a new row in the usage_event table, fill out the date and get the user id, product id and tool id based on who executed what. The usage_event_id will be auto-incrementing unique id - not sure how to do that yet, but I'm still at the planning stage.

Only registered members can see post attachments!

mister_gosh fucked around with this message at 21:42 on Jan 28, 2009

# ¿ Jan 28, 2009 19:17

mister_gosh: May 24, 2002

Not sure if this entirely proper to post here, but I've been searching for over 15 minutes now for Oracle Client 10.1.0.5 (not instant, I need the full admin package) but can't find it anywhere. Does anyone know how to find it?

Edit: maybe it's only on metalink...crap, now I have to go through all of the red tape in my company to find out how to get access

mister_gosh fucked around with this message at 02:23 on Feb 9, 2009

# ¿ Feb 9, 2009 01:59

mister_gosh: May 24, 2002

I'm looking to create hot (and cold) backups of a production MySQL database. Are there any tools out there that are proven/standard?

# ¿ Nov 15, 2011 16:21

mister_gosh: May 24, 2002

Thanks for the recommendations!

# ¿ Nov 16, 2011 16:09

mister_gosh: May 24, 2002

I have a table which stores queries (example data in parens):

code:

query
-----
id (1)
etc (...)

Now I want to run queries in batches and I don't know how. For example, let's say I have 1-100 queries and I want to store a set of queries (1, 5, 88) and maybe choose to run them. I could analyze a stored batch of queries (see that it wants to run queries 1, 5, 88) and I could add or remove queries from that batch query anytime.

I have no idea how to design this.

code:

batch_query
-----------
id (1)
query_ids (1,5,88)

I can't/shouldn't just create a field called query_ids and separate the values with commas, that seems silly. It would've been fun to set up the query id as a FK but I realize that is probably impossible.

Another not so hot idea::

code:

batch_query
-----------
id      (1)  
query_1 (1)
query_2 (5)
query_3 (88)

This is limiting, as I would have a set limitation.

Any ideas? Thanks!!

# ¿ Nov 30, 2011 19:38

Adbot: ADBOT LOVES YOU

# ¿ May 7, 2024 19:30

mister_gosh: May 24, 2002

Got it, thanks for the quick response!

# ¿ Nov 30, 2011 20:07

The Something Awful Forums > Discussion > Serious Hardware/Software Crap > The Cavern of COBOL > SELECT * FROM Questions WHERE Type = 'Stupid'