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'

«‹›303 »

chocojosh: Jun 9, 2007; D00D.

Azerban posted:

So I've got a schema that looks like this;

Student (sID: integer, sName: string, major: string, year: string)
Class (cName: string, meets_at: time, roomno: string, fID: integer)
Faculty (fID: integer, fName: string, deptID: integer)

with a relationship;

Enrolled (sID: integer, cName: string)

And I need to write a bunch of check constraints, and one in particular is kicking my rear end. It needs to make sure faculty in different departments can never teach in the same room; ie each room is specific to each department. I have no idea where to start, the logic is evading me. Anybody able to point me in the right direction?

If I understand your post, we need to avoid having
record1.roomno = record2.roomno
AND record3.deptID != record4.deptID
AND record1.fID = record3.fID
AND record2.fID = record4.fID

Normally when I need to simultaneously access two records in the same table it's a hint for me to look at self-joins.

# ? Mar 3, 2009 23:44

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 00:12

sd6: Jan 14, 2008; This has all been posted before, and it will all be posted again

Learning PL/SQL and have a small question.

code:

CURSOR e IS SELECT *
             FROM employees;
e_rec e%ROWTYPE;

Can someone show me the syntax for declaring that inside of a procedure?

# ? Mar 4, 2009 01:16

var1ety: Jul 26, 2004

fge posted:

Learning PL/SQL and have a small question.
code:
CURSOR e IS SELECT *
             FROM employees;
e_rec e%ROWTYPE;
Can someone show me the syntax for declaring that inside of a procedure?

There's an example of declaring an explicit cursor in the PL/SQL User's Guide at the following URL:

http://download-west.oracle.com/docs/cd/B19306_01/appdev.102/b14261/overview.htm#BABFAAAJ

Here's an example using a wildcard instead of explicitly listing the columns:

code:

declare
  cursor e is
    select * from all_users;
  e_rec e%rowtype;
begin
  open e;
  loop
    fetch e
      into e_rec;
    exit when e%NOTFOUND;
    dbms_output.put_line('username = ' || e_rec.username);
  end loop;
  close e;
end;
/

I only use explicit cursors if I'm fetching a large number of rows and processing in bulk or if I am returning a cursor from a procedure, however. For other cases, I will use an implicit cursor like so:

code:

begin
  for row in (select * from all_users)
  loop
    dbms_output.put_line('username = ' || row.username);
  end loop;
end;
/

# ? Mar 4, 2009 02:53

sd6: Jan 14, 2008; This has all been posted before, and it will all be posted again

Ok I think I got it, thanks.

# ? Mar 4, 2009 03:09

SLOSifl: Aug 10, 2002

I just got a pretty hosed up email from work. We're making some pretty big cutbacks (a lot of people lost their job, and it's a small company), so I'm looking for ideas to help out.

We have a lot of in-house toolkits that my coworker and I have developed to make our lives easier. One of them is a pretty powerful database abstraction layer for MSSQL 2005, 2008 and 201x.

It's a versioned database abstraction layer that emits and consumes only XML, handles all table relationships automatically (including bridge objects - eg Object A can be associated with Object B through Object C), with a Web Service and .NET API. It also provides events (web service calls, email, etc), has tons of filtering options, and can attach to any number of databases.

We use it to lessen the annoyance of an industry standard database, and it's actually faster than even well optimized queries most of the time.

The feature list is very long, and the amount of things it does for us is crazy.

I'm considering talking to the CEO about packaging it as a distinct product. Of all you SQL people, does anyone think this is a sellable product for use as middleware? If you could work with an annoying database through well defined XML based objects, would that help you?

I'll be happy to field any questions here if appropriate, via PM or AIM, or by email. I'm not looking for actual buyers, I'm just curious if such a tool could be marketed in the near future. I feel bad even posting this, but since I have to take a 10% pay cut just to keep my job, I think it's worth probing for interest.

# ? Mar 4, 2009 08:20

Spiffo: Nov 24, 2005

In SQL Server, I'm trying SELECT * but so that a specific subset of records appears first. Right now it's:

SELECT * FROM Account WHERE emaildomain = @domain

I need to change it so that it still gets all the records, but gets people from a specific country FIRST, followed by the rest of the results. I tried:

SELECT * FROM Account WHERE emaildomain = @domain AND Country = @country
UNION
SELECT * FROM Account WHERE emaildomain = @domain

but that didn't seem to have an effect on the recordset.

Is there an easy way to put a specific set of records at the beginning of the recordset? Something along the lines of "ORDER BY Country = @country" isn't any sort of valid syntax.

# ? Mar 4, 2009 16:38

var1ety: Jul 26, 2004

Spiffo posted:

In SQL Server, I'm trying SELECT * but so that a specific subset of records appears first. Right now it's:

SELECT * FROM Account WHERE emaildomain = @domain

I need to change it so that it still gets all the records, but gets people from a specific country FIRST, followed by the rest of the results. I tried:

SELECT * FROM Account WHERE emaildomain = @domain AND Country = @country
UNION
SELECT * FROM Account WHERE emaildomain = @domain

but that didn't seem to have an effect on the recordset.

Is there an easy way to put a specific set of records at the beginning of the recordset? Something along the lines of "ORDER BY Country = @country" isn't any sort of valid syntax.

One thing that works for me in Oracle which might translate to SQL Server is placing a function in the order by clause, like this:

code:

select * from account order by case when country = 'USA' then 1 end, country

# ? Mar 4, 2009 16:49

Flamadiddle: May 9, 2004

Edit: Maybe not what you were looking for.

# ? Mar 4, 2009 17:28

Victor: Jun 18, 2004

SLOSifl posted:

It's a versioned database abstraction layer that emits and consumes only XML, handles all table relationships automatically (including bridge objects - eg Object A can be associated with Object B through Object C), with a Web Service and .NET API. It also provides events (web service calls, email, etc), has tons of filtering options, and can attach to any number of databases.

I would encourage you to compare&contrast this with the other similar products out there. That should give you a better idea of what is in demand.

# ? Mar 4, 2009 18:30

SLOSifl: Aug 10, 2002

Yeah, I'm looking around at other products right now. This sucks, I don't know what to do if the company I've spent 10 years at goes under.

# ? Mar 4, 2009 18:49

Victor: Jun 18, 2004

If the tool you made is as cool as it sounds, that's a pretty sweet thing to put on your resume...

# ? Mar 4, 2009 18:54

SLOSifl: Aug 10, 2002

Touche.

I haven't found any competing products yet that do anything close to what ours does yet. I'm not sure if that's good or bad, since it makes it hard to determine whether there's a market or not.

# ? Mar 4, 2009 19:06

sd6: Jan 14, 2008; This has all been posted before, and it will all be posted again

Another PL/SQL question, I have a record like this:

code:

type employees_table is table of employees%rowtype
      index by binary_integer;
      
    e_table employees_table;
    i binary_integer;
  
    cursor e is 
      select * from employees;
    e_rec e%rowtype;

and I do a cursor for loop and set all the rows of the cursor to each index of the record. I need to know two things. First, how can I delete a specific row of the e_table record? Secondly, I want to make a while loop that's something like this:

code:

while (count < 5) or (e_table is not empty) loop

What would be the syntax for that?

# ? Mar 4, 2009 19:35

Victor: Jun 18, 2004

SLOSifl posted:

I haven't found any competing products yet that do anything close to what ours does yet. I'm not sure if that's good or bad, since it makes it hard to determine whether there's a market or not.

Start with NHibernate (Ayende has done a lot with it, including NHProf) and Entity Framework. You might also be interested in Frans Bouma's adventures in adding LINQ functionality to LLBLGen. Actually, I offer to hack out a good list of specs for your product which we can then present to Ayende, who can probably help you out greatly. Then, perhaps we can do a three-way call with an EtherPad instance and do something pretty cool. (I've done this once before with him.)

# ? Mar 4, 2009 19:56

var1ety: Jul 26, 2004

fge posted:

Another PL/SQL question, I have a record like this:
code:
type employees_table is table of employees%rowtype
      index by binary_integer;
      
    e_table employees_table;
    i binary_integer;
  
    cursor e is 
      select * from employees;
    e_rec e%rowtype;
and I do a cursor for loop and set all the rows of the cursor to each index of the record. I need to know two things. First, how can I delete a specific row of the e_table record? Secondly, I want to make a while loop that's something like this:
code:
while (count < 5) or (e_table is not empty) loop
What would be the syntax for that?

You can delete a specific element as follows.

code:

declare
  type tb1 is table of varchar2(10) index by pls_integer;
  v1 tb1;
begin
  v1(1) := 'one';
  v1(2) := 'two';
  v1(3) := 'three';
  dbms_output.put_line(v1(v1.first));
  v1.delete(1);
  dbms_output.put_line(v1(v1.first));
end;

code:

one
two

Check out the PL/SQL User's Guide for information on looping. Maybe you want to use v1.count.

# ? Mar 4, 2009 20:31

Spiffo: Nov 24, 2005

Spiffo posted:

SELECT * FROM Account WHERE emaildomain = @domain AND Country = @country
UNION
SELECT * FROM Account WHERE emaildomain = @domain

but that didn't seem to have an effect on the recordset.

Whoop, while that didn't work, it looke like

SELECT * FROM Account WHERE emaildomain = @domain AND Country = @country
UNION
SELECT * FROM Account WHERE emaildomain = @domain AND Country <> @country

does. If it didn't, the CASE solution Var1ety posted will also work.

# ? Mar 4, 2009 21:52

sd6: Jan 14, 2008; This has all been posted before, and it will all be posted again

var1ety posted:

Check out the PL/SQL User's Guide for information on looping. Maybe you want to use v1.count.

Well I know what kind of loop I need to use, I should have been more specific, my loop is supposed to repeat either until the counter limit is reached, or the e_table record is empty. How do I test if e_table is empty or not? Or is there some function that returns the number of elements in a record?

# ? Mar 4, 2009 22:11

var1ety: Jul 26, 2004

fge posted:

Well I know what kind of loop I need to use, I should have been more specific, my loop is supposed to repeat either until the counter limit is reached, or the e_table record is empty. How do I test if e_table is empty or not? Or is there some function that returns the number of elements in a record?

e_table.count will return the number of elements it contains.

# ? Mar 4, 2009 22:36

sd6: Jan 14, 2008; This has all been posted before, and it will all be posted again

var1ety posted:

e_table.count will return the number of elements it contains.

Oh that's what you meant by v1.count, I'm dumb. Thank you!

# ? Mar 4, 2009 23:07

v1nce: Sep 19, 2004; Plant your brassicas in may and cover them in mulch.

On MySQL I have multiple tables representing magazines and their covers:

code:

meg_covers                prog covers

issue | character_id      issue | character_id
------+----------         ------+----------
1.01  | 50                1     | 50
1.02  | 999               2     | 50
1.03  | 50                3     | 997
510   | 998               1.15  | 996

What I want to do is get a list of which issues feature character 50 in these tables, hopefully resulting in something like the following, although the only important part is the issue number and being able to differentiate between the progs and megs:

code:

meg_issue | meg_character | prog_issue | prog_character
----------+---------------+------------+---------------
1.01      | 50            |            |
1.03      | 50            |            |
          |               | 1          | 50
          |               | 2          | 50

I've tried the following query and lots of variations thereof:

code:

SELECT
  *
FROM
    prog_covers
  , meg_covers
WHERE
  prog_covers.character_id = 50 OR meg_covers.character_id = 50

But its been so long since I did SQL I've forgotten my way around, and we end up with a table like the following, presenting all possible combinations of results:

code:

meg_issue | meg_character | prog_issue | prog_character
----------+---------------+------------+---------------
1.01      | 50            | 1          | 50
1.01      | 50            | 2          | 50
1.03      | 50            | 1          | 50
1.03      | 50            | 2          | 50

Multiply the number of items in the table up to several hundred and the query is spitting back 280 thousand results each time.

