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'

«‹›6 »

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Opius posted:

I've got a table set up for allowing people to vote on content items, with the structure like so:
code:
INT id    INT content_id    ENUM(y,n) vote
The vote column is just a 'yes/no' on whether they liked that particular item or not.

The problem is trying to find the mostly liked content item from this table. The query I'm currently using is:
code:
SELECT
	`content_id`,COUNT(content_id) AS count
FROM
	`votes`
WHERE
	`vote` = 'yay'
GROUP BY
	`content_id`
ORDER BY
	`count` DESC
LIMIT 6
However, instead of just finding the items with the most 'yay' votes, I'd like to find the ones with the highest ratio of 'yay' votes to 'nay' votes.

Is there a particular way to assign ENUM values to 'fake' columns, so I could do '(yay/nay) AS ratio'? Or is there a better way to achieve the same result?

Any help is appreciated.

Perhaps something like

code:

SELECT
	`content_id`,
        sum(case `vote` when 'yay' then 1 else 0 end case) AS count,
        sum(case `vote` when 'yay' then 1 else 0 end case)
         /(sum(case `vote` when 'nay' then 1 else 0 end)+1) AS ratio
FROM
	`votes`
GROUP BY
	`content_id`
ORDER BY
	`ratio` DESC
LIMIT 6

Though now that I think about it, I'd probably do 'yay' over total rather than 'yay' over 'nay' as a better way to avoid the divide by zero error. So[code]sum(case `vote` when 'yay' then 1 else 0 end case)
/count(content_id) AS ratio

minato posted:

Edit: Jethro's solution is better, replace my count(vote) with "sum(case when vote="yay" then 1 else 0 end)".

But be sure to keep the cast to float, which I forgot to do.

Jethro fucked around with this message at 02:09 on Dec 3, 2007

# ¿ Dec 3, 2007 01:18

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 02:53

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

If you can't do that, how about the union clause (yes, I realize that this is probably not the most efficient)?

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5' AND
manufacturer IS NOT NULL
ORDER BY manufacturer
UNION ALL
SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5' AND
manufacturer IS NOT NULL
ORDER BY manufacturer

I think you meant to make one of those "IS NOT NULL"s an "IS NULL". Also, I think most DBs only let you have one ORDER BY clause which comes after all of the SELECT statments and orders the entire query.

kalleboo posted:

�ngstr�m, or god forbid 甘い贅沢 will break your system

I don't know about other DB systems, but MySQL lets you do

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY manufacturer IS NULL, manufacturer

In T-SQL you could do it that way, though you'd probably have to change it to

SELECT DISTINCT manufacturer
FROM planes
WHERE pilot_id='5'
ORDER BY CASE WHEN manufacturer IS NULL THEN 1 ELSE 0 END, manufacturer

# ¿ Feb 3, 2008 19:29

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Walked posted:

But anyone know why it's erroring out on the WHERE clause?

Try giving the subquery an alias. I.e.

code:

SELECT *
FROM
(
	SELECT id, title, subtitle, body,
	ROW_NUMBER() OVER(ORDER BY id DESC) as row
	FROM blog
) tbl
WHERE row BETWEEN (@blogPage - 1) * (@pageSize + 1) and (@blogPage * @pageSize)
ORDER BY id DESC

E:F,B.

# ¿ Feb 8, 2008 04:12

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Xae posted:

Sad, but profitable. You can spend a couple weeks, maybe a month, learning about SQL and how to properly use it, and every one thinks you are some super genius.

In the next couple weeks I'm totally gonna blow everyone at my job away when I break out the MDX.

# ¿ Feb 20, 2008 03:09

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Stephen posted:

I'm trying to select in MySQL one row for each auto in my Photos table where the orderid column is the lowest value:
code:
SELECT id, auto_id
FROM photos 
GROUP BY auto_id
ORDER BY orderid ASC 
This returns one row per auto, however it always returns the id of the last row instead of the id of the row with the lowest orderid.

Can anyone give me a hint on how I can get around this?

