Search Results

Search found 8943 results on 358 pages for 'audit tables'.

Page 21/358 | < Previous Page | 17 18 19 20 21 22 23 24 25 26 27 28 | Next Page >

IP Tables won't save the rule.

- by ArchUser

Hello, I'm using ArchLinux and I have an IP tables rule that I know works (from my other server), and it's in /etc/iptables/iptables.rules, it's the only rule set in that directory. I run, /etc/rc.d/iptables save, then /etc/rc.d/iptables/restart, but when I do "iptables --list", I get ACCEPTs on INPUT,FORWARD & OUTPUT. # Generated by iptables-save v1.4.8 on Sat Jan 8 18:42:50 2011 *filter :INPUT DROP [0:0] :FORWARD DROP [0:0] :OUTPUT ACCEPT [216:14865] :BRUTEGUARD - [0:0] :interfaces - [0:0] :open - [0:0] -A INPUT -p icmp -m icmp --icmp-type 18 -j DROP -A INPUT -p icmp -m icmp --icmp-type 17 -j DROP -A INPUT -p icmp -m icmp --icmp-type 10 -j DROP -A INPUT -p icmp -m icmp --icmp-type 9 -j DROP -A INPUT -p icmp -m icmp --icmp-type 5 -j DROP -A INPUT -p icmp -j ACCEPT -A INPUT -m state --state RELATED,ESTABLISHED -j ACCEPT -A INPUT -j interfaces -A INPUT -j open -A INPUT -p tcp -j REJECT --reject-with tcp-reset -A INPUT -p udp -j REJECT --reject-with icmp-port-unreachable -A INPUT -p tcp -m tcp ! --tcp-flags FIN,SYN,RST,ACK SYN -m state --state NEW -j DROP -A INPUT -f -j DROP -A INPUT -p tcp -m tcp --tcp-flags FIN,SYN,RST,PSH,ACK,URG FIN,SYN,RST,PSH,ACK,URG -j DROP -A INPUT -p tcp -m tcp --tcp-flags FIN,SYN,RST,PSH,ACK,URG NONE -j DROP -A INPUT -i eth+ -p icmp -m icmp --icmp-type 8 -j DROP -A BRUTEGUARD -m recent --set --name BF --rsource -A BRUTEGUARD -m recent --update --seconds 600 --hitcount 20 --name BF --rsource -j LOG --log-prefix "[BRUTEFORCE ATTEMPT] " --log-level 6 -A BRUTEGUARD -m recent --update --seconds 600 --hitcount 20 --name BF --rsource -j DROP -A interfaces -i lo -j ACCEPT -A open -p tcp -m tcp --dport 80 -j ACCEPT -A open -p tcp -m tcp --dport 10011 -j ACCEPT -A open -p udp -m udp --dport 9987 -j ACCEPT -A open -p tcp -m tcp --dport 30033 -j ACCEPT -A open -p tcp -m tcp --dport 8000 -j ACCEPT -A open -p tcp -m tcp --dport 8001 -j ACCEPT -A open -s 76.119.125.61 -p tcp -m tcp --dport 21 -j ACCEPT -A open -s 76.119.125.61 -p tcp -m tcp --dport 3306 -j ACCEPT -A open -p tcp -m tcp --dport 22 -j BRUTEGUARD -A open -s 76.119.125.61 -p tcp -m tcp --dport 22 -m state --state NEW,RELATED,ESTABLISHED -j ACCEPT COMMIT # Completed on Sat Jan 8 18:42:50 2011

Read the article
Make a snapshot of a live mySQL database with myISAM & innoDB tables without locking

- by Artem

