|
code:
|
# ? Feb 15, 2008 16:31 |
|
|
# ? May 13, 2024 08:12 |
|
Hey. A very stupid question let's say I need to construct a query that goes something like this. 1. There are many grassy knolls 2. Each knoll can have a bunch of unicorns or no unicorns at all Problem. I need to select all grassy knolls where there are no dead unicorns.
|
# ? Feb 18, 2008 22:00 |
|
GroceryBagHead posted:Hey. A very stupid question It should look like this: code:
Alex007 fucked around with this message at 22:24 on Feb 18, 2008 |
# ? Feb 18, 2008 22:14 |
|
Alex007 posted:It should look like this: I believe the AND should be an OR. I'd do something like this: code:
|
# ? Feb 18, 2008 22:17 |
|
Thanks Alex, you're the best. I caught the OR thing as well
|
# ? Feb 18, 2008 22:37 |
|
GroceryBagHead posted:Thanks Alex, you're the best. I caught the OR thing as well Hey, no problem IsaacNewton's solution is great too, depending on the number or grassy knolls and/or dead unicorns, his solution may be faster (if there are fewer grassy knoll with dead unicorns in them).
|
# ? Feb 18, 2008 22:47 |
|
Even faster now, with a LEFT anti-JOIN the exclude knolls with dead unicorns in them:code:
|
# ? Feb 18, 2008 22:50 |
|
Victor posted:
I'm sorry, I don't think I was clear enough. I need to return the top store of each month. Here is an example output: code:
|
# ? Feb 19, 2008 16:45 |
|
code:
|
# ? Feb 19, 2008 16:49 |
|
GroceryBagHead posted:1. There are many grassy knolls code:
|
# ? Feb 19, 2008 16:50 |
|
Victor posted:
You're gonna have to throw a DISTINCT there, otherwise hello grassy knolls multiplication !
|
# ? Feb 19, 2008 16:54 |
|
I don't know how to say this, other than no. Anti-joins like that don't need DISTINCT.
|
# ? Feb 19, 2008 16:55 |
|
Victor posted:I don't know how to say this, other than no. Anti-joins like that don't need DISTINCT. You're right, I'm a dumbass for not thinking about it, sorry.
|
# ? Feb 19, 2008 18:24 |
|
That's actually not surprising -- I have seen very few people add a condition to a left join statement that has nothing to do with the join. It's a sad state of affairs, that SQL is so poorly taught...
|
# ? Feb 19, 2008 18:39 |
|
Victor posted:That's actually not surprising -- I have seen very few people add a condition to a left join statement that has nothing to do with the join. It's a sad state of affairs, that SQL is so poorly taught... 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.
|
# ? Feb 20, 2008 02:20 |
|
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.
|
# ? Feb 20, 2008 03:09 |
|
Here's an easy query that I just can't figure out: code:
|
# ? Feb 21, 2008 17:30 |
|
You're doing a GROUP BY and then selecting columns that aren't grouped by, without aggregating. That doesn't make sense. MySQL lets you do it because sometimes it works, and you're supposed to know when it won't, even though it might look like you're getting good data. Your code below is playing Implementation Roulette. This post gives a template for what you want to do: for every set of records, you want to choose the min/max by some criteria. You make that its own derived table, pull the min/max value(s) out, as well as whatever designs "set of records", and then join that back to the main table and get everything you need. Perhaps I should put this idea in tsunami...
|
# ? Feb 21, 2008 17:49 |
|
Thanks again, Victor. I guess I've always just misused GROUP BY as well to try and eliminate rows based on my WHERE clauses.
|
# ? Feb 21, 2008 18:04 |
|
If you need more help, do let me know -- I was just being lazy and/or giving you more opportunity to learn on your own. (If I help, you'll probably learn less, but it will definitely take less time, so it's a trade-off.) I'm beginning to think that SQL might be as misunderstood as Javascript.
|
# ? Feb 21, 2008 18:44 |
|
No, that was perfect thanks. I'm less of a hand-out and more of a hand-up person as well.
|
# ? Feb 21, 2008 20:06 |
|
I want to get a result set through a recursively selecting query. Table: relationships code:
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 |
|
Oracle has a CONNECT BY operator that lets you do this.quote:select c quote:124 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.
|
# ? Feb 21, 2008 21:39 |
|
var1ety posted:Oracle has a CONNECT BY operator that lets you do this. This works perfectly. Thanks!!
|
# ? Feb 21, 2008 21:47 |
|
var1ety posted:Oracle has a CONNECT BY operator that lets you do this.
|
# ? Feb 21, 2008 22:02 |
|
Anyone have experience with Unit Testing frameworks for (PL)SQL? I have looked at UTPLSQL and SQLUnit, but I figure there has to be more than just two out there. Both of them look to be overly complex for just PL/SQL testing, as they both seem to be hacks to get JUnit to test SQL.
|
# ? Feb 24, 2008 05:53 |
PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut? edit: Another question - This query only returns articles that have comments, how do I get it to return the ones that don't have any comments? Looking at the joins tutorial it looks like LEFT OUTER JOIN is what I want, but the query returns the same thing when I try it. code:
fletcher fucked around with this message at 01:29 on Feb 27, 2008 |
|
# ? Feb 26, 2008 22:05 |
|
fletcher posted:PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut? Ghetto Method: code:
Xae fucked around with this message at 02:02 on Feb 27, 2008 |
# ? Feb 27, 2008 01:59 |
Xae posted:Ghetto Method: I'm running MySQL 5. Whoops, I phrased my question a little wrong. I want it to return articles with comments AND articles without comments in the same query, but count the # of comments if it does have any. edit: specifying LEFT or RIGHT join gives me the same results
|
|
# ? Feb 27, 2008 02:03 |
|
fletcher posted:PHP/PDO question: If I have a table with an auto-incrementing id field, what is the fastest way to get the id of a new row I just throw in? Just do a SELECT? Or is there some shortcut? http://www.php.net/manual/en/function.PDO-lastInsertId.php Or SELECT LAST_INSERT_ID() if you want to do it in SQL (you said you were using MySQL 5). EDIT: Does this do what you want? It's a lot like your query above, but using an outer join, which you were right to think you needed to use. code:
brae fucked around with this message at 02:29 on Feb 27, 2008 |
# ? Feb 27, 2008 02:17 |
|
fletcher posted:I'm running MySQL 5. code:
If it doesn't look at your data, it may be that you don't have any, or some other issue exists.
|
# ? Feb 27, 2008 03:53 |
brae posted:http://www.php.net/manual/en/function.PDO-lastInsertId.php Awesome, this worked great, thanks! And thank you to everybody else who answered as well.
|
|
# ? Feb 27, 2008 09:10 |
|
So it's LAST_INSERT_ID() for MySQL 5 (what about 4?), SCOPE_IDENTITY() for SQL Server 2000+, what about Oracle, Postgre?
|
# ? Feb 27, 2008 14:48 |
|
Victor posted:So it's LAST_INSERT_ID() for MySQL 5 (what about 4?), SCOPE_IDENTITY() for SQL Server 2000+, what about Oracle, Postgre? Oracle uses RETURNING: code:
|
# ? Feb 27, 2008 15:00 |
|
I'm using Oracle to build a query that has me scratching my head. I simply cannot find a way to do the following in a single big query. I don't really know PL/SQL so I don't want to go that way, if possible. So here's my example. Let's say I have a table FOOBAR with data: code:
Basically, I have a query like that: code:
Any ideas? I don't know PL/SQL but maybe it would be possible to store the first results into an array or something and then loop over that...
|
# ? Feb 27, 2008 15:21 |
|
You should be able to use either IN() or an inline view. Like...code:
|
# ? Feb 27, 2008 15:31 |
|
yaoi prophet posted:
This does not work, I get a "ORA-00913: too many values" error. I'll try to make the second option work and let you know how that goes.
|
# ? Feb 27, 2008 15:55 |
|
Senso posted:This does not work, I get a "ORA-00913: too many values" error. I'll try to make the second option work and let you know how that goes. That's because I'm stupid. Take out the COUNT(*) from the select clause: code:
|
# ? Feb 27, 2008 16:00 |
|
^^^^ That works perfectly, thanks!
|
# ? Feb 27, 2008 16:31 |
|
|
# ? May 13, 2024 08:12 |
|
I need a custom Excel SQL query written for my job. The boss wants it, and I don't know a thing about SQL. If I wanted to pay a goon to write it for me, should I post it in here somewhere, or over in SA Mart? We run a MS SQL server. I have 2 tables that I need to pull data from. One is a table with all of our vendors, the other is a table that records the payments that are made to them by date. There is a common "Vendor Code" field in both tables. The boss wants a spreadsheet that lists all our active vendors, and how much we paid to them by week. The first report I gave to him, I just queried the payments by week, but that didn't work, because he wants all the vendors listed, whether a payment was made to them that week or not, so he can go line by line, look at all the vendors by week, and look at our buying trends. Since the first time I looked at SQL was the day he asked me for this, and since I can't figure out how to do this with one script, what I've done is made a bunch of different scripts, and then combined the data in excel. I queried our master vendor file, had it filter by active vendors and by a certain last payment date, to filter out all the old vendors, and put it in the first column. Then I have a script that queries the vendor payments table, and I have it filter between dates. I run it for every week and put the data in a separate excel table. I have an excel plugin called "Merge Tables Wizard" that I run on each week, and it takes the numbers, and merges them to the appropriate vendor. I have to run it for each week. That gives me something like this: With the totals for each week at the bottom. The problem with doing it by hand, besides being completely idiotic, is that new vendors are added all the time, so every Friday when I create the report I have to redo the main vendor list and re-merge all the data from the previous weeks, plus add the data from the current week. The query would also have to combine multiple payments to the same vendor during the same week, so that there is just the one total dollar amount. Aaaaaaanyway, should I just post this in SA Mart, and what's the going rate for something like this? If I got a quote, I could run it by the boss, or if it was reasonable enough, just pay it myself, because doing this by hand blows.
|
# ? Feb 29, 2008 01:09 |