Certain Records
Code:
insert into users (username, password, email) select user , ID, email from selex.empleados_selex
but i want to copy only the records where its status is "working"
something like:
insert into users (username, password, email) select user , ID, email from selex.empleados_selex WHERE status =´working`
View Complete Forum Thread with Replies
See Related Forum Messages: Follow the Links Below to View Complete Thread
Count Related Records, But Show Records With NO Related Records Also
cl_items ======== it_id (pk) it_ownerid it_name cl_offers ======== off_id (pk) off_itid (fk) -> to it_id off_whoid My query needs to output ALL of the records in cl_items AND still show how many offers are on each item (from cl_offers) I can't get what I want through the GROUP BY because I want to show the records in cl_items which DO NOT have any offers on them yet also. Is there any way to do this with mysql only?
All Records From Table A - All Records From Table B - Join Alike Records
I am by no means a SQl Jedi as will be apparent by my question, but I can usually figure out a select statement on my own. I have one today though that really has me stumped. I am working in MySQlL 5. In My first select statement I get all my records from Table B SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Inner Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` In my second statement I get my records that match in this case phase 401 in Table B and all my Table A records for phase 401. SELECT `table_A`.`ITEM`, `table_A`.`DECSCRIPTION`, `table_A`.`UM`, `table_A`.`PHASE`, `table_B`.`Qty`, `table_B`.`Calc` as calculated FROM `table_A` Left Outer Join `table_B` ON `req_itemlist`.`ITEM` = `table_B`.`ItemID` Where table_A.PHASE In ('401' ) Now I need to combine the Data of both recordsets. I need EVERYTHING in Table B, but I also need All Table A records that match the phase selection.... Can I write this one query or do I need to use a Temp table?
Can't See Old Records
I really don't know much about mysql. We have an hardware device performing bandwidht management which we didn't even know it is using mysql until it was broken down. After we solved the problem, we saw that the operationg system running in the device is linux (and old version) and the database is mysql. The problem is that we can't see the old data eventhough when I view the binary ISD file I can see the whole data including the olds. But the management program doesn't show the old data. When I reenter new definitions from the management program, I can see that they are written into the ISD files. ISD files contains both old and new records but I can see the only new files. Does that mean the index files are curropted? If so, How can I recreate indexes or recover them?
Records That Are Only From 3 Pm To 6 Pm
I have a field in my table called time which is a datetime field. The format of the data is like these below: 2006-07-12 17:55:19 2006-07-11 16:31:34 ...... I would like to get every records that are only from 3 pm to 6 pm. Is there any query where I can check for the hour of a datetime field?
Top 5 Records - ASP
I have an ASP application talking to a MySQL database. All works fine but: 1) I can't figure out how to select just the latest 5 records in a table. 2) SELECT LAST_INSERT_ID() doesn't work, it just returns 0. 3) I need to select the 'recordid' (held in the first field and is the PK) of the latest record.
Getting Last 12 Records
I am trying to get just the last 12 records from a table. I have tried this but get no results. SELECT TITLE, ARTIST, STYLE, MEDIUM, FORMAT, CATEGORY, WIDTH, HEIGHT, SPRICE, FILENAME, NUM FROM PICTURES WHERE CUSNUM='YES' AND ARTALLOW<>'NO' ORDER BY DESC LIMIT 12
Get Records From The End
my question is how can I get the last 20 rows from a table without changing the order the table is.
Pf Records
Is it possible to specify the limit of records that can be entered in the table.
No Records In DB
I connected to the DB like this: Set rsP1000 = Server.CreateObject("ADODB.Recordset") strSQL = "SELECT [G2P] FROM tblClients WHERE ClientID = '" & strID & "' AND GTime2 ='10:00a'" rsP1000.Open strSQL, adoCon I was wondering if the connection does not return any results, how do I display nothing. It keeps failing out because there is no record.
Records
I have a mysql database running on Mysql version 3.23.53 on a Red Hat Linux 7.2. In the database there is a single table with over 150,000 records. Unfortunately, the performance of that table is very poor, comparing to other tables in the same database.
Get Records
I have one table (members) and a linked table (payments) and I trying to retrieve all the records in the members table that don't have a record in the payments table.The payments table is linked to the members table by memberId. Does anyone know how I would compare the two tables and bring back only the members that don't have a record in the payments table?
To Many Records
This query is getting me rows which dont = $cid can anyone tell me why? =x Code: "SELECT event.eventName, event.eventMonth, event.eventYear, exhibitor.standNum FROM event, exhibitor WHERE exhibitor.companyID = event.companyID AND exhibitor.companyID = '$cid'"
Records
I want to extract those values whose modificatio dates are few days older using the following sql [SQL] select status from note where mdate<=date_add(date('Y-m-d'),interval -2 day) and status='Selected' and id=$r[0]; [/SQL] but the comparison 'mdate<=date_add(date('Y-m-d'),interval -2 day)' doing nothing. I have checked it in db. There is no error too. How to correct that?
40 Records
This is to gather a sample from the database. I need to select 40 consecutive records every 1001st record. So I would need records 1001-1040 and records 2002-2041 and so on. Anyone have a clue on how to write the query for this?
Records 1 To 10, 10 To 20 Etc
im not sure how to do this or what to search for so any help would be great. basically if i do a (select * from table) how do i get only the fiorst ten matches like records 1 to 10 then like on another page id have records 10 to 20. i thought i coudld use limit but when i do say SELECT * FROM `sale` LIMIT 2 , 4 i get 4 results back instead of what i thought would have been results ows 2 to 4
10.000.000 Records
I've got some experience building PHP/MySQL websites, but the databases in these websites never contained more than a couple of thousand records. Now I've been asked to build a website with a database containing a table with roughly 10 million records.This amount of records gives me the creeps, to tell you the truth. It's simple records, containing just 2 columns. The only thing a script would have to do is check wether a code is in the database or not. Can I use standard PHP scripting methods and SQL queries, or does this amount of records demand a totally different approach? I can imagine an application getting very slow when it has to work with a huge recordset like this. What do I have to consider before I decide to build this site (or not)?
How To Get Subtotals Along With All Records?
I have a cart full of items. Each item belongs to a category - so, there might be 3 items in the cart belonging to Category A, 2 to Category B, etc. If possible I'd like to select all items from the cart, and at the same time get the subtotal for each category (so I can then say "$500 was spent on category A" and so forth). I am currently exploring the SUM/GROUP BY avenue, but I will admit I'm not at all sure of what I'm doing...
Counting Records
Got a question for you that is really stumping me. I have 3 tables job_jobs ------------ job_id job_name . . . job_cats ------------- cat_id cat_name . . . job_jobsToCats ------------- jtc_id job_id cat_id *************** ok i have those 3 tables to categorise the jobs on the site and im trying to build a query that will essentially get all categry names and display the number of jobs in that category so when i output it i will get accounting (12) admin(2) etc etc
Records That Don't Match
I have two tables. One has bar information. The second has the bar's drinks. They are tied by 'bar_id'. So there are either one or multiple records in table 'bar_drinks' for a given bar_id. The problem is finding the bars that don't exist in the bar_drinks table. I tried several ways, even a subselect, but get an error of more than one row existing: SELECT bar_name FROM bar WHERE bar_id = (select distinct bar_id from bar_drinks) I see the error here, but really am stumped how to achieve this....
How To Get Records Between Two Dates
In database I have stored dates in ��-00-00 00:00:00' format. And from user I am getting dates in 'mm/dd/yyyy' format. 1- How can I get records from database between user entered dates.
How To Get 15 Minutes Old Records
Scenario : My system enables a record on zero minute of every hour [through cron job]. Lets suppose right now time is 10:10 AM. Query: My query is that -> 1- How can I get all records which are enabled before (1 hour and 15 minutes) of the current time?
Records Being Removed
Can anyone tell me why records would keep being deleted on an mySQL database? I don't think the server is being hacked - but how could I make it more secure (it is Windows 2000)?
Deleting Records
I have the following table PHP +--------+-------+---------------------+ | postID | catID | date | +--------+-------+---------------------+ | 125 | 0 | 2006-08-18 17:02:40 | | 126 | 0 | 2006-08-18 17:03:02 | | 123 | 0 | 2006-08-18 17:03:13 | | 131 | 0 | 2006-08-25 12:34:20 | | 130 | 0 | 2006-08-25 12:34:49 | | 135 | 0 | 2006-08-28 15:14:13 | | 137 | 0 | 2006-08-28 18:23:27 | | 138 | 0 | 2006-08-28 15:43:15 | | 139 | 0 | 2006-08-29 00:17:42 | | 140 | 0 | 2006-08-29 12:01:43 | +--------+-------+---------------------+ What i'd want to do is to setup a daily script to delete records from this table but only leave out 8 latest records. Is there a single sql query to do the deletion?
Update 175,000 Records In 1 Hit
I have just under 175,000 records in my database and need to create an MD5 hash of a certain field in each row. I created a page to run on localhost which had the following PHP <?php mysql_connect("localhost", "root", "") or die(mysql_error()); mysql_select_db("wordlist") or die(mysql_error()); if (isset($id)) { $id=$_GET['id']; } else { $id=Ƈ' } $GetThisWord_result = mysql_query(" SELECT * FROM word_list WHERE id=$id") or die(mysql_error()); $GetThisWord = mysql_query($GetThisWord_result); $GetThisWord = mysql_fetch_assoc($GetThisWord_result); $word=$GetThisWord['word']; if ($word=="") { header("Location: ./end.php"); } else { $query = "UPDATE word_list SET hash=MD5('$word') WHERE id='$id'"; mysql_query($query) or die(mysql_error()); $nextid=$id+1; header("Location: ./index.php?id=$nextid"); } ?> But the browser keeps coming up saying theres an error as the page will just keep repeating itself after it's processed 20 rows. Can I just enter PHP UPDATE word_list SET hash=MD5('$word') WHERE id='$id' or simiilar into MySQL Front and let it do it all itself?
Selecting Top Records
I was posed a question in an interview that required me to select the top 3 records of table after doing a sort. I have been studying MySQL for the last 6 months or so and don't recall that capability in MySQL. I know it is available in Oracle and SQL Server. Is this possible in MySQL?
Records Disappear
Has anyone experienced their records disappearing. I have 8 tables in a DB that each table rougly contain 10 to 900 records. the program that i had developed is a PHP program. I checked the site the other day and their was some data missing. i checked the DB and i noticed zero records in a few tables. the only thing that i can think of is in Cpanel(WHM) I exported the DB the day before for my weekley backups. I export all my DB in PHPmyadmin every week, just incase my server crashes. I have a dedicated server.
Duplicating Records...
I have looked long n hard at the Insert Select as being the answer to my need to create duplicate records. (Purpose is basically to allow someone to create a duplicate of an existing email template then modify it). I have tried all sorts of variations on: INSERT INTO emaillist SELECT * FROM emaillist WHERE emailid = #url.emailid# (inc trying IGNORE) but of course this throws an error over duplicate keys. All I want to do is to take Record A, duplicate it and have a new key assigned to it (the key is an autonumber field).
Duplicate Records
I've noticed what might be a bug. I'm using InnoDB tables to store some payment information. Included in the table is a DATETIME field that logs the exact time a payment was made to a client. The problem is that in one instance there are clearly duplicates of a payment record in the table. i.e. 3 records were inserted that have the exact same information including the same timestamp. I have checked the code making the INSERTs and it can't be that. It's just a simple INSERT query. Has anyone else noticed things like this and is there a solution?
Showing All The Records With The Same Name
I was wondering how to write an sql that'll say "show me all the records that have the same name" I have about 500 records, and i would like to know which of them have the same name (which is a column in my table).
Updating Records
let us say for example i have a database called sales that has a table called sales. sales has 4 columns: names, yr1999, yr2000, yr2001 names ------- bob carla cynthia yr1999 ------- 3000 1200 8000 yr2000 ------- 4000 6700 4500 yr2001 ------ 3400 3200 1200 what I want to do is update yr1999, yr2000, yr2001 by 10 percent so that when i do SELECT * from sales.sales I will be able to see all records updated to their new values.
Getting Records When Using UNIX_TIMESTAMP
I'm currently using this query: SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys ORDER BY date_time ASC LIMIT 0, $nrpp My question is... can I pull all records where the converted "date_time" is > a certain time? I tried the below but it didn't seem to work: $time = time(); SELECT poker_room,game_type,limit_type,UNIX_TIMESTAMP(date_time),date_time,buyin_desc,description,url FROM poker_tourneys WHERE UNIX_TIMESTAMP(date_time) > '$time' ORDER BY date_time ASC LIMIT 0, $nrpp Btw... I'm using PHP as my server-side language.
Searching Records
say i have two tables! one tables with car news and the other table with pictures of these cars. the tables: cars: name model engine pictures: small_pic large_pic waht i want to do is get the name and then search the small_pic column in my pictures table and then output this along with my article. e.g car name: ford i have 3 pictures in my pictures table for this car ford1.jpeg ford2.jpeg ford3.jpeg i want to output ford1.jpeg!! do i have to use a LIKE query? basically i want to show the article and the picture on the side of it.
Updating Records
I'm not sure what function to use in updating records in my existing table. I want to update a field that contains this sample data, "cards/misc/love/test/iloveyou.gif", but I need to retain only the filename (iloveyou.gif). The table already have 12000 records with different filename. What update statement should I used?
Exporting Records
Is there a way for me to get to the tables and export records without having access to login info like user name, password, etc. The company is closed and may need financial transactions in the future which they want to import into an Access database. The MySQL database is owned by us, is on a server under our possession. The developer is asking for an exhorbitant amount to do this and says he has removed the schema and backup the application and software.
How To Return Records Set From SP (SF) In V5.0?
i have stored procedure (or function) and I need to return records set from it. This result set is generated inside SP, so simple select <some columns> from <somewhere> will not work. In postgresql this is done using SET OF construction, but how to do it in MySQL?
Random Records
I'm trying to get a random record for use with my website. I've tried figuring out some premade code and it's led me nowhere.. Here's basically the rundown... I have a database setup by my site's shopping cart system, and in it are 2 tables i need to read from, "specials" and "featured". I want to pull 4 random entries from "specials" and 1 from "featured" to have posted on my site's main page outside of the store. I know how to connect to and select the database, I even know how to send queries, but the whole selecting a random entry is what's got me at a brick wall. I'm not used to PHP and MySQL, i'm just now learning after 4 years of using Perl(and never using a database system, but creating my own database-like files).
Sequences Of Records
I have a table with primary key name "product_id" with values for primary snp1 snp2 snp3 ... snp12 Using phpadmin when i insert a record last record inserted comes at the the last (bottom) I have another table with same primary key name but when i insert a record (snp12) something like this happens snp1 snp12 snp2 snp4 snp5 snp12 instead of going at the end gets inserted after snp1 I don't know why?
Locking Records?
I am using MSAccess 2000 to run against MySQL 4.0.12. What I need to have happen is when a user pulls up a record and begins updating it, another user on another workstation cannot access that same record. Where do I begin with this? Is it a simple setting in the .ini file or using MySQLAdmin?
Combining Records
I have a database table tblobjects like this: object_name, reference_id a 1 a 2 a 3 a 4 b 2 b 3 b 1 b 4 c 2 c 4 c 5 c 6 d 2 d 4 d 5 d 6 I now would like to have a SQL query which gives me the number of unique object and reference combinations, like this: a b c d shouldn't be displayed, because it's equal to c. The problem is also that a sequence of object references is also important. So, for instance, object a shouldn't be equal to object b. The solution should also work is MS SQL and Mysql.
WHERE-ing For Multiple Records
If I say SELECT * FROM table WHERE ID = 1 AND ID = 10; I just get the one record. Be grateful to know the secret for selecting multiple records with a WHERE clause.
Join Sum To Records
Am trying to get a list of all vacation time used by each employee since 1-1-2002. I can do it with 2 separate queries. The first looks like... SELECT e.employee_pk, e.first_name, e.last_name, a.rate,a.effective_date, ((((YEAR(CURRENT_DATE)-YEAR(effective_date))*12))+(MONTH(CURRENT_DATE)-MONTH(effective_date))) AS month_count, ((((YEAR(CURRENT_DATE)-2002)*12))+(MONTH(CURRENT_DATE)-1)) AS max_count FROM employee e, hr_vacation_accrual a WHERE e.employee_pk = a.employee_uid AND e.active=1 AND exempt_status != 'H' AND exempt_status != 'C' ORDER BY e.last_name, a.effective_date What the above is doing is getting the employee's name, their accrual rate (we accrue at various numbers of hours per month based on seniority), the date the new seniority kicks in, and the number of months at this rate, as well as how many months there are since 1-1-2002. Code:
Performance Vs Many Records
I currently have 300 records in table "metrics". These metrics are assigned to users. So each user has access to the 300 metrics. by default, my SQL will load all 300 on a page for the user. I am now wanting the users to be able to say which they want to appear. So, out of the 300 they can tick the ones they want to see. What's the best way of doing this? I have thought about having a table called metrics_to_user which would store the user ID and a whole list of the metrics they choose. By default, every user would then need 300 records in this table? then they untick ones they dont want to see. Or another way is just to store just the ones they DONT want to see. So when I am loading the page, I do some SQL to get all 300, but somehow forget the ones they dont want.
How Display Records With Zero Value?
i have a list of students and their test scores ... some have zero or null for their test score (e.g they may have been absent and not yet taken the test).... if I do the usual SELECT * from studentlist only students with a score greater than zero are listed. How do I get a complete class list which shows me what every student scored, including those who scored zero?
Self Deleating Records
is there a way for records stored that are older than a given amount of time to auto deleat themself? say after 1 week for example?
Datestamp On Records?
is there any sql query which can be done to get the update or creation date of a record?? I've not used a datestamp or anything - I just wondered if there was a low-level command which is somehow stored somewhere??
|
TRACKER