We have a live database in production where we are running out of space on the server. So I would like to transfer to a new server without any downtime (or as little downtime as possible). In general, I would also like to have a hot failover copy of the database available. I would like to use replication to get all of the data copied to the new machine, and then at some point flip a switch and have that new machine become the master (normal failover scenario). My problem is that I am not sure how to initialize replication without locking the db to make the initial snapshot I will use? Is there any way to do this? I know I could do it using single-transaction if I was using innoDB, but very unfortunately we have some myISAM tables in there (in fact the largest 150GB table is myISAM and I want to switch it to InnoDB but I can't do it until I have more space & a hot copy to switch to). Any ideas? Is there some way to make such a snapshot? Or is there alternatively a way to get replication to "catch up" without an snapshot for initialization?

Read the article
Livre Blanc : « Les outils de recensement et d'audit open-source », Smile revient sur l'utilité d'analyser son « patrimoine logiciel »

Livre Blanc : « Les outils de recensement et d'audit open-source » Smile revient sur l'utilité d'analyser son « patrimoine logiciel » Pour Smile, le recensement est le point de départ d'une politique open source : « il s'agit de faire l'état des lieux des logiciels open source utilisés dans l'entreprise ou entrant dans la composition d'un programme donné ». Le but est d'optimiser et d'accompagner l'analyse d'un « patrimoine de logiciel », (identifier les composants open source utilisés, les licences, etc.). Pour faire le point sur les différents outils du marché (Blackduck Software, Protecode, Palamida, OpenLogic, ou le français Antepedia) Smile vien...

Read the article
Make a Crystal Report with data fetched from two differents tables

- by Selom

Hi, Im using vb.net and I need to fetch data from two different tables and display it in form of report. These are the schemas and data of my two tables: CREATE TABLE personal_details (staff_ID integer PRIMARY KEY, title varchar(10), fn varchar(250), ln varchar(250), mn varchar(250), dob varchar(50), hometown varchar(250), securityno varchar(50), phone varchar(15), phone2 varchar(15), phone3 varchar(15), email varchar(250), address varchar(300), confirmation varchar(50), retirement varchar(50), designation varchar(250), region varchar(250)); INSERT INTO personal_details VALUES(1,'Mr.','Selom','AMOUZOU','Kokou','Sunday, March 28, 2010','Ho',7736,'024-747-4883','277-383-8383','027-838-3837','[email protected]','Lapaz Kum Hotel','Sunday, March 28, 2010','Sunday, March 28, 2010','Designeur','Brong Ahafo'); CREATE TABLE training( training_ID integer primary key NOT NULL, staff_ID varchar(100), training_level varchar (60), school_name varchar(100), start_date varchar(100), end_date varchar(100)); INSERT INTO training VALUES(1,1,'Primary School','New School','Feb 1955','May 1973'); INSERT INTO training VALUES(2,1,'Middle/JSS','Ipmc','Feb 1955','May 1973'); Im trying to fetch and display data from the tables above the following way: Dim rpt As New CrystalReport1() Dim da As New SQLiteDataAdapter Dim ds As New presbydbDataSet ds.EnforceConstraints = False If conn.State = ConnectionState.Closed Then conn.Open() End If Dim cmd As New SQLiteCommand("SELECT p.fn, t.training_level FROM personal_details p INNER JOIN training t ON p.staff_ID = t.staff_ID", conn) cmd.ExecuteNonQuery() da.SelectCommand = cmd da.Fill(ds) rpt.SetDataSource(ds) CrystalReportViewer1.ReportSource = rpt conn.Close() My problem is that nothing displays on the report unless I take off either the training fields or the personal_details fields from the report. Need your help. Thanks

Read the article
x86 instruction encoding tables

- by Cheery

I'm in middle of rewriting my assembler. While at it I'm curious about implementing disassembly as well. I want to make it simple and compact, and there's concepts I can exploit while doing so. It is possible to determine rest of the x86 instruction encoding from opcode (maybe prefix bytes are required too, a bit). I know many people have written tables for doing it. I'm not interested about mnemonics but instruction encoding, because it is an actual hard problem there. For each opcode number I need to know: does this instruction contain modrm? how many immediate fields does this instruction have? what encoding does an immediate use? is the immediate in field an instruction pointer -relative address? what kind of registers does the modrm use for operand and register fields? sandpile.org has somewhat quite much what I'd need, but it's in format that isn't easy to parse. Before I start writing and validating those tables myself, I decided to write this question. Do you know about this kind of tables existing somewhere? In a form that doesn't require too much effort to parse.

Read the article
SQL View with Data from two tables

- by Alex

Hello! I can't seem to crack this - I have two tables (Persons and Companies), and I'm trying to create a view that: 1) shows all persons 2) also returns companies by themselves once, regardless of how many persons are related to it 3) orders by name across both tables To clarify, some sample data: (Table: Companies) Id Name 1 Banana 2 ABC Inc. 3 Microsoft 4 Bigwig (Table: Persons) Id Name RelatedCompanyId 1 Joe Smith 3 2 Justin 3 Paul Rudd 4 4 Anjolie 5 Dustin 4 The output I'm looking for is something like this: Name PersonName CompanyName RelatedCompanyId ABC Inc. NULL ABC Inc. NULL Anjolie Anjolie NULL NULL Banana NULL Banana NULL Bigwig NULL Bigwig NULL Dustin Dustin Bigwig 4 Joe Smith Joe Smith Microsoft 3 Justin Justin NULL NULL Microsoft NULL Microsoft NULL Paul Rudd Paul Rudd Bigwig 4 As you can see, the new "Name" column is ordered across both tables (the company names appear correctly in between the person names), and each company appears exactly once, regardless of how many people are related to it. Can this even be done in SQL?! P.S. I'm trying to create a view so I can use this later for easy data retrieval, fulltext indexing and make the programming side simpler by just querying the view.