edit: Nevermind it seems I found a way around it by:
code:
SELECT * FROM (
SELECT id, auto_id
FROM photos 
ORDER BY orderid ASC 
) as test GROUP BY auto_id
I don't know how efficient this is, but oh well, it works.

It may work, but it probably shouldn't, and it definitely won't work anywhere but MySQL. In T-SQL or Oracle you could use windowing functions. In MySQL a better solution might be

code:

SELECT p1.id, p1.auto_id
FROM photos p1 left outer join
     photos p2 on p1.auto_id = p2.auto_id and p1.orderid < p2.orderid
where p2.id is null

# ¿ Mar 20, 2008 16:43

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Stephen posted:

Yeah, I figured I could do it with a join, but I was hoping there was a simple obvious answer that I was too stupid to see.

Not with MySQL, I don't believe.

# ¿ Mar 20, 2008 17:53

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

stack posted:

How can I make several tables keep their timestamps in sync with a central watch table?
code:
-- example trimmed down tables
CREATE TABLE `watch` (
 `server` char(20) NOT NULL,
 `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 PRIMARY KEY (`server`)
) Engine = InnoDB;

CREATE TABLE `email` (
 `server` char(20) NOT NULL,
 `updated` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) Engine = InnoDB;

-- other tables along the email table
My goal is to refresh the updated timestamp in watch when the settings in email,... where server=server is true and the non-watch table is updated.

Can this be done with foreign keys or do I need to use a trigger?

I think the best way to do it be to normalized your data by getting rid of the `updated` field in all the tables except `watch`, and just keep that one column in one table up to date.

EDIT: But yes, if you insist on doing it the other way (or are unable to change how things work) I'm pretty sure the only way to do it automagically would be to use a trigger.

# ¿ Apr 10, 2008 17:21

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

StonedogJones posted:

no i dont think replace will do it. the column has different text in each row, so i cant search for a string, and then replace something in that string, because the string is not constant for each row.

Replace(column,',','')

# ¿ Apr 15, 2008 06:40

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

fletcher posted:

I was just about to ask about this. It seems like a strange thing to need to specify. How do I know if it is a situation where I have to specify the index to use?

Do some sort of explain plan. If the plan is all retarded, then you may have to tell your RDMS to use your nice index.

# ¿ Apr 15, 2008 20:22

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Triple Tech posted:

Every day is like a rapid fire of dumb SQL questions to Victor and #cobol... Here's my current one for SQL Server. Is there a way to store the value of a column into a variable? In Perl-speak, I would like this.
code:
select sum(value), $column
from table
group by $column
order by $column
I've seen some examples where you essentially construct a string and then eval that string with EXECUTE(@query) but is there something more direct? Also, is that use of EXECUTE(@query) bad/slow?

You could use sp_executesql instead of EXECUTE, but I don't think it really gains you anything in this case. Either way, this is definitely a case of needing dynamic SQL, assuming there isn't a totally different way of doing what you want to do.

# ¿ Apr 18, 2008 17:45

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

I may be mistaken, but I believe that sp_executesql only allows for parameterization on the WHERE clause. That is, you cannot use sp_executesql to dynamically select different fields/tables (SELECT and FROM clauses), but it can be used for a dynamic where clause. This would be because sp_executesql does make an execution plan that it will re-use assuming you pass the exact same string to sp_executesql (note that you can use different parameter values as the string passed to sp_executesql takes a list of parameters).

That was my impression as well, which is why I didn't think that TT would get much out of using sp_executesql instead of EXECUTE.

# ¿ Apr 19, 2008 08:13

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

Except that I don't think TT CAN use sp_executesql -- it should be an error.

Note that sp_executesql is drat awesome for dynamic where conditions (think many different search criteria that don't need to apply).

I guess what I'm trying to say is that he could dynamically generate a query string with no parameters and then he could execute said query with either EXECUTE or sp_executesql and it wouldn't matter. He gains nothing by using sp_executesql since he does not actually have any parameters in his query and thus must re-generate it each time.

EDIT: So when I said "you could use sp_executesql" I mostly meant it as an alternate syntax, as opposed to an actual functional alternative.

Jethro fucked around with this message at 21:57 on Apr 19, 2008

# ¿ Apr 19, 2008 21:54

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

nbv4 posted:

I've got a pretty simple problem:
code:
SELECT DISTINCT route FROM flights, planes LEFT JOIN tags ON planes.plane_id = tags.plane_id
WHERE flights.plane_id = planes.plane_id
AND ((planes.category_class = 1 OR planes.category_class = 3))
AND flights.pilot_id = 22
AND flights.route IS NOT NULL
AND tags.tag != "Turbine"
Basically I have a table called "flights" which is linked to one plane, which is then linked to multiple tags via a tag table. If I run this query, it will return no results. If I remove the last line, it'll work perfectly. Even if I change the "!=" to "=", I still get zero results. I need to exclude any flights where the plane has that certain "Turbine" tag.

A few questions:

What RMDBS? MySQL?

Try using single quotes around 'Turbine'

If a plane has a 'Turbine' tag and a different tag (say, 'Butts'), do you want the flights associated with that plane? If not, then what you are trying to do will not work. I believe for that purpose, you would need to use a sub-select like

code:

SELECT route
  FROM flights,
       planes
 WHERE flights.plane_id = planes.plane_id
       AND ((planes.category_class = 1 OR planes.category_class = 3))
       AND flights.pilot_id = 22
       AND flights.route IS NOT NULL
       AND planes.plane_id not in
          (SELECT plane_id
             FROM tags
            WHERE tag = 'Turbine')

# ¿ May 28, 2008 22:35

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

var1ety posted:

You gotta feel bad for MySQL users. Without having analytics you really have to bend over backwards to avoid self joins.

Agreed. I loves me some rank() over (partition by Column, order by OtherColumn).

# ¿ Jun 13, 2008 21:23

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

code:

SELECT `News`.`news_Title` FROM `News`
INNER JOIN `NewsCategories` AS Cat2 ON Cat2.`news_Id` = `News`.`news_Id`
INNER JOIN `NewsCategories` AS Cat81 ON Cat81.`news_Id` = `News`.`news_Id`
WHERE Cat81.`category_Id` = 81 AND Cat2.`category_Id` = 2

I forget the syntax for table aliases in MySQL, but something like this will do what you want.

# ¿ Jun 17, 2008 21:45

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

How would you go about importing a CSV file into the database with some custom logic and be able to report errors (ideally by sending an e-mail)? We're using SQL Server 2005.

Essentially each row in the CSV file should have an ID that matches a record in a database table. If there is a match, then there is an insert done in a separate table using the values from the CSV file. If there is no match, then we may need to report an error with the information from the CSV file. If there are rows in the DB table that are not in the CSV file, then we may need to do an update or report an error depending on the values in the table.

My supervisor suggested taking a look into Sql Server Integration Services, which I know absolutely nothing about. Any recommendations for where to learn about SSIS or if it can do what I need it to do? Any suggestions on other tools to look into.

This would be running from a windows service that would execute the script once a day automatically.

SSIS absolutely can do what you need it to. Here's the Books On Line page. I don't know how useful this page is, since when I started learning I had some existing packages to look at. But I guarantee that if you need to do more than just straight loading of data, SSIS is probably the way to go.

# ¿ Jun 18, 2008 21:46

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

No Safe Word posted:

chocojosh posted:
Regarding SSIS: my supervisor insists that I use SSIS for this little tool I'm working on. I think they're hinting that I test out a new technology for the company (I've shown interest before in learning new stuff).

Any books you'd recommend? I've been given permission from the CTO to go pick up a book and have it expensed by the company.

I've been working with SSIS for about a year now and the two Wrox books I used to get started: Professional SQL Server 2005 Integration Services and the Expert counterpart were both pretty good.

Also, reading through Jamie Thomson's blog entries are good too: http://blogs.conchango.com/jamiethomson/

I can too can vouch for both the blog and the first book (I have not read the Expert book).

# ¿ Jun 20, 2008 22:47

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Aredna posted:

You should also look at the difference between UNION ALL and UNION as they will give you different result sets.

UNION ALL will return every row from both queries.

UNION will merge the results of the queries and if there are any duplicated rows between the unioned queries they will be printed only once.

Just for further clarification, this means that, unless you specifically know that you want to discard duplicates you should use UNION ALL, since UNION forces the RDBMS to check for duplicates, even if there aren't any.

# ¿ Jun 26, 2008 16:41

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

I may not be able to use an OLE DB command for what I need. This is what I have:

Right now in my dataflow I'm joining a CSV file and a database table (to import daily information that we get from an external source).

What I'm doing now is that if there are values that exist in one source and not the other (obtained from a FULL-OUTER JOIN and checking for NULL), I want to send an e-mail for each value (record/row) that is not in both lists (there's a few other rules, but that's the gist of it).

You might be able to use an OLE DB Command for that, but I think your best bet would be to store that information in a different table and then run a SQL task (or whatever) afterwards. What you'd want to do either way is use a multi-cast to create a second copy of the data, then have one copy go to the destination tables as normal, while the other copy goes into a conditional split, which you use to discard the "good" rows. The "bad" rows then either go through your OLE DB Command, or they go to another table for use outside of the Data Flow. If you don't want your "bad" rows to go into the destination tables, then you don't need the multi-cast, just have the conditional split send the good rows on to the destination instead of discarding them.

# ¿ Jul 1, 2008 21:38

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

chocojosh posted:

What is a NULL outer join? I thought it would be a "LEFT OUTER JOIN" (you could use Right outer join but in practice I've only seen left).

I do like the subquery answers -- a good trick would be to ask for TWO ways to do it

I think he means a LEFT OUTER JOIN with WHERE RightTable.key IS NULL

# ¿ Jul 16, 2008 15:22

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Mr. DNA posted:

I'm running an analysis on data being generated by a PLC. The database is formatted specially for some software that runs on site so it's a bit of a pain to get all the data I need. Here's the query I'm running:

ADJUSTEDORIGINATIONTIME is created by the DATEADD routine to compensate for timezone and DST. I want to filter results by this adjusted time but when I try to run this query there's an error saying "Invalid column name 'AdjustedOriginationTime'". I can copy the DATEADD portion to the filter section of the query and it runs as expected, but it's silly to do the same calculation three times.

It seems the solution should be very simple, but I'm quite new to this. Help would be much appreciated.

What RDBMS? Looks like either MSSQL or MySQL, but I don't know which.

Either way, the short answer is "it may be silly, but that's pretty much the way to do it." The long answer is "stick it in a view or (RDBMS depending) a CTE or subquery."

# ¿ Jul 21, 2008 21:43

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

kirvett posted:

I'm having a pretty newbie problem with a search module I'm writing. The schema is that there are sessions in one table, with multiple files accessed per each session that are stored in another table. I'm trying to get sessions that do not have a certain file accessed, and this is what I'm doing:
code:
SELECT * FROM session_records s
LEFT JOIN file_records f
ON f.ipid = s.id
WHERE f.file NOT LIKE '%pdf%'
This just flat out does not work at all, returning a lot of sessions that still accessed PDFs. The opposite operation (looking for sessions that did access PDFs with LIKE) works fine. What am I doing wrong?

I think this is more what you are looking for:

code:

SELECT * FROM session_records
WHERE s.id NOT in (SELECT f.ipid FROM file_records f
WHERE f.file LIKE '%pdf%')

# ¿ Jul 31, 2008 23:41

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Random Child posted:

Real quick question, I'm using MS reporting services to make a front-end to my database. I need to filter results based on Windows Authentication, specifically the user's network alias. I'm told this information is a parameter that is already in Reporting services, but I have no idea where it is or what it is called.

Can anyone help me out?

User!UserID

# ¿ Aug 6, 2008 23:32

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Triple Tech posted:

Let's say I have a table called goons and each row in the table represents one goon with typical goon attributes like user_id, last_login, etc.

Let's say each goon can be categorized into only one goon_type, like where they hang out most often (TCC, GWS, TFR, etc). In the OOP sense, think subclasses.

Now, what if each subclass has attributes exclusive to that subclass? favorite_weapon, favorite_drug, favorite_food, etc.

How do you model this? Do you put all of those fields in the goons table and NULL them out where they don't apply? Or do you keep the goons table generic and make tables to join off to like goons_tfr, goons_gws, etc that consist only of the goon pk plus the exclusive attributes?

I think the main reason no one answered you in the sticky thread is because this is one of those questions that doesn't have one single answer (unless you're just asking people how they personally do it). The problem domain is called Object-Relational Mapping, and you've already laid out the two main means of doing so (IIRC). But the "best way" is definitely open for debate.

# ¿ Sep 18, 2008 17:38

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Mo Hawk posted:

Is it possible to sort/select by a value given by a function?

I've got a table with customers (students) and their Dutch bank account numbers, which can be validated with this function:
code:
CREATE FUNCTION fnElfProef (accountNr INT)
    RETURNS BOOLEAN
BEGIN
    DECLARE i, totaal INT DEFAULT 0;
    WHILE i < 9 DO
        SET i = i+1;
        SET totaal = totaal+(i*(accountNr%10));
        SET accountNr = FLOOR(accountNr/10);
    END WHILE;
    RETURN (totaal%11) = 0;
END
Now I would like to only select the customers which have invalid bank accounts, basically like so:
code:
SELECT * from `students` WHERE ( fnElfProef(`vDutchBankAccount`) = 1 )
Is it possible, and if not, what would you recommend as a work-around?
I could load the whole table into PHP arrays first, and filter afterwards, but that is very slow and inconvenient...

I don't guarantee my syntax, but

code:

SELECT * FROM (
SELECT *,
       fnElfProef(`vDutchBankAccount`) as `validAccounts`
  from `students`) AS `studentAccounts`
 WHERE ( `validAccounts` = 1 )

is probably the way to go.

# ¿ Sep 22, 2008 20:16

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Xae posted:

I need a way to export huge(20+gb) data sets from Oracle as fast as possible. However there are some problems:

1) It needs to be by query, not by Table.
2) It must be to my local machine. The network admins have refused to give me a couple dozen gigs on the network drives to dump these.
3) Variable formatting. I have to pull in the format the requester wants.
4) Preferably some way to break on file size or records retrieved. I need to be able to fit these on a DVD or CD.
5) I can't actually run code on the system, so no PL/SQL.

This is probably impossible, but it is the situation I find my self in. I'll charitably describe the situation as a "clusterfuck", but it is the assignment I have.

Right now I just run queries through SQL*PLUS and spool them, but this is stupidly slow and I generally dislike spending 20-30 hours on a weekend baby sitting my laptop at work. I have looked at SQLDeveloper, but it only seems to support export by table, not be query.

Any ideas?

It seems to me that if you are retrieving specific queries with specific formatting, you aren't "dumping data" you are generating reports. So maybe look into some sort of reporting tool?

# ¿ Oct 6, 2008 17:27

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

var1ety posted:

Sergeant Hobo posted:
Kind of a generic question, but what's better: joins or sub-queries?

I think sub-queries, because I feel like if you were partying with one then it would get belligerently drunk and make out with someone's girlfriend while her boyfriend was on a smoke break. So I guess it really just depends on how slutty your girlfriend is.

Translation: it depends on a lot of different things, so the only way to know is to test, test, test. But depending on your RDBMS, it may come up with the same execution plan either way.

# ¿ Oct 14, 2008 22:18

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

crazyfish posted:

Is there a way to create an index of unique, variable length numeric ranges?

For example, I would like my tables to be able to treat (1, 1000) and (1001, 1500) as two separate keys and be able to quickly determine if a new incoming range falls into one that is already in the DB. In an ideal world, the range would be represented as a composite index of two table columns (start, end).

I'm not up poo poo creek if there isn't an easy way to do this because the size of this table is never expected to be more than 20 rows so manually pulling all of the data in this table to do this check in a manual fashion won't be costly. It won't be regularly queried in this manner and inserts only happen during specific failure scenarios (which is when this check would need to happen).

This may depend upon your RDMBS, but in T-SQL at least I was able to do this by creating a Check constraint.
I just tested it by creating a function which took in two numbers, start and end, and returned the number of rows in the existing table where the range overlapped that described by start and end. Then I put a check on the table which said CheckTable(start,end) = 0

# ¿ Oct 14, 2008 22:40

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

trance monkey posted:

Question on Multiple Joining:

First off, to do many to many joins, create a join table. So instead of having a TopTen table with ten song id columns, you have a Person table, a Song table, and a TopTen table that looks like
PersonID,
SongID,
SongRank

Now, if you are unable to change the way your tables are laid out, then you want to do one of the following

code:

SELECT TopTen.NameOfPerson,
       TopTen.FirstPickID,
       S1.SongName FirstSongName,
       TopTen.SecondPickID,
       S1.SongName SecondSongName
       --etc etc etc.
FROM TopTen LEFT JOIN
     SongNames S1 ON TopTen.FirstPickID = S1.ID LEFT JOIN
     SongNames S2 ON TopTen.SecondPickID = S2.ID LEFT JOIN
     SongNames S3 ON TopTen.ThirdPickID = S3.ID--Continue On in this way

code:

SELECT TopTen.NameOfPerson,
       SongNames.ID SongID,
       CASE SongNames.ID
            WHEN TopTen.FirstPickID THEN 'FirstSong'
            WHEN TopTen.SecondPickID THEN 'SecondSong'
            WHEN TopTen.ThirdPickID THEN 'ThirdSong'
            --etc.
       END SongOrder
       SongNames.SongName
FROM TopTen LEFT JOIN 
     SongNames ON TopTen.FirstPickID = SongNames.ID
                  OR TopTen.SecondPickID = SongNames.ID
                  OR TopTen.ThirdPickID = SongNames.ID
                  --etc.

depending on which looks the way you want.

# ¿ Oct 16, 2008 23:20

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

thefncrow posted:

I'm working in a SQL Server 2005 environment here.

I have a large data table with a 3 part primary key. The 3 part primary key is something like CustomerNumber, BillID, LineItem. Changes to this table are difficult at best, because it's large and partitioned.

I've got another table, now, where I'm working with the line items for a given (CustomerNumber, BillID) combination. Basically, this table is an identity int column, CustomerNumber, BillID, and some XML data about those line items and some associated data for each. What I want is a constraint on this table that, for any inserted row, the (CustomerNumber, BillID) combination matches a record in my larger table.

I can't do it as a foreign key, because of the third part of the key(and the PK without the third part is not unique to each record). How can I enforce this? It seems like I'm missing something obvious, but I can't see it.

Two sets of questions:
Why is there not a "bills" table which has a key of (CustomerNumber, BillId) instead of just a "bill line items" table? If there is such a table, then why aren't you using that for the foreign key constraint?

If your new table has XML data about the line items, why is LineItem not part of the key? If the XML data is about the bills and not the bill line items, see the above questions.

If you don't have any real ability to change the schema then neither of those questions is particularly helpful, but it seems like it points out why you're having trouble.

As for your problem, I just recently learned about check constraints, and they're my new best friend. To make one that does what you want, create a UDF that returns true or false depending on whether a given (CustomerNumber, BillId) is in the line item table, and then create a check constraint on the table which requires that the function return true for the (CustomerNumber, BillId) of the record in question.

# ¿ Nov 14, 2008 18:42

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

pikes posted:

This seems so simple but I don't know how I'd do it without hitting the database twice. Any ideas?

The other way to write it would be

code:

select users.user
from users left outer join
     shipments on shipments.user = users.user and shipment = 'Outbound'
where shipments.user is null

You'll have to test to see which is better, assuming they don't perform the same.

# ¿ Nov 14, 2008 19:37

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Vince McMahon posted:

Yeah sorry I didn't explain that very well. By relational DB I kind of meant what you described - basically a row per keyword per image. It concerns me that the rows in this database will be in the millions - will that slow things down or can mysql handle it?

even mysql will be able to handle millions of rows better than it can handle comma delimited fields.

# ¿ Nov 17, 2008 22:27

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Victor posted:

Edit: moreover², CHECK constraints cannot contain subqueries in SQL2005; try this (from the stackoverflow thread):
code:
CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  CHECK (student_id NOT IN (SELECT student_id FROM musicians 
                            UNION SELECT student_id FROM slackers 
                            UNION ...)) 
);
I get this error:
SQL2005 posted:
Msg 1046, Level 15, State 1, Line 4
Subqueries are not allowed in this context. Only scalar expressions are allowed.

Subqueries may not be allowed in check constraints, but UDFs are.

code:

CREATE FUNCTION [dbo].[CheckAthletes] 
(
	-- Add the parameters for the function here
	@student_id int
)
RETURNS bit
AS
BEGIN
	DECLARE @validAthlete tinyint

	-- Add the T-SQL statements to compute the return value here
	SELECT @validAthlete = (SELECT CASE WHEN student_id NOT IN (SELECT student_id FROM musicians 
                                UNION SELECT student_id FROM slackers 
                                UNION ...) THEN 1 ELSE 0 END)

	-- Return the result of the function
	RETURN @validAthlete 

END

code:

CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  CHECK ([dbo].CheckAthletes(student_id) = 1) 
);

# ¿ Dec 2, 2008 19:09

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Syano posted:

Man I have one that is killing me. I have a table constructed thusly:

CompanyID | EmployeeID | Payrate | ChangeDate

Each row in the table records when the payrate for the employee changed and what it changed to and our payroll software uses the latest record for the current pay rate.

My boss wants me to construct a report that shows him current rates plus what the rate was previously for each employee. Basically so he can see what impact raises are having on the company. So essentially I would need for each companyID and employeeID to see the latest payrate plus the one just below that as relates to dates. What is killing me is figuring out how to return a result set that shows both the current rate and the rate just before that on the same row.

I have been farting around with top statements and subqueries for the last 4 hours and I am about ready to pull my eyes out. Any ideas?

Assuming MS-SQL (given your mention of TOP), if you have 2005 or better you could do:

code:

select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate
  from (
select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate,
       row_number() over (partition by CompanyID, EmployeeID order by ChangeDate desc()) pay_row
  from pay_table) ranked_pay
where pay_row in (1,2)

The sub-query ranks all of the pay rate changes for each CompanyID and EmployeeID by date from most recent to oldest. Then the outer query returns the two most recent for each.

EDIT: Oh, didn't see that you want them on the same row. Try using PIVOT. Give me a second to do something with that.

Jethro fucked around with this message at 18:36 on Dec 3, 2008

# ¿ Dec 3, 2008 18:33

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

code:

select CompanyID,
       EmployeeID,
       [1] [CurrentPayrate],
       [2] [PreviousPayrate]
  from (
select CompanyID,
       EmployeeID,
       Payrate,
       ChangeDate,
       row_number() over (partition by CompanyID, EmployeeID order by ChangeDate desc()) pay_row
  from pay_table) as ranked_pay
pivot
(
sum(Payrate) FOR pay_row IN ([1],[2])
) as pvt

I think that would work.

# ¿ Dec 3, 2008 18:42

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Gary the Llama posted:

stuff

Assuming SQL2005, change

code:

SELECT tbl_SalesTool_Associates.AssociateName, tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime,

Abs(CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100 - 100, 0) AS INT)) AS RemainingPercent,

CASE tbl_SalesTool_DailyOptions.Title
WHEN 'Travel - Rep' THEN tbl_SalesTool_RepOffices.Title
ELSE tbl_SalesTool_DailyOptions.Title
END as DailyOption,

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
ORDER BY AssociateName

code:

SELECT AssociateName,
       SchedulingOption,
       PercentTime,

100 - sum(PercentTime) over (partition by AssociateName) AS RemainingPercent,

       DailyOption
FROM (
SELECT tbl_SalesTool_Associates.AssociateName,
       tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime,

CASE tbl_SalesTool_DailyOptions.Title
WHEN 'Travel - Rep' THEN tbl_SalesTool_RepOffices.Title
ELSE tbl_SalesTool_DailyOptions.Title
END as DailyOption

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title) sales_stats
ORDER BY AssociateName

This won't work on SQL 2000.

# ¿ Dec 8, 2008 23:09

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Gary the Llama posted:

That did the trick. Never seen that before, that's pretty awesome. Now to look it up so I can understand what it does. Thanks a million!

http://msdn.microsoft.com/en-us/library/ms189461.aspx

# ¿ Dec 8, 2008 23:55

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

A simple union could probably do the trick.

code:

SELECT tbl_SalesTool_Associates.AssociateName,
       tbl_SalesTool.SchedulingOption,
CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool.SchedulingOption,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
UNION ALL
SELECT tbl_SalesTool_Associates.AssociateName,
       0 SchedulingOption,
100 - CAST(ROUND((CAST(COUNT(tbl_SalesTool.Id) AS DECIMAL(8,4)) / @totalNumDays) * 100, 0) AS INT) AS PercentTime

FROM dbo.tbl_SalesTool_DailyOptions INNER JOIN
dbo.tbl_SalesTool INNER JOIN
dbo.tbl_SalesTool_Associates ON dbo.tbl_SalesTool.UserName = dbo.tbl_SalesTool_Associates.UserName ON 
tbl_SalesTool_DailyOptions.CategoryId = dbo.tbl_SalesTool_Associates.Category AND 
tbl_SalesTool_DailyOptions.DailyOptionsId = dbo.tbl_SalesTool.SchedulingOption LEFT OUTER JOIN
dbo.tbl_SalesTool_Region INNER JOIN
dbo.tbl_SalesTool_RepOffices ON dbo.tbl_SalesTool_Region.RegionId = dbo.tbl_SalesTool_RepOffices.RegionId ON 
dbo.tbl_SalesTool.RepOffice = dbo.tbl_SalesTool_RepOffices.RepOfficesId
WHERE (tbl_SalesTool.ScheduleDate BETWEEN @startDate AND @endDate)
GROUP BY 
tbl_SalesTool_Associates.AssociateName,
tbl_SalesTool_RepOffices.Title,
tbl_SalesTool_Associates.Category,
tbl_SalesTool_DailyOptions.Title
ORDER BY AssociateName

EDIT: That problem is due to an extra something in the GROUP BY part of the second half of the query. I don't have any idea about what your data means, so I don't know what it is, but I'd guess it's the tbl_SalesTool_DailyOptions.Title, so try taking that out of the second GROUP BY.

Jethro fucked around with this message at 22:50 on Dec 9, 2008

# ¿ Dec 9, 2008 22:28

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

tayl0r posted:

The "left join" "inner join" "outer join" syntax always freaks me out. I much prefer using (+) for when I want to use an outer join, and leaving out any of the join keywords. Maybe that's an Oracle thing.

It is.

# ¿ Dec 10, 2008 22:17

Adbot: ADBOT LOVES YOU

# ¿ Apr 29, 2024 02:53

Jethro: Jun 1, 2000; I was raised on the dairy, Bitch!

Triple Tech posted:

What is type 2? Kind of difficult to Google for, I'm not sure what I'm looking at.

SCD Type I is discard history, SCD Type II is retain history, SCD Type III is retain last history as attribute.

# ¿ Dec 15, 2008 23:11

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

«‹›6 »