Can anyone help shed some light on this or point me in the right direction? I had tried using a UNION between two SELECTs, but the fact it combined the issue results into the same column made it impossible to differentiate between them.

# ? Mar 5, 2009 18:07

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

Prog and meg are two different objects. Why don't you just run two seperate queries?

# ? Mar 5, 2009 18:09

WhoIsBarryBostwick: Sep 17, 2008; You're going to like the way you look...I guarantee it.

v1nce posted:

On MySQL I have multiple tables representing magazines and their covers:

You could try something like:

code:

SELECT issue as meg_issue, character_id as meg_character, '' as prog_issue, 
    '' as prog_character
FROM meg_covers
WHERE character_id = 50
UNION
SELECT '' as meg_issue, '' as meg_character, issue as prog_issue, 
    character_id as prog_character
FROM prog_covers
WHERE character_id = 50

# ? Mar 5, 2009 20:59

v1nce: Sep 19, 2004; Plant your brassicas in may and cover them in mulch.

Brilliant! Thanks WhoIsBarryBostwick - that was exactly what I was after. It seems so obvious now, padding the result sets across with empty variables.

Triple Tech - there are actually a lot more tables we have to search and the number may increase in the future, so a single query that takes a few moments may work out more time efficient than our slow-rear end system sending MySQL repeated queries. Maybe.

# ? Mar 5, 2009 23:06

KarmaticStylee: Apr 21, 2007; Aaaaaughibbrgubugbugrguburgle!

found the problem. i'm retarded.

KarmaticStylee fucked around with this message at 02:48 on Mar 6, 2009

# ? Mar 6, 2009 02:44

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

How do I select multiple things at once, and stuff them into the same cell?

Okay I know that sounded vague, here is an example.

Normally, someone says:

code:

SELECT title FROM node WHERE type = 1 LIMIT 20

This will return 20 separate rows:

"Title1"
"Title2"
...
"Title20"

Well, how do I query it so that it actually returns a delimited, single row, single coloum:

"Title1,Title2,Title3,Title4,Title5,Title6,...,Title20"

All in one row, one column?

EDIT: Using MYSQL

More specifically, MySQL and PHP.

cannibustacap fucked around with this message at 08:13 on Mar 6, 2009

# ? Mar 6, 2009 06:47

Nevett: Aug 31, 2001

Hey, I need a bit of help with my dumb database project.
I'm little more than an SQL beginner, I'm keeping a database of movies, heres a simple version of what the table looks like:

code:

Movie                 Tag           TagValue
-----------------------------------------------
Wall-E                Genre         Animation
Wall-E                Genre         Adventure
Wall-E                Year          2008
Burn After Reading    Genre         Comedy
Burn After Reading    Genre         Crime
Burn After Reading    Year          2008

In the actual DB, Movie and Tag are foreign keys, TagValue is a Text type column.

Now, its easy enough to find movies that satisfy a single criteria:

select Movie from MovieTagValues where Tag="Year" and TagValue="2008";

But how do I go about finding movies that satisfy multiple criteria? Action movies from 2008 for example.

Can it be done in a single query or do I need to do more processing on the application side?

Also, if you're wondering about the database design itself, it has to handle arbitrary numbers of and content of tags, so all this data can't exist on a single row of a 'Movies' table unfortunately.

Nevett fucked around with this message at 06:49 on Mar 6, 2009

# ? Mar 6, 2009 06:47

Victor: Jun 18, 2004

cannibustacap, what DBMS are you using?

# ? Mar 6, 2009 07:38

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

Victor posted:

cannibustacap, what DBMS are you using?

Using MYSQL

# ? Mar 6, 2009 08:07

Bad Titty Puker: Nov 3, 2007; Soiled Meat

Nevett posted:

Hey, I need a bit of help with my dumb database project.
I'm little more than an SQL beginner, I'm keeping a database of movies, heres a simple version of what the table looks like:
code:
Movie                 Tag           TagValue
-----------------------------------------------
Wall-E                Genre         Animation
Wall-E                Genre         Adventure
Wall-E                Year          2008
Burn After Reading    Genre         Comedy
Burn After Reading    Genre         Crime
Burn After Reading    Year          2008
In the actual DB, Movie and Tag are foreign keys, TagValue is a Text type column.