Read the article
SQL 2008: Using separate tables for each datatype to return single row

- by Thomas C

Hi all I thought I'd be flexible this time around and let the users decide what contact information the wish to store in their database. In theory it would look as a single row containing, for instance; name, adress, zipcode, Category X, Listitems A. Example FieldType table defining the datatypes available to a user: FieldTypeID, FieldTypeName, TableName 1,"Integer","tblContactInt" 2,"String50","tblContactStr50" ... A user the define his fields in the FieldDefinition table: FieldDefinitionID, FieldTypeID, FieldDefinitionName 11,2,"Name" 12,2,"Adress" 13,1,"Age" Finally we store the actual contact data in separate tables depending on its datatype. Master table, only contains the ContactID tblContact: ContactID 21 22 tblContactStr50: ContactStr50ID,ContactID,FieldDefinitionID,ContactStr50Value 31,21,11,"Person A" 32,21,12,"Adress of person A" 33,22,11,"Person B" tblContactInt: ContactIntID,ContactID,FieldDefinitionID,ContactIntValue 41,22,13,27 Question: Is it possible to return the content of these tables in two rows like this: ContactID,Name,Adress,Age 21,"Person A","Adress of person A",NULL 22,"Person B",NULL,27 I have looked into using the COALESCE and Temp tables, wondering if this is at all possible. Even if it is: maybe I'm only adding complexity whilst sacrificing performance for benefit in datastorage and user definition option. What do you think? Best Regards /Thomas C

Read the article
Retrive data from two tables in asp.net mvc using ADO.Net Entity Framework

- by user192972

Please read my question carefully and reply me. I have two tables as table1 and table2. In table1 i have columns as AddressID(Primary Key),Address1,Address2,City In table2 i have columns as ContactID(Primary Key),AddressID(Foriegn Key),Last Name,First Name. By using join operation i can retrive data from both the tables. I created a Model in my MVC Application.I can see both the tables in enitity editor. In the ViewData folder of my solution explorer i created two class as ContactViewData.cs and SLXRepository.cs In the ContactViewData.cs i have following code public IEnumerable<CONTACT> contacts { get; set; } In the SLXRepository.cs i have following code public IEnumerable<CONTACT> GetContacts() { var contact = ( from c in context.CONTACT join a in context.ADDRESS on c.ADDRESSID equals a.ADDRESSID select new { a.ADDRESS1, a.ADDRESS2, a.CITY, c.FIRSTNAME, c.LASTNAME } ); return contact; } I am getting the error in return type Cannot implicitly convert type 'System.Linq.IQueryable' to 'System.Collections.Generic.IEnumerable'. An explicit conversion exists (are you missing a cast?)

Read the article
PL/SQL - How to pull data from 3 tables based on latest created date

- by Nancy

Hello, I'm hoping someone can help me as I've been stuck on this problem for a few days now. Basically I'm trying to pull data from 3 tables in Oracle: 1) Orders Table 2) Vendor Table and 3) Master Data Table. Here's what the 3 tables look like: Table 1: BIZ_DOC2 (Orders table) OBJECTID (Unique key) UNIQUE_DOC_NAME (Document Name i.e. ORD-005) CREATED_AT (Date the order was created) Table 2: UDEF_VENDOR (Vendors Table): PARENT_OBJECT_ID (This matches up to the ObjectId in the Orders table) VENDOR_OBJECT_NAME (This is the name of the vendor i.e. Acme) Table 3: BIZ_UNIT (Master Data table) PARENT_OBJECT_ID (This matches up to the ObjectID in the Orders table) BIZ_UNIT_OBJECT_NAME (This is the name of the business unit i.e. widget A, widget B) Note: The Vendors Table and Master Data do not have a link between them except through the Orders table. I can join all of the data from the tables and it looks something like this: Before selecting latest order date: ORD-005 | Widget A | Acme | 3/14/10 ORD-005 | Widget B | Acme | 3/14/10 ORD-004 | Widget C | Acme | 3/10/10 Ideally I'd like to return the latest order for each vendor. However, each order may contain multiple business units (e.g. types of widgets) so if a Vendor's latest record is ORD-005 and the order contains 2 business units, here's what the result set should look like by the following columns: UNIQUE_DOC_NAME, BIZ_UNIT_OBJECT_NAME, VENDOR_OBJECT_NAME, CREATED_AT After selecting by latest order date: ORD-005 | Widget A | Acme | 3/14/10 ORD-005 | Widget B | Acme | 3/14/10 I tried using Select Max and several variations of sub-queries but I just can't seem to get it working. Any help would be hugely appreciated!

