|
Ok for some reason i cant do this in Foxpro I need to select the data from a table that doesnt exists in another for example, i want to filter the users that came on January and only in January even if they did come back later (the users that came back later should be filtered) Ive tried with EXISTS and ANY but i cant do this :{{{{
|
# ? Sep 5, 2008 19:26 |
|
|
# ? Jun 8, 2024 08:40 |
|
Stupid question thread, perfect, I'm an idiot so this will work. I have an access database I created. The whole intent was to get it hosted as a webpage for a small group of users to access. I am familiar with how the logic of databases work but overall am a novice in this field. This is my first attempt to create a database and think I did a 'modest' job but definitely know I am still only utilizing 2% of the potential for it. That being said, I'm trying to find the simplest way to get this accomplished. At this point glitz and glamor is a distant second to function. I picked up a hosting plan from 1and1.com under the impression that I could simply 'publish' the database and be on my way. As always with all things tech it doesn't seem to be working out that way. Their recommendations on what to do can be found here (http://faq.1and1.com/scripting_languages_supported/access_database/ ) and from the looks of it I would need to create several .html pages to run the queries I want. Is my understanding correct on this? Not knowing SQL this is starting to look a bit daunting as I wanted to 'publish' and have my login screen I created pop up and function mostly like it does locally. Please spare a newb some help and point me in the right direction for the most simple method to accomplish my task. Did I pick the wrong provider? Is there another piece of software (gasp Frontpage?) that can help tie this all together? Thanks!
|
# ? Sep 10, 2008 14:29 |
|
I don't really know what the person above me is trying to do, but I'll say what I can about it in hopes of making my question respectable... It sounds like you are going to need to do some research on web scripting. http://www.w3schools.com/PHP/php_db_odbc.asp might be a good place to start. Are you trying to get one of your Access forms to become a web form? If so, you will need to reprogram the form using a web scripting language (I think). Anyway, my question is hopefully an easy one to answer. I need to transfer data from an Access database into an MSSQL one. I've gotten the Access tables into my MSSQL database, but now I need to pick data from the imported tables and move it into an existing table. For instance: tbl_old has columns (id, name, commission, new_id) tbl_new has columns (new_id, new_name, commission) new_id is a column that matches the rows in tbl_old with the associated rows in tbl_new. This might be still confusing, but here's the query I'm trying to run to accomplish this: code:
Edit: Moments after posting this I figured out the solution (of course). For those curious, the proper SQL is: code:
moww fucked around with this message at 21:55 on Sep 11, 2008 |
# ? Sep 10, 2008 17:23 |
|
moww posted:I don't really know what the person above me is trying to do, but I'll say what I can about it in hopes of making my question respectable... Thanks for the reply. I suppose I am trying to recreate what I believed to be a very turnkey solution that many companies do. Have a database created, then publish it on their IIS server for the employees to access. From my understand, it is as easy as clicking 'Publish' and from the research I have found I would need to code each query as it's own web page, which is not what I had originally thought would have been the case. To that effect, I am seeing many more references to having Frontpage (now expression web?) do this all for you so I am looking down that route. I would love to learn how to program properly to get this up and running, but currently lack the skill set so was hoping to replicate the 'ease' of the corporate environment on the internet.
|
# ? Sep 10, 2008 17:27 |
|
TraderStav posted:Thanks for the reply. I suppose I am trying to recreate what I believed to be a very turnkey solution that many companies do. Have a database created, then publish it on their IIS server for the employees to access. From my understand, it is as easy as clicking 'Publish' and from the research I have found I would need to code each query as it's own web page, which is not what I had originally thought would have been the case. Somebody could come in here and give you a super easy solution, but as far as I know reprogramming your forms is the only way. If you already have your tables in place and you are comfortable writing your own SQL, learning how to program your forms with a simple scripting language like PHP should be relatively easy. If you were able to dedicate a week to learning PHP, I believe you would become qualified to do what I think you're trying to do.
|
# ? Sep 10, 2008 17:36 |
|
moww posted:Somebody could come in here and give you a super easy solution, but as far as I know reprogramming your forms is the only way. If you already have your tables in place and you are comfortable writing your own SQL, learning how to program your forms with a simple scripting language like PHP should be relatively easy. If you were able to dedicate a week to learning PHP, I believe you would become qualified to do what I think you're trying to do. I am not comfortable writing my own SQL. However, access does generate it's own (efficient or not, it seems to work!), perhaps I could copy/paste that into an acceptable PHP format. I'll look into this as well. Thanks a lot for your responses.
|
# ? Sep 10, 2008 17:41 |
|
I want to build a query that will use three tables, and I know the speed of each table individually, so I want to maximize the speed with the joins. That being said, does the order of events in the query have an effect on which one is completed first? So, lets say table A is super fast, B is not so fast, and C is dead slow. I want A to execute first and knock out as many rows as possible before it tries to join with B, and then B to C. As an example: select * from A a, B b, C c where a.this > # and a.date > curdate() and a.element=b.element and b.id=c.id Would this execute in the order I want it to?
|
# ? Sep 10, 2008 18:29 |
|
LightI3ulb posted:I want to build a query that will use three tables, and I know the speed of each table individually, so I want to maximize the speed with the joins. That being said, does the order of events in the query have an effect on which one is completed first? It depends on your database vendor and version. Many modern databases leverage index and table statistics to generate their own query plan for answering your question in the way they believe will be the most efficient, such that the order in which you specify your tables and predicates does not matter.
|
# ? Sep 10, 2008 18:51 |
|
TraderStav posted:I am not comfortable writing my own SQL. However, access does generate it's own (efficient or not, it seems to work!), perhaps I could copy/paste that into an acceptable PHP format. I'll look into this as well. Thanks a lot for your responses. SQL is actually pretty simple. Also, Access is really good at taking what should be simple easy to read SQL and making it complex, unreadable, and uneditable. I suggest reading up on SQL and then get an understanding of third normal form. I think that with about 8 hours of messing around creating tables and writing SQL statements you will surprise yourself at what you can accomplish.
|
# ? Sep 11, 2008 13:51 |
|
Begby posted:SQL is actually pretty simple. Also, Access is really good at taking what should be simple easy to read SQL and making it complex, unreadable, and uneditable. Quoted for truth. The sooner you can start writing free-hand SQL the better off you will be... People who get too reliant on the Access Query builder sometimes have a hard time transitioning over. Although, I have to say, at first I would avoid table manipulation with SQL, and just use the GUI to make, drop and modify your tables. Just concentrate on getting the Selects and joins down. I like "Sams Teach Yourself SQL in 10 Minutes". Its a fairly quick read, and I still keep it around for reference.
|
# ? Sep 11, 2008 15:16 |
|
moww posted:Edit: Moments after posting this I figured out the solution (of course). For those curious, the proper SQL is: I'm surprised that works, I had no idea you could use a FROM clause in an update statement. Just for the record, you also should be able to write: code:
|
# ? Sep 11, 2008 18:40 |
|
I'll try to make it clear as to what I want to do here. I want to get a result set of all the stores in our system, with their total sales for each month for 2008. This is what I have so farcode:
code:
code:
|
# ? Sep 11, 2008 22:26 |
|
The immediate problem is that the SITE table in your subqueries is a different copy of the table than the one in your outer query. Refer to SITE_1 instead. But, that still might not work properly... I don't understand your data model from what you've shown. Why the DISTINCT? What's the relationship between a store and a site, and why do invoices have a site id but not a store number? edit: Maybe this will help... If you wanted to select sites, and each site's sales, you could do: code:
code:
MoNsTeR fucked around with this message at 00:13 on Sep 12, 2008 |
# ? Sep 12, 2008 00:00 |
|
MoNsTeR posted:The immediate problem is that the SITE table in your subqueries is a different copy of the table than the one in your outer query. Refer to SITE_1 instead. But, that still might not work properly... I don't understand your data model from what you've shown. Why the DISTINCT? What's the relationship between a store and a site, and why do invoices have a site id but not a store number? I'll give this a try in the morning, but to answer your questions: The DISTINCT probably isn't necessary, probably typed it in without thinking. There are 2 tables of interest, SITE and INVOICE. The SITE table holds the store information, ie. Store Number, Address, State, Zip, etc. The INVOICE table joins on the SiteID attaching that invoice to that store/site. Sorry about the confusion, I neither created nor named the SITE table.
|
# ? Sep 12, 2008 01:08 |
|
golgo13sf, what RDBMS are you using?
|
# ? Sep 12, 2008 02:04 |
|
Victor posted:golgo13sf, what RDBMS are you using? MSSQL. Also, that solution above worked aces. Thanks.
|
# ? Sep 12, 2008 02:49 |
|
If you have SQL2005, this will run faster and be much easier to maintain:code:
|
# ? Sep 12, 2008 04:20 |
|
Victor posted:If you have SQL2005, this will run faster and be much easier to maintain: Son of a bitch, this kicks rear end.
|
# ? Sep 12, 2008 14:46 |
|
I wish I had access to pivot, but we're still on Oracle 10gR1 and it's only available in R2
|
# ? Sep 12, 2008 16:07 |
|
I need some help querying some counts from Oracle. I need to count the records for about 80 tables, but I want to generate that into one query if possible. I don't want the results as one big count, but each table's count preserved. Is this possible? Example of this with one table: code:
code:
code:
We generally use Forest&Trees reporting software to interface with the Oracle tables.
|
# ? Sep 12, 2008 17:05 |
|
Uziel posted:I need some help querying some counts from Oracle. There's no intrinsic way to do this in Oracle until 11g (which adds pivot and unpivot). If you're using an older version you'll need to create a query with 80 columns, or create a filter outside of Oracle that will pivot your data set.
|
# ? Sep 12, 2008 17:26 |
|
The server is running Oracle 10. Do you have an example of the query if it were just two columns instead of 80?
|
# ? Sep 12, 2008 17:29 |
|
Uziel posted:The server is running Oracle 10. The simplest way is like this: code:
code:
|
# ? Sep 12, 2008 17:40 |
|
I tried:code:
code:
|
# ? Sep 12, 2008 17:46 |
|
Have you tried the following?code:
|
# ? Sep 12, 2008 18:28 |
|
Victor posted:Have you tried the following? Dual is a dummy table in Oracle with exactly one row. You must select from a table in Oracle, for better or for worse.
|
# ? Sep 12, 2008 18:36 |
|
Ahah, one learns something new every day. I wonder why Oracle choked on your version...
|
# ? Sep 12, 2008 19:59 |
|
Hey, quick question - How is postgres on timestamps? There's a possibility my company is going to switch to postgres from mysql (which has great automatic timestamp generation) and I'd like to know whether I have to write it manually or set up a trigger and whatnot. edit: I found this and I'll trust that it works quote:When you want a default timestamp to be the current time, here's how you'd do it: Clobbersaurus fucked around with this message at 18:04 on Sep 15, 2008 |
# ? Sep 15, 2008 17:54 |
|
I'm having a hell of a time with a particular query. There is a USERNAME and SYSTEMDATE field and I need to develop a query that filters out records with a USERNAME/SYSTEMDATE pair that falls within 30 seconds of another USERNAME/SYSTEMDATE pair (with identical USERNAME). Basically, if a user connects again within the same 30 second window of their first connect, we want to omit that record in our analysis. I don't even know where to begin doing this in SQL (Oracle). I wrote up a VB app, but it's projected to take more than 9 hours to run through 300k records (and this is a very streamlined version of the app). Plus, since I can't create or update tables, I'll have to use that VB app to output some 300k records into an .XLS or .CSV file and then run the actual data analysis on this file (rather than querying the database directly). I imagine this'll just add even more overhead to the entire process. So, long story short, is there any way I can do this type of query within SQL?
|
# ? Sep 17, 2008 19:33 |
|
dangerous.hotdog posted:So, long story short, is there any way I can do this type of query within SQL? Yep, you sure can. In general, its best to do all of the heavy lifting with SQL, but some things are better done with logic inside your App... When you start out, its not always clear which is which. Getting the latest combination within a time window can be done with GROUP BY and sub-Queries. The second part of your statement bothers me, though... why go to XLS or CSV? If your truly need to add NEW data to your temp table, then you might have to do that, but otherwise you can synthesize data into a View, and search from that. If you really do need to build a temp table that big, I would use Access instead of Excel or text files. You don't even have to use the Access GUI or object model: just have a blank MDB file somewhere, and you can MAKE your table(s), INSERT your data, and then SELECT back what you need, all with SQL through ADO. (NOTE: keep the MDB file local to your PC, or it could take a LOOONG time) Squashy Nipples fucked around with this message at 19:54 on Sep 17, 2008 |
# ? Sep 17, 2008 19:51 |
|
SQL nub here. I have a varchar field in my table that the user can have in many different formats, such as "abc def", "ABD-DEF", "ABC; DEF", "ABC - DEF", etc etc. So far I've used PHP to do all the "cleaning up" of this data to get into a uniform format to work with, but now I realize the best way is probably to do it with a MySQL function. Something like "SELECT DISTINCT clean_up(`column`) FROM ..." instead of a bunch of PHP loops and crap following it. Doing a google search for "user defined functions mysql" tells me that there are about 3 or 4 ways to create functions, and they all, as far as I can tell, require compiling C++ code. Is there an easy way to define a simple function without going balls out? All I need is a few REPLACE()'s, and a SUBSTRING() or two...
|
# ? Sep 18, 2008 06:38 |
|
nbv4 posted:Doing a google search for "user defined functions mysql" tells me that there are about 3 or 4 ways to create functions, and they all, as far as I can tell, require compiling C++ code. Is there an easy way to define a simple function without going balls out? All I need is a few REPLACE()'s, and a SUBSTRING() or two... http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html
|
# ? Sep 18, 2008 10:31 |
|
dangerous.hotdog posted:I'm having a hell of a time with a particular query. There is a USERNAME and SYSTEMDATE field and I need to develop a query that filters out records with a USERNAME/SYSTEMDATE pair that falls within 30 seconds of another USERNAME/SYSTEMDATE pair (with identical USERNAME). Basically, if a user connects again within the same 30 second window of their first connect, we want to omit that record in our analysis. Assuming i've not missed something very obvious... Yes. If you are running on a fairly decent box it would take 5-10 minutes tops with a couple of procedures/functions nested. What if they try again after thirty seconds, you want that kept? Here is how I would do it... Assuming can create a temp table. code:
|
# ? Sep 18, 2008 14:30 |
|
I'm creating a views table that tracks which items on my site were viewed/clicked etc. and what time. The table has become far too large to efficiently query and results, so I'm creating a view count column in the item table to do efficient sorts by number of views. I guess my real question is about Triggers. Will it be viable/good practice to create a MySQL Trigger that will update the item view_count column on every insert into the views table? Or is there a better way to accomplish this?
|
# ? Sep 18, 2008 16:01 |
|
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?
|
# ? Sep 18, 2008 17:19 |
|
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.
|
# ? Sep 18, 2008 17:38 |
|
Jethro posted:But the "best way" is definitely open for debate. Anyone who disagrees with me is a momma's boy
|
# ? Sep 18, 2008 18:07 |
|
Triple Tech posted: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? Without a shadow of doubt, this is the way to do it.
|
# ? Sep 18, 2008 18:19 |
|
dangerous.hotdog posted:I'm having a hell of a time with a particular query. There is a USERNAME and SYSTEMDATE field and I need to develop a query that filters out records with a USERNAME/SYSTEMDATE pair that falls within 30 seconds of another USERNAME/SYSTEMDATE pair (with identical USERNAME). Basically, if a user connects again within the same 30 second window of their first connect, we want to omit that record in our analysis. Assuming that when there are two records within 30 seconds you want to keep the first one... code:
code:
code:
|
# ? Sep 18, 2008 20:18 |
|
|
# ? Jun 8, 2024 08:40 |
|
PostgreSQL 8.2.6 (Greenplum 3.1.1.3 build 1) Greenplum is the commercial version of bizgres (https://www.bizgres.org). They use distributed processing and storage and are designed for data warehousing. I've got a problem with a function that can cause the database to crash and require that some segments are rebuilt. I've turned the exact steps to reproduce this over to our admins, but I didn't know if anyone had seen something similar to this before. Here's the function: code:
code:
code:
code:
code:
|
# ? Sep 19, 2008 14:47 |