Now, its easy enough to find movies that satisfy a single criteria:

select Movie from MovieTagValues where Tag="Year" and TagValue="2008";

But how do I go about finding movies that satisfy multiple criteria? Action movies from 2008 for example.

Can it be done in a single query or do I need to do more processing on the application side?

Also, if you're wondering about the database design itself, it has to handle arbitrary numbers of and content of tags, so all this data can't exist on a single row of a 'Movies' table unfortunately.

Google up "Entity-Attribute-Value", it's an absolute trainwreck of a denormalized design. The fact that querying for Action movies from 2008 is at all hard to write is already a tip-off. CHECK constraints are practically impossible to write, and so data integrity goes down the tubes as soon as there is a typo in one tag value. In EAV, the more data that goes into the table, everything gets worse: performance, data integrity, query complexity.

Genre and Year are attributes of movies. They need to be columns on the Movies table, with correct datatypes (don't use Text for everything!), CHECK constraints, and (where applicable) lookup tables.

If this is your own little project, then

quote:

Also, if you're wondering about the database design itself, it has to handle arbitrary numbers of and content of tags, so all this data can't exist on a single row of a 'Movies' table unfortunately.

doesn't *have* to be that way. If you have control of the schema, it will pay off over and over again to use a normalized design.

# ? Mar 6, 2009 08:15

Nevett: Aug 31, 2001

The project is a virtual filesystem where files can be given arbitrary tags and values. The movie example is just one database I've made during testing. It seems like this is one of the scenarios where EAV is (cautiously) justified.

The problem is that the code for the database consumer (the VFS driver) needs to be as generic as possible so that it can load up a database of movie files, music files, documents, or a mix, and work just the same.

The project is still in its early stages, so I'll definitely investigate some other methods of constructing the database. Perhaps have the VFS read the schema of a database and adapt rather than relying on the tables being exactly how I've set them up currently.

Thanks for the input!

# ? Mar 6, 2009 08:47

Victor: Jun 18, 2004

If it's a virtual filesystem, why not use an object-oriented database?

# ? Mar 6, 2009 09:21

C.Y. Phillis: Jan 28, 2006

I'm wondering if someone could help me with a query I'm trying to build using MySQL, I hope I can explain this without sounding like an idiot.

Basically, I have a list of jobs that have failed with a timestamp attached to them. I'm trying to do a count and a group by to see how many times each individual job fails, but the timestamp messes that up.

Basically, the data in the column looks like:

FOO_BAR_JOB_NAME Failed Fri Mar 6 13:22 2009
ANOTHER_STUPID_JOB Failed Fri Mar 6 13:25 2009

All I want to do is to match how many times each job failed. I would do this individually but we have literally thousands of jobs like this, and this is going be in a script that will send someone an email if it finds a job that fails too much.

What I have pictured in my head is something that would return like this:

code:

    Job                  count
--------------------------------------
FOO_BAR_JOB_NAME          30
OTHER_STUPID_JOB          10
THIS_ALWAYS_BREAKS        54

# ? Mar 6, 2009 19:27

Triple Tech: Jul 28, 2006; So what, are you quitting to join Homo Explosion?

I need help figuring out a workflow and I'm not sure how to go about it... Let's say I'm transforming (ETL?) data from Table A to Table B. Table A has a composite primary key A.a+A.b+A.c, while Table B has just an automatically populated identity column. How can I map the composite keys from A back to the identities created when inserting into B?

Preferably I would like to not have any columns in table B related to A's composite key because there are many other tables that need to undergo the same operation but don't have the same composite key structure.

This is SQL Server.

# ? Mar 6, 2009 20:28

SLOSifl: Aug 10, 2002

You can map them through a third table if you can't update table B.

code:

declare @id int

-- insert into B
select @id_b = @@identity

insert into MapAtoB ( ID_A_a, ID_A_b, ID_A_c, ID_B ) values ( @id_a_a, @id_a_b, @id_a_c, @id_b )

# ? Mar 6, 2009 22:11

geetee: Feb 2, 2004; >;[

cannibustacap posted:

How do I select multiple things at once, and stuff them into the same cell?

Okay I know that sounded vague, here is an example.

Normally, someone says:
code:
SELECT title FROM node WHERE type = 1 LIMIT 20
This will return 20 separate rows:

"Title1"
"Title2"
...
"Title20"

Well, how do I query it so that it actually returns a delimited, single row, single coloum:

"Title1,Title2,Title3,Title4,Title5,Title6,...,Title20"

All in one row, one column?

EDIT: Using MYSQL

More specifically, MySQL and PHP.

You can do:

code:

SELECT GROUP_CONCAT(`title`) FROM `node` WHERE `type` = 1 GROUP BY `type`;

There are some options for GROUP_CONCAT like what the separator is.

# ? Mar 7, 2009 04:00

cannibustacap: Jul 7, 2003; Brrrruuuuuiinnssss

geetee posted:

You can do:
code:
SELECT GROUP_CONCAT(`title`) FROM `node` WHERE `type` = 1 GROUP BY `type`;
There are some options for GROUP_CONCAT like what the separator is.

Sweet! Thanks

# ? Mar 7, 2009 07:01

zeldadude: Nov 24, 2004; OH SNAP!

I'm working on a clothing store website, and I'm trying to think of the best way to go about managing inventory (different sizes and colors, etc) with the database. Right now on my products table all I have is one field called "stock", which obviously doesn't cover colors or different sizes.

Anyone have any suggestions as for the best way to go about this? I'm new to this whole thing.

I was thinking of making a new table for just inventory, and then linking the product ID's. But then I'd have to have a bunch of different fields like, BLUESMALL, BLUEMEDIUM, BLUELARGE, etc etc and I'm pretty sure that isn't the best way.. but what do I know.

(Every day I'm beginning to wish I just used premade webshop software.

)

# ? Mar 8, 2009 06:57

Bad Titty Puker: Nov 3, 2007; Soiled Meat

zeldadude posted:

I'm working on a clothing store website, and I'm trying to think of the best way to go about managing inventory (different sizes and colors, etc) with the database. Right now on my products table all I have is one field called "stock", which obviously doesn't cover colors or different sizes.

Anyone have any suggestions as for the best way to go about this? I'm new to this whole thing.

I was thinking of making a new table for just inventory, and then linking the product ID's. But then I'd have to have a bunch of different fields like, BLUESMALL, BLUEMEDIUM, BLUELARGE, etc etc and I'm pretty sure that isn't the best way.. but what do I know.

(Every day I'm beginning to wish I just used premade webshop software. )

Since you're new at this, I would recommend reading a book (or two) (or three) on data modeling. You may get some good tips in the forums, but it's no place to get an education. Start from the beginning...

Bad Titty Puker fucked around with this message at 08:20 on Mar 8, 2009

# ? Mar 8, 2009 08:17

gotly: Oct 28, 2007; Economy-Sized

It's been a while since I've done SQL and I've forgotten a pretty basic thing I used to know how to do.

example of my problem:
Select title, sum(qty) from tablename
where sum(qty) > 10

This obviously doesn't work. How do you reference a sum, avg or renamed column like this?

# ? Mar 9, 2009 03:19

Adbot: ADBOT LOVES YOU

# ? May 10, 2024 00:12

No Safe Word: Feb 26, 2005

gotly posted:

It's been a while since I've done SQL and I've forgotten a pretty basic thing I used to know how to do.

example of my problem:
Select title, sum(qty) from tablename
where sum(qty) > 10

This obviously doesn't work. How do you reference a sum, avg or renamed column like this?

code:

select
    title
    ,sum(qty)
from
    tablename
group by
    title
having
    sum(qty) > 10

Untested, but that should do it.

# ? Mar 9, 2009 04:01

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

«‹›303 »