Read the article
Horizontally align rows in multiple tables using web user control

- by goku_da_master

I need to align rows in different tables that are layed out horizontally. I'd prefer to put the html code in a single web user control so I can create as many instances of that control as I want and lay them out horizontally. The problem is, the text in the rows needs to wrap. So some rows may expand vertically and some may not (see the example below). When that happens, the rows in the other tables aren't aligned horizontally. I know I can accomplish all this by using a single table, but that would mean I'd have to duplicate the name, address and phone html code instead of dynamically creating new instances of my user control (in reality there are many more fields than this, but I'm keeping it simple). Is there any way to do this whether with div's, tables or something else? Here's the problem: Mary Jane's address field expands 2 lines, causing her phone field to not align properly with John's and Bob's. Name: John Doe Name: Mary Jane Name: Bob Smith Address: 123 broadway Address: Some really long address Address: Short address Phone: 123-456 that takes up multiple lines Phone: 111-2222 Phone: 456-789 I'm not restricted in any way how to do this (other than using asp.net), but I'd prefer to use a single web control that I instantiate X times at design time (in this example, it's 3 times). I'm using VS2008, and .Net 3.5

Read the article
MySQL - optimising selection across two linked tables

- by user293594

I have two MySQL tables, states and trans: states (200,000 entries) looks like: id (INT) - also the primary key energy (DOUBLE) [other stuff] trans (14,000,000 entries) looks like: i (INT) - a foreign key referencing states.id j (INT) - a foreign key referencing states.id A (DOUBLE) I'd like to search for all entries in trans with trans.A 30. (say), and then return the energy entries from the (unique) states referenced by each matching entry. So I do it with two intermediate tables: CREATE TABLE ij SELECT i,j FROM trans WHERE A30.; CREATE TABLE temp SELECT DISTINCT i FROM ij UNION SELECT DISTINCT j FROM ij; SELECT energy from states,temp WHERE id=temp.i; This seems to work, but is there any way to do it without the intermediate tables? When I tried to create the temp table with a single command straight from trans: CREATE TABLE temp SELECT DISTINCT i FROM trans WHERE A30. UNION SELECT DISTINCT j FROM trans WHERE A30.; it took a longer (presumably because it had to search the large trans table twice. I'm new to MySQL and I can't seem to find an equivalent problem and answer out there on the interwebs. Many thanks, Christian

Read the article
Silverlight RIA Services - Showing columns from multiple tables

- by Villager

Hello, I have been evaluating Silverlight and .NET RIA Services for my company. I am trying to decide if it is right for us. For the most part, I like it. But, I see one item that I am surprised that I cannot do easily. Because of this, I'm guessing I'm doing some thing wrong. To demonstrate, I have two database tables: Order ----- ID CustomerID OrderDate OrderNumber Customer -------- ID FirstName LastName Address When I create my Domain Service class, I select both of these tables. On the Silverlight application, I drag-and-drop the Order entity from the Data Sources page to my Silverlight page. When I do this, a DataGrid is added with all of the properties in the Order entity. In reality though, I would like the DataGrid to show: Order.OrderNumber Order.OrderDate Customer.FirstName Customer.LastName Because this information is spread across multiple tables, I'm not sure how to use RIA Services to show them in my Silverlight application. What is the recommended way to do this? Should I add a view in my database? Can I do it without touching the database? Thank you,

Read the article
MySQL DDL error creating tables

- by Alexandstein

