How To Write Query To Compare Tables
I give in. I can't figure it out, and I know it's one of those things that once I see it I'll think, "OF COURSE!".
I have 2 tables, same DB.
Table 1 is named Policies
Table 2 is named Assignments
Both contain a PolicyID field.
I want to find out which PolicyID entries are ONLY in Policies, and not in Assignments.
So if "Select PolicyID from Assignments" returned the following:
1, 2, 3, 4, 5, 6
And "Select PolicyID from Policies" returned:
1, 2, 3, 4, 5, 6, 7, 8, 9, 10
What do I use to return this? 7, 8, 9, 10
Any nudge, help, clue, etc. would be appreciated. I've looked thru examples of Outer Joins, Inner Joins, Lefts, Rights, Unions, Intersects, etc. and can't figure out which to use, all for what seems to be a simple operation!
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Write Tricky Mysql Query (related Tables)
member id(int, pk) name(varchar) profile_pic(varchar) other_fields books id(int, pk) cat_id(int) name comment id(int, pk) comment_type(enum: books, more_type) comment_id(books id) comment Sample data of comment id comment_type comment_id comment ------------------------------------- 1 member 1 comment about first member 2 books 1 this book is really nice 3 books 2 horror books are instesting 4 member 5 paype is the sailor is now a member now i need a SQL Query to "select all books information having cat_id=1 along with user inputed comments about those books".
View Replies !
View Related
Best Way To Write This Query
Query 1: obtains results in boolean mode from products table based on keywords Query 2: Would like to grab all manufacturers names from manufacturers table based on mf_id in products table to then create a brand filter. What is the best way to do this in the most efficient query possible. I was thinking of building a php array from query 1 of mf id's and the putting them in query 2 and separating them using OR operators. any ideas? here is the query fyi: PHP $sql = "SELECT products_id as prodid, products_name as name, manufacturers_id as manufacturers_id, products_price as price, products_msrp as msrp, products_date_added as pda, products_status as status, products_sku as sku, products_weight as weight, categories_id as cat_id, " .boolean_sql_select( boolean_inclusive_atoms($search_string), $fulltext_key)." as relevance " ."FROM $table_name " ."WHERE " .boolean_sql_where($search_string,$fulltext_key)." " ."HAVING relevance>0 "; if (isset($HTTP_GET_VARS['sort'])) { if ($_GET['sort']=="brand") { $sql.="ORDER BY products_name "; } elseif ($_GET['sort']=="size") { $sql.="ORDER BY products_weight "; } elseif ($_GET['sort']=="price_low") { $sql.="ORDER BY products_price ASC "; } elseif ($_GET['sort']=="price_high") { $sql.="ORDER BY products_price DESC "; } elseif ($_GET['sort']=="fav") { $sql.="ORDER BY products_name "; } } else { $sql.="ORDER BY relevance DESC "; }
View Replies !
View Related
How To Write Group By Query
i create table item-info. i want to view my data in the following way. i write query use group by but i want name show only one time no repeatation name next line. item-info table: id name item price 1 A XX 15 2 B XX 16 3 A YY 17 4 C YY 20 5 B ZZ 50 6 A ZZ 40 View my data in the following way: name item price A XX 15 YY 17 ZZ 40 B XX 16 ZZ 50 C ZZ 20
View Replies !
View Related
Compare 2 Tables
How can I compare 2 databases to check if one is embedded within the other . That mean given a database A , add some tables and new records to it , modify some tables , that result in table B . How can I check that A is embedded with B ? that all A entries and part of B ? . Any easy tools to do that ?
View Replies !
View Related
How Do I Compare Two Tables?
I have two tables with the same columns.Table A and Table B. I would like to compare a column in bot tables and determine if there is an entry in A but not in B, also I'd like to determin if there is an entry in B but not in A. How would I do this?
View Replies !
View Related
Compare Two Tables
I have two tables. First table is just list of dealers (table name dealers). Second table is user input of dealers transactions.(Table name is log) The first field of table two is the dealership name (field name is dealership). Sometime not all dealers in the first table perform transactions. How can show the dealers that are listed in the first table that are not listed in the second table. I tried this, but it did not work select dealers from dealers, log where dealers != dealership
View Replies !
View Related
How To Compare 2 Tables ?
I have two tables containing references and I would like to compute the differences but I have some problems. For example table 1 +--------+ | ref | +--------+ | A250 | | B260 | | G650 | | D250 | +--------+ table 2 +--------+ | ref | +--------+ | A250 | | B280 | | Z650 | | D250 | | W650 | +--------+ SELECT table1.`ref` FROM table1, table2 WHERE table1.`ref` NOT LIKE table2.`ref` group by table1.`ref` ; does not compute corectly the différences. On the other end : SELECT table1.`ref` FROM table1, table2 WHERE table1.`ref` LIKE table2.`ref` group by table1.`ref` ; computes correctly the comon references. So what is wrong in the first query ? What can I do to see the differences between my 2 tables ?.
View Replies !
View Related
Compare Tables
Does anyone know if there is any tool that can compare two databases (and the tables) and allow you to create the SQL necessary to make them identical? I.e. creating new tables, adding columns,
View Replies !
View Related
To Re-write Complex Query With JOINS
I have a problem with one of the JOIN query here. Below is a brief description of the problem. tablename : test RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 12004-01-10110 22004-01-20120 32004-01-20270 42004-01-10280 52004-01-15110 62004-01-25310 Output : RecordId EffectiveDateothertableidvalue -------- ------------- ------------ ----- 22004-01-20120 32004-01-20270 62004-01-25310 Now I want to use a single SQL query to find a result where there exist one record for each unique "othertableid" where the record selected for the "othertableid" should be the recent one with regard to "EffectiveDate". That is from the above records, I want to select Records with "RecordId" = 2 and 3 because they are the recent one for "othertableid" = 1 and 2 respectively. Please be sure that I want to retrive all fields including "RecordId". The result should not depend on any other fields but "EffectiveDate" only. I am using MySQL 4.0.12 and it does not support "SUBQUERIES" which is now given support in latest MySQL edition. But I have read in the manual of MySQL that any "SUBQUERY" SQL statement can be written with proper "JOINS".
View Replies !
View Related
Trying To Write A Two-part Fast Query
The situation is this: there are two tables, one called "objects" (fields: object_id[int] and object_name) and properties (fields: object_id[int] and property_name and score[int]). Objects have one or more properties. In short, what these 2 queries need to ask are "what objects could an unknown object be, given what properties I know?" and "what property should I measure next to most narrow down the possible objects?" The objective of the first query is to return an object who has all of the given properties scored above or below the given value. For example, I might look for the object_id of an object who has the property "green" scored above 3, the property "hot" scored below 2, and the property "bright" scored above 0. It might have other properties, too, but it must have these 3. And then, for the second part, I need a query that will select properties which are NOT in this set of given properties but are relevant to the returned set of objects. In this part, I'm not concerned with the property's score - just that it is relevant to the objects. In other words, this query might tell me that the property "hard" is relevant to 3/4 objects (even though one of them had a very low score for the hard property, another had a very high one, etc). Basically I want to know which new property, once I can determine the score of this new property, will help narrow down the object-set the most, so that I can collect data for the most relevant property to narrow down the object set.
View Replies !
View Related
Compare Record In Two Tables
if there is an easy way to compare a row from table a, to a row in table b with the same structure/field names and return those fields which don't match?I have table_a storing the originally submitted applicant information,and table_b storing a version that can be changed. When a user makes a change I'd like to compare it to the original table to see what fields were changed, preferably without opening the row from both tables and going field by field in code to see if they match...
View Replies !
View Related
Compare Fields Of Two Tables
There is a way to compare fields and rows in two tables and display the values that differ from each other. Example: I have a table called "products", and a table called "products_bak", with a few fields: id, name, quantity. "Products_bak" contains yesterdays values, and "products" contains todays updated values. "Products_bak.quantity" contains yesterday's stock, and "products.quantity" contains current stock. Is there a way to compare the two tables and get the differences. I would like to be notified that the stock has increased from 3 to 5 in the first row, and that the name has changed from "TestOld" to "TestNew".
View Replies !
View Related
Compare Tables Before DELETE
I need to delete a record from one table in one database, but compare it to a second table in a second database (it is essential that these tables remain in separate DBs). I tried the following, but kept getting a syntax error: delete from db1.table1 AS a USING db2.table2 AS b where a.user_name = b.user_id I realized that I cannot use aliases to do cross-DB queries.
View Replies !
View Related
Compare And Delete Two Tables
I have two tables which have a common field id (partner,pid),(invester,pid) which need to be in synchronous all the time. The user has provision to delete (partner,pid). So some of the pid in (invester,pid) are left behind. I cannot have a foreign key relation because changes in the original table (partner,pid) will need a lot of updates and code management. I can only perform a direct operation on (invester,pid). Is there a way to compare both tables (partner,pid) and (invester,pid) and delete the pids from (invester,pid) which do not match with the ones in (partner,pid)?
View Replies !
View Related
Compare Fields Tables
I would like to compare two tables, in both tables there is an user field called "user" (table_1.user, and table_2.user). Thee is however a small problem, in table_1 the users are inserted like this: 235463 and in table_2 the users where inserted like this: p235463! Would it still be possible to compare this fields in some way (By using only the numbers maybe? But how?)
View Replies !
View Related
Inner Join :: Compare Tables
Here is what I need to do- I have 2 tables related by an ID (int) field. I need to return all fields from one table and none from the second but I need to run a comparison against a field on the second table and if the field data equals 'x' then the corresponding record from the first table should not be returned. Seems simple to just say it, lol. I changed the way I was querying the db in the middle of the development so this particular situation wasn't anticipated when I created the db. Code:
View Replies !
View Related
Compare And Get Result From Two Tables
I'm trying to compare the id from the users table to the id of the white_player in the games table, and provide me with the users name. I think I am close, but unsure of how to actually get the result. Pretty sure the query is good, but not sure what the second line should look like. $wplayer = mysql_fetch_object(mysql_query("SELECT ".$this->settings->mysql_prefix."users.nick," .$this->settings->mysql_prefix."users.id," .$this->settings->mysql_prefix."game.white_player from ".$this->settings->mysql_prefix."users ".$this->settings->mysql_prefix."game WHERE " .$this->settings->mysql_prefix."users.id = " .$this->settings->mysql_prefix."game.white_player ")); $wplayer = $wplayer->nick;
View Replies !
View Related
Compare 2 Tables And Delete
i made a rookie mistake in my delete user code and now i got a problem that i still got challenges by the user in my database with the deleted user_id i have two tables set like so Table users contains one cell called user_id Table challenges contains two cells called challenge_to and challenge from - each contain a user_id i need to get all the user_id `s from the users table and then delete all challenges that contain a user_id not in the list
View Replies !
View Related
Can't Compare 2 Tables' Columns
For some reason, comparing 2 tables using the operator "<>" is not working but using the opposite operator "=" is returning the correct opposite results. I have 2 tables: wp_posts with a column called 'post_title' sas_products with a column called 'sas_product_id' Both 'post_title' and 'sas_product_id' contain the same type of data, a product ID. So if I do the opposite query from what I want, it works: SELECT ID FROM wp_posts, sas_products WHERE post_title = sas_product_id Results give me the 100 rows where the post_title and sas_product_id are equal. But, if I do the following query, I get 10,000+ results when I should just get 2. SELECT ID FROM wp_posts, sas_products WHERE post_title <> sas_product_id Can someone help explain what it is I am missing here?
View Replies !
View Related
Compare 2 Tables And Update One If A Match.
I have two tables "contact_tbl" and "address_tbl". I have a query that compares the email addresses between the two. If a match is found I need to update a specific field in "contact_tbl" with a value of 'yes'. My query is: SELECT * FROM contact_tbl INNER JOIN address_tbl ON contact_tbl.email = address_tbl.email; This works and I get a nice result set showing records where the email addresses match. I now need to update a field "status" in contact_tbl when there is a match. Have been tinkering with this for a while but not sure of the syntax and placement for the UPDATE code.
View Replies !
View Related
Compare Data Beetwen 2 Tables
MYSQL problem: I'm trying to copy all unique data from a heap table to a myisam table: CREATE TABLE `heap_in` ( `id` varchar(32) NOT NULL default '', `domain` varchar(50) NOT NULL default '', ) TYPE=HEAP; CREATE TABLE `last_visits` ( `id` varchar(32) NOT NULL default '', `domain` varchar(50) NOT NULL default '', KEY `id` (`id`,`domain`) ) TYPE=MyISAM; Heap table just record the data. heap_in.id and heap_in.domain are not unique. While I'm inserting data to myisam table I want to insert just one time the same domain(if it is duplicated) for the same id. Code:
View Replies !
View Related
Compare And Update Table Structure Of Two Tables
Is there an easy way to compare the structures of two tables and update the structure of one table based on the differences found? I imagine this might work by exporting the structure (not the data) from two tables to SQL files, then compare the two of them to produce a third SQL file with only the differences. I need something like this because I'm running the same database on a number of different sites, and don't want to have to manually update each DB everytime I make a change. Or perhaps someone could offer a procedure I could follow to make updating DB's with new structures less involved? Something that makes it easy for me to keep track of changes I've made.
View Replies !
View Related
Flat File Load And Compare 2 Tables
I'm trying to complete sounds simple, so I'm not sure why I can't understand it. I created two(2) tables with the same values +------------------+----------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------------+----------+------+-----+---------+-------+ | PNODE | char(10) | YES | | NULL | | | Source_Directory | char(80) | YES | | NULL | | | SNODE | char(10) | YES | | NULL | | | Dest_Directory | char(80) | YES | | NULL | | | File_Permission | char(10) | YES | | NULL | | | Size | int(10) | YES | | NULL | | | File_Date | datetime | YES | | NULL | | | File_name | char(30) | | PRI | | | +------------------+----------+------+-----+---------+-------+ My idea is to have a flat file loaded into table a, then have it compare against table b. - If files exist in both table a and table b, then just ignore. - If files exist in Table a but not in table b, then add to table b - if files exist in Table b but not in table a, then remove from table b I was thinking it would be easier to have a 3rd table to hold the results of the compare, but not sure. I looked at the documentation for "load data infile", but do not konw if it can run the compare during the load.
View Replies !
View Related
How To Write Query To Select The Max(version) For Each Unique File_name Record?
I am a MySQL newbie trying to write a query that selects file_name records possessing the highest numbered version for that unique file_name. I show sample data and two trial queries below. Logically I want to use max(version) as a constraint in a Where Clause. However, the max() function is not allowed directly in a where clause. I have contemplated a second table to track the max version for each file name. I would like to structure the data in an efficient manner for query performance when the data set grows to many thousands of unique file_name records with many hundreds of versions each........
View Replies !
View Related
Search And Write, Or Write And Recover?
The problem: I need to generate a 'unique string' for each row in a table. I already use auto_increment for system dependencies between tables. What is the best approach one of these or another? After generating a candidate 'unique string' the two strategies that came to mind are: 1. to then search the table's column to see if it is already assigned; locking the table for write while searching and writing the new row, or 2. set the column to UNIQUE when defining the table. Just go ahead and write the new row if you get a "non-unique" exception, generate another 'unique string' and try again. I've tried both on a small XP laptop and get "lock timeout exceptions" rather quickly using #1. But replace those with lots of re-writes when there starts to get "collisions" of 'unique string's.
View Replies !
View Related
Compare Two Date Columns In Mysql Query
I have 2 date columns in my table. One grabs the date that the article was created. The 2nd is user-defined and is used to tell the site when to "auto-archive" the article. I need to compare the two date columns to each other in a query so that I know if there are any current articles that have not been auto-archived. I thought I could use something like: $query = "SELECT * FROM publications where date_full >= auto_archive"; But this doesn't work. Anyone got any ideas?
View Replies !
View Related
Compare 2 Tables And Find Rows Which Don't Match (was "Mysql Noob Question")
I have a products table and a products_description table. They both have products_id as primary key. However, products_description table has more products_id keys then the products table. How do i compare the 2 tables and show just the products_id that dont match from products_descripition table? SELECT products.products_id, products_description.products_id FROM products, products_description WHERE products.products_id != products_description.products_id; I tried that query but got some wierd results and also the results gave me 2 columns when I just want one.
View Replies !
View Related
Cant Write Or Delete
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
View Related
How To Write A Trigger
we have an invoice table with fields ( invoice_id,date, item_id,item_name, price_per_item,quantity,cust_id) here (item_id ,item_name and price_per_item) are from table item_details. now i have to write a trigger to track the users who have changed the price_per_item column before insertion in invoice table.
View Replies !
View Related
Write Data To Row With Via C API
I have an app written in C that sends data to text files. The files receive 8 variables and look like this: May 19 20:42:09 2002 7 Services 3.23 4.34 0 None 2.31 Rather than writing data to a text file, I need that data to go to a MySQL 5.0.41 database row. I've already got the C API working and connecting to the "localhost" database, but now I need to write data with each transaction. Optimally, I'd like to send the data directly to the database row, but there doesn't seem to be an INSERT statement to do that. What's the best method to write the data to the database directly?
View Replies !
View Related
Can't Write To File
Using MySQL 4.1.11-max on OS X 10.3. Using command line. Can't successfully use "INTO OUTFILE". Getting the following: mysql> select * -> into outfile '/users/steves/desktop/test.txt' -> fields terminated by ',' -> from industry; ERROR 1 (HY000): Can't create/write to file '/users/steves/desktop/test.txt' (Errcode: 13) At the shell, I get this: steves2ndmac:~ steves$ perror 13 OS error code 13: Permission denied Newbie in both Unix shell and MySQL. Can anyone tell me how to set permissions for Unix User mysql?
View Replies !
View Related
How To Write Trigger
want to copy the table data from one host to another host why because its live data . i want to update one host to another host. ie i have one host 192.168.1.1 (db name is EXample table is live) i want to copy the live table values into another table which is located in different host ie(192.168.5.5 db name is Datadummy and table is livedummy). in triggers is it possible or not?
View Replies !
View Related
Cannot Write To Database
The result I am getting is: request "Could not execute SQL query" SELECT dt FROM webcalendar_events WHERE dt in ('2009-03-13') AND calendar_id=cum_wetheralhalls FOR UPDATE Unknown column 'cum_wetheralhalls' in 'where clause' The strange thing is that when the calendar_id is a numeric value, it works perfectly. Is there anything that I can do in MySQL to get this to work with text-characters also, or do I have to use numeric values for this? Is this something in the PHP script that is preventing it from saving to the database? In case you need to know the table row calendar_id is varchar(30).
View Replies !
View Related
Help Me Write This Join?
Hi there, been ages since I wrote any sql, and I'm a little stuck but I figure this is easy for you folks. I have this original query: $posts = $wpdb->get_results("SELECT ID, post_title FROM " . $wpdb->posts . " WHERE post_status='publish' ORDER BY post_date DESC"); And this gives a lists of all posts. However, I want to only grab posts by a certain category now, say category #9. This information is kept in an intersection table called wp_post2cat. The columns in that table are: rel_id post_id category_id category_id is what I want, and post_id is the key pointing back to the original post. Posts can have multiple categories, but I just want the one listing where it's category 9, which is audio. So pseudocode would be something like: SELECT all posts WHERE post_status equals "publish" and the category is equal to audio (9).
View Replies !
View Related
How To Write This In MySQL?
I Want to check if a certain value doesnt exist in a ceratain field in my table as a condition. "select id from employees inner join translog on employees.emp_id=translog.empid inner join outofofficedays on employees.emp_id=outofofficedays.emp_id inner join holidays where (here is my question: ��-04-02' doesnt exist in the column translog.vtransdate) and (��-04-02' not between holidays.H_START and H_END) order by translog.vTransDate,employees.emp_id limit 1"
View Replies !
View Related
How To Write This Select?
I'm having some troubles writing a select. I have the following tables: theater ------- - id - name movie ----- - id - name showtime --------- - id - theater - movie - from - to - schedule I would like to list all the theaters with all the available showtimes and movies. I tried to, but i didn't get the result i was expecting. Can somebody help?
View Replies !
View Related
How Do I Write This Trigger?
I’ve been searching all over for the answer to this one. If you have the answer, it would help me out a lot! Using MySQL triggers, I want to sync content between two tables; I have table “user” and table “users”, each have similar columns “last_name” “first_name” “user_id” …etc, and when a row gets written to table “users”, I wanted that record to be replicated and inserted to “user” (with all the same field info). How do I write this trigger? I know, it’s anti-normalization, but it will really help me out with testing one of our site's authentication.
View Replies !
View Related
Create Single Query From Queries On Two Tables (was "Help With Query...")
I read from other thread that query inside loop is not good idea. May I ask some help how can I create a single query to the following code which I use loop. $sql = "SELECT * FROM mytable order by points desc limit 10"; $rec = mysql_query($sql) or die(mysql_error()); $datas = mysql_fetch_array($rec); do{ $sq = "Select * from secondtable where linkid = '$datas[id]'"; $rst = mysql_query($sq) or die(mysql_error()); $rows = mysql_fetch_array($rst); echo "$rows[somefield]"; }while($datas= mysql_fetch_array($rec)); This works perfectly but I want the second query to be out of the loop if there is a way and how.
View Replies !
View Related
C- Program To Write To Myi , Myd File
is it possible to create a myi, myd file for mysql using a C program. I am using Stata that is written in C API, and I want to write the data out to MYSQL database. Using ODBC and loading the data in MYSQL from STATA takes forever. Can anyone please tell me if I can create the myd files by using the data in memory thru C programming.
View Replies !
View Related
Using INSERT To Write To A Directory
I have a problem that I'm not sure if it can be done. I'm trying to use the MySQL C API to be able to use a normal sql insert statement that will send the data or file to a directory and NOT the database. I'm not sure if there is anything out there that will allow this, but I've searched everywhere, and I have found no solution.
View Replies !
View Related
Write Conflict Error
I have a database with access as front end and Mysql as back end. I am gettting the following 'Write Conflict' Error. when i am on my order form, This form does has a subform where i enter all the products that are ordered. Could any one let me know what could cause this problem. I am the only one using the database as its still in the build stage Code:
View Replies !
View Related
Cant Write Or Delete To Table
I cant get my php script to insert or delete records into a mysql table. I can view them all fine but that's about it. I've checked the user permissions on mysql and have set them all to 'Y' incase that was the problem but still no joy. Can anyone suggest what the problem may be? I'm tried the usual RTFM but cant spot the problem
View Replies !
View Related
MySql Write Vs Read
Essentially I am faced with a design choice; have an oft run (modifiable) query run every time any user wants to run it, comparing criteria againt each and every user in the table and delviering list OR saving that list (user/user/) so the next time all that needs to be queried is the existence of a record in the table. The basic tradeoff; version 1 there are many simultaneous queries of the user db on complex search criteria; version 2 there are fewer but their are as many far more simple queries to a much larger table, not to mention many writes to same to keep the table current. I know this is pretty vague, but I was wondering if in general there is an area that mysql excels in vis a vis complex query smaller tables simple query larger tables. Question 2 is there anyway to guestiate the search/write times of a user log in to both query and update? #Records, fields, etc? I'd like to keep log in to < 10 seconds max.
View Replies !
View Related
Select .. Into Outfile Write
global scope: trying to execute on a schedule a .sql file which writes out a .csv file. windows(dont ask)/mysql in the batch file i have mysql -u myUser --password=somePass < myFile.sql the sql query in the file Code: use mydb; select field1,field2,field3 union select `actField1`,`actField2`,`actField3` into outfile 'F:folder est.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' FROM `detailsTable`;
View Replies !
View Related
Create/write To The File
I am working on Solaris and seem to get the following error Cant create/write to the file '/home/dslab456/temp/tempoutput.lst' As in windows i cannot specify which drive it is or so ? could anyone help me out with this ? SELECT * INTO OUTFILE '/home/dslab456/temp/tempoutput.lst' FROM stocklist_vokus s,p_tmpr p WHERE s.STOCKLIST =p.stocklist_value ORDER BY p.rid Edited 1 time(s). Last edit at 02/12/2007 05:38AM by summer queen.
View Replies !
View Related
|
TRACKER