I am attempting to create tables for a MySQL database, but I am having some syntactical issues. It would seem that syntax checking is behaving differently between tables for some reason. While I've gotten all the other tables to go through, the table, 'stock' doesn't seem to be working, despite seeming to use the same syntax patterns. CREATE TABLE users ( user_id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, username VARCHAR(30) NOT NULL, password CHAR(41) NOT NULL, date_joined DATETIME NOT NULL, funds DOUBLE UNSIGNED NOT NULL, PRIMARY KEY(user_id), UNIQUE KEY(username) ); CREATE TABLE owned_stocks ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id SMALLINT UNSIGNED NOT NULL, paid_price DOUBLE UNSIGNED NOT NULL, quantity MEDIUMINT UNSIGNED NOT NULL, purchase_date DATETIME NOT NULL, PRIMARY KEY(id) ); CREATE TABLE tracking_stocks ( ticker VARCHAR(5) NOT NULL, user_id SMALLINT UNSIGNED NOT NULL, PRIMARY KEY(ticker) ); CREATE TABLE stocks ( ticker VARCHAR(5) NOT NULL, last DOUBLE UNSIGNED NOT NULL, high DOUBLE UNSIGNED NOT NULL, low DOUBLE UNSIGNED NOT NULL, company_name VARCHAR(30) NOT NULL, last_updated INT UNSIGNED NOT NULL, change DOUBLE NOT NULL, percent_change DOUBLE NOT NULL, PRIMARY KEY(ticker) ); Am I just missing a really obvious syntactical issue? ERROR: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'change DOUBLE NOT NULL, percent_change DOUBLE NOT NULL, last DOUBLE' at line 4

Read the article
Join two tables with same # of row but sorted for NULL

- by VISQL

I need to join two tables with the same number of rows. Each table has 1 column. There is NO CONNECTING COLUMN to reference for a join. I need to join them side by side because each table was sorted separately so that numeric values are at the top in descinding order. The Table Earners has income values from say 200K down to 0. I cannot just select using 2 cases, because then I will have my first row with Incomes above 100K, but the first 20 or so entries in the second row are NULL. I want the second row to also be sorted descending. I looked up using ORDER BY within CASE but there is no such thing. I have tried to read about row_number() but none of the examples seem to match or make sense. drop table #20plus select case when Income >= 20000 AND Income < 100000 then Income end as 'mula' into #20plus from Earners order by mula desc drop table #100plus select case when Income >= 100000 then Income end as 'dinero' into #100plus from Earners order by dinero desc Select A.dinero, B.mula FROM #100plus as A JOIN #20plus as B ON A.????? = B.????? Since both A and B are sorted descending, moving all NULL to the bottom, what can I reference to join the two tables? Previous output using one SELECT statement with 2 CASE statements dinero mula 2.12688e+007 NULL 1.80031e+007 NULL 1.92415e+006 NULL … … NULL 93530.7 NULL 91000 NULL 84500 Desired output using one SELECT statement after creating two temp TABLES dinero mula 2.12688e+007 93530.7 1.80031e+007 91000 1.92415e+006 84500 … 82500 NULL 82000 NULL … NULL NULL This is Microsoft SQL Server 2008. I'm super new to this, so please give an answer as clear and simplified as possible. Thank you.

Read the article
SQL 2 INNER JOINS with 3 tables

- by Jelmer Holtes

I've a question about a SQL query.. I'm building a prototype webshop in ASP.NET Visual Studio. Now I'm looking for a solution to view my products. I've build a database in MS Access, it consists of multiple tables. The tables which are important for my question are: Product Productfoto Foto Below you'll see the relations between the tables For me it is important to get three datatypes: Product title, price and image. The product title, and the price are in the Product table. The images are in the Foto table. Because a product can have more than one picture, there is a N - M relation between them. So I've to split it up, I did it in the Productfoto table. So the connection between them is: product.artikelnummer -> productfoto.artikelnummer productfoto.foto_id -> foto.foto_id Then I can read the filename (in the database: foto.bestandnaam) I've created the first inner join, and tested it in Access, this works: SELECT titel, prijs, foto_id FROM Product INNER JOIN Productfoto ON product.artikelnummer = productfoto.artikelnummer But I need another INNER JOIN, how could I create that? I guess something like this (this one will give me an error) SELECT titel, prijs, bestandnaam FROM Product (( INNER JOIN Productfoto ON product.artikelnummer = productfoto.artikkelnummer ) INNER JOIN foto ON productfoto.foto_id = foto.foto_id) Can anyone help me?

Read the article
Joining Tables Based on Foreign Keys

- by maestrojed

I have a table that has a lot of fields that are foreign keys referencing a related table. I am writing a script in PHP that will do the db queries. When I query this table for its data I need to know the values associated with these keys not the key. How do most people go about this? A 101 way to do this would be to query this table for its data including the foreign keys and then query the related tables to get each key's value. This could be a lot of queries (~10). Question 1: I think I could write 1 query with a bunch of joins. Would that be better? This approach also requires the querying script to know which table fields are foreign keys. Since I have many tables like this but all with different fields, this means writing nice generic functions is hard. MySQL InnoDB tables allow for foreign constraints. I know the database has these set up correctly. Question 2: What about the idea of querying the table and identifying what the constraints are and then matching them up using whatever process I decide on from Question 1. I like this idea but never see it being used in code. Makes me think its not a good idea for some reason. I would use something like SHOW CREATE TABLE tbl_name; to find what constraints/relationships exist for that table. Thank you for any suggestions or advice.

Read the article
Properly populating tables in an Object Relational database

- by chaosTechnician

I've got a homework assignment that requires that I use Oracle 10g Express to implement an Object Relational database to track phone billing data. I have a superclass of Communications with subclasses of Call, Text, and Data. I'm hitting a snag with properly populating these tables so that I can find the appropriate data in the various tables. My Types and Tables are declared as such: create type CommunicationType as object ( -- column names here ) not final; create type CallType under CommunicationType ( -- column names here ); create type TextType under CommunicationType ( -- column names here ); create type DataType under CommunicationType ( -- column names here ); create table Communications of CommunicationType ( -- Primary and Foreign key constraints here ); create table Calls of CallType; create table Texts of TextType; create table Datas of DataType; When I try to insert data into one of the subclasses, its entry doesn't appear in the superclass. Likewise if I insert into the superclass, it doesn't show up in the appropriate subclass. For example, insert into Calls values (CallType( -- Values -- )); doesn't show any data in Communications. Nor does insert into Communications values (CallType( -- Values -- )); show anything in Calls. What am I doing wrong?

Read the article
MySQL "OR MATCH" hangs (very slow) on multiple tables

- by Kerry

After learning how to do MySQL Full-Text search, the recommended solution for multiple tables was OR MATCH and then do the other database call. You can see that in my query below. When I do this, it just gets stuck in a "busy" state, and I can't access the MySQL database. SELECT a.`product_id`, a.`name`, a.`slug`, a.`description`, b.`list_price`, b.`price`, c.`image`, c.`swatch`, e.`name` AS industry, MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) AS relevance FROM `products` AS a LEFT JOIN `website_products` AS b ON (a.`product_id` = b.`product_id`) LEFT JOIN ( SELECT `product_id`, `image`, `swatch` FROM `product_images` WHERE `sequence` = 0) AS c ON (a.`product_id` = c.`product_id`) LEFT JOIN `brands` AS d ON (a.`brand_id` = d.`brand_id`) INNER JOIN `industries` AS e ON (a.`industry_id` = e.`industry_id`) WHERE b.`website_id` = %d AND b.`status` = %d AND b.`active` = %d AND MATCH( a.`name`, a.`sku`, a.`description` ) AGAINST ( '%s' IN BOOLEAN MODE ) OR MATCH ( d.`name` ) AGAINST ( '%s' IN BOOLEAN MODE ) GROUP BY a.`product_id` ORDER BY relevance DESC LIMIT 0, 9 Any help would be greatly appreciated. EDIT All the tables involved are MyISAM, utf8_general_ci. Here's the EXPLAIN SELECT statement: id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY a ALL NULL NULL NULL NULL 16076 Using temporary; Using filesort 1 PRIMARY b ref product_id product_id 4 database.a.product_id 2 1 PRIMARY e eq_ref PRIMARY PRIMARY 4 database.a.industry_id 1 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 23261 1 PRIMARY d eq_ref PRIMARY PRIMARY 4 database.a.brand_id 1 Using where 2 DERIVED product_images ALL NULL NULL NULL NULL 25933 Using where I don't know how to make that look neater -- sorry about that UPDATE it returns the query after 196 seconds (I think correctly). The query without multiple tables takes about .56 seconds (which I know is really slow, we plan on changing to solr or sphinx soon), but 196 seconds?? If we could add a number to the relevance if it was in the brand name ( d.name ), that would also work

Read the article
MySQL get point in time totals from related tables

- by batfastad

Hi everyone We have an order book and invoicing system and I've been tasked with trying to output monthly rolling totals from these tables. But I don't know really where to start with this. I think there's some SQL syntax that I don't even know about yet. I'm familiar with INNER/LEFT/JOINS and GROUP BY etc but grouping by date is confusing since I don't know how to limit the data to only the current date that's being grouped by at that point. I think this will involve joining the tables to themselves or possibly a sub-select. I always thought it best to avoid sub-selects apart from when absolutely necessary. Basically the system has 3 tables orders: order_id, currency, order_stamp orders_lines: order_line_id, invoice_id, order_id, price invoices: invoice_id, invoice_stamp order_stamp and invoice_stamp are UTC unix timestamps stored as integers, not MySQL timestamps. I'm trying to get a listing by year/month showing the total of current unbilled orders (sum of price), at that point in time. Current orders are ones where order_stamp is less than or equal to 00:00 on the 1st of the month. Unbilled orders are ones where invoice_stamp is null or invoice_stamp is greater than 00:00 on the 1st of the month. At that point in time there may not be a related invoice yet and invoice_id might be null. Anyone got any suggestions on what I should join to what and what I need to group by? Cheers, B

Read the article
Merge computed data from two tables back into one of them

- by Tyler McHenry

I have the following situation (as a reduced example). Two tables, Measures1 and Measures2, each of which store an ID, a Weight in grams, and optionally a Volume in fluid onces. (In reality, Measures1 has a good deal of other data that is irrelevant here) Contents of Measures1: +----+----------+--------+ | ID | Weight | Volume | +----+----------+--------+ | 1 | 100.0000 | NULL | | 2 | 200.0000 | NULL | | 3 | 150.0000 | NULL | | 4 | 325.0000 | NULL | +----+----------+--------+ Contents of Measures2: +----+----------+----------+ | ID | Weight | Volume | +----+----------+----------+ | 1 | 75.0000 | 10.0000 | | 2 | 400.0000 | 64.0000 | | 3 | 100.0000 | 22.0000 | | 4 | 500.0000 | 100.0000 | +----+----------+----------+ These tables describe equivalent weights and volumes of a substance. E.g. 10 fluid ounces of substance 1 weighs 75 grams. The IDs are related: ID 1 in Measures1 is the same substance as ID 1 in Measures2. What I want to do is fill in the NULL volumes in Measures1 using the information in Measures2, but keeping the weights from Measures1 (then, ultimately, I can drop the Measures2 table, as it will be redundant). For the sake of simplicity, assume that all volumes in Measures1 are NULL and all volumes in Measures2 are not. I can compute the volumes I want to fill in with the following query: SELECT Measures1.ID, Measures1.Weight, (Measures2.Volume * (Measures1.Weight / Measures2.Weight)) AS DesiredVolume FROM Measures1 JOIN Measures2 ON Measures1.ID = Measures2.ID; Producing: +----+----------+-----------------+ | ID | Weight | DesiredVolume | +----+----------+-----------------+ | 4 | 325.0000 | 65.000000000000 | | 3 | 150.0000 | 33.000000000000 | | 2 | 200.0000 | 32.000000000000 | | 1 | 100.0000 | 13.333333333333 | +----+----------+-----------------+ But I am at a loss for how to actually insert these computed values into the Measures1 table. Preferably, I would like to be able to do it with a single query, rather than writing a script or stored procedure that iterates through every ID in Measures1. But even then I am worried that this might not be possible because the MySQL documentation says that you can't use a table in an UPDATE query and a SELECT subquery at the same time, and I think any solution would need to do that. I know that one workaround might be to create a new table with the results of the above query (also selecting all of the other non-Volume fields in Measures1) and then drop both tables and replace Measures1 with the newly-created table, but I was wondering if there was any better way to do it that I am missing.

Read the article
Advice on setting up a central db with master tables for web apps

- by Dragn1821

I'm starting to write more and more web applications for work. Many of these web applications need to store the same types of data, such as location. I've been thinking that it may be better to create a central db and store these "master" tables there and have each applicaiton access them. I'm not sure how to go about this. Should I create tables in my application's db to copy the data from the master table and store in the app's table (for linking with other app tables using foreign keys)? Should I use something like a web service to read the data from the master table instead of firing up a new db connection in my app? Should I forget this idea and just store the data within my app's db? I would like to have data such as the location central so I can go to one table and add a new location and the next time someone needs to select a location from one of the apps, the new one would be there. I'm using ASP.NET MVC 1.0 to build the web apps and SQL 2005 as the db. Need some advice... Thanks!

Read the article
SQL Binary Microsoft Access - Combining two tables if specific field values are equal

- by Jordan

I am new to Microsoft Access and SQL but have a decent programming background and I believe this problem should be relatively simple. I have two tables that I have imported into Access. I will give you a little context. One table is huge and contains generic, global data. The other table is still big but contains specific, regional data. There is only one common field (or column) between the two tables. Let’s call this common field CF. The other fields in both tables are different. I’ll take you through one iteration of what I need to do. I need to take each CF value in the regional, smaller table and find the common CF value in the larger, global table. After finding the match, I need to take the whole “record” or “row” from the global data and copy it over to the corresponding record in the smaller regional table (This should involve creating the new fields). I need to do this for all CF values in the regional, smaller table. I was recommended to use SQL and a binary search, but I am unfamiliar. Let me know if you have any questions. I appreciate the help!

Read the article
codeigniter differentiate field names in JOINed tables

- by Patrick

Hi, I need to retrieve data from two tables. the first is a list of events, the second is a list of venues. I have a fields with the same name in both tables: events.venue (which is an ID) and venues.venue is the name of the place, say "blues bar". The tables can be joined on events.venue = venues.id. Snippet of my model: $this->db->select('events.*, venues.*'); $this->db->join('venues', 'events.venue = venues.id'); if ($date != 'all') { $this->db->where('date', $date); } if ($keyword) { $this->db->like('description', $keyword); $this->db->or_like('band', $keyword); $this->db->or_like('venue', $keyword); $this->db->or_like('genre', $keyword); } $Q = $this->db->get('events'); if ($Q->num_rows() > 0) { foreach ($Q->result() as $row) { $data[] = $row; } } $Q->free_result(); return $data; Snippet of the View: foreach ($events as $row) { echo "<p>{$row->band} ({$row->genre})<br />"; echo "Playing at: {$row->venue}<br /></p>"; // echoes "blues bar" //more here... } 2 Questions: 1) Why does $row-venue echo venues.venue, instead of events.venue? B) how can I differentiate them? eg. What if I want to echo both events.venue and venues.venue? I can probably do something like "SELECT venues.venue as name_of_the_venue", but how can I do this when I've already selected *?

Read the article
Joining tables with composite keys in a legacy system in hibernate

- by Steve N

Hi, I'm currently trying to create a pair of Hibernate annotated classes to load (read only) from a pair of tables in a legacy system. The legacy system uses a consistent (if somewhat dated) approach to keying tables. The tables I'm attempting to map are as follows: Customer CustomerAddress -------------------------- ---------------------------- customerNumber:string (pk) customerNumber:string (pk_1) name:string sequenceNumber:int (pk_2) street:string postalCode:string I've approached this by creating a CustomerAddress class like this: @Entity @Table(name="CustomerAddress") @IdClass(CustomerAddressKey.class) public class CustomerAddress { @Id @AttributeOverrides({ @AttributeOverride(name = "customerNumber", column = @Column(name="customerNumber")), @AttributeOverride(name = "sequenceNumber", column = @Column(name="sequenceNumber")) }) private String customerNumber; private int sequenceNumber; private String name; private String postalCode; ... } Where the CustomerAddressKey class is a simple Serializable object with the two key fields. The Customer object is then defined as: @Entity @Table(name = "Customer") public class Customer { private String customerNumber; private List<CustomerAddress> addresses = new ArrayList<CustomerAddress>(); private String name; ... } So, my question is: how do I express the OneToMany relationship on the Customer table?

Read the article
FreeText COUNT query on multiple tables is super slow

- by Eric P

I have two tables: **Product** ID Name SKU **Brand** ID Name Product table has about 120K records Brand table has 30K records I need to find count of all the products with name and brand matching a specific keyword. I use freetext 'contains' like this: SELECT count(*) FROM Product inner join Brand on Product.BrandID = Brand.ID WHERE (contains(Product.Name, 'pants') or contains(Brand.Name, 'pants')) This query takes about 17 secs. I rebuilt the FreeText index before running this query. If I only check for Product.Name. They query is less then 1 sec. Same, if I only check the Brand.Name. The issue occurs if I use OR condition. If I switch query to use LIKE: SELECT count(*) FROM Product inner join Brand on Product.BrandID = Brand.ID WHERE Product.Name LIKE '%pants%' or Brand.Name LIKE '%pants%' It takes 1 secs. I read on MSDN that: http://msdn.microsoft.com/en-us/library/ms187787.aspx To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables. So I added an INNER JOINED table to FROM: SELECT count(*) FROM (select Product.Name ProductName, Product.SKU ProductSKU, Brand.Name as BrandName FROM Product inner join Brand on product.BrandID = Brand.ID) as TempTable WHERE contains(TempTable.ProductName, 'pants') or contains(TempTable.BrandName, 'pants') This results in error: Cannot use a CONTAINS or FREETEXT predicate on column 'ProductName' because it is not full-text indexed. So the question is - why OR condition could be causing such as slow query?

Read the article

< Previous Page | 17 18 19 20 21 22 23 24 25 26 27 28 | Next Page >