w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML videos Categories
group concat divide two values in different table in mysql
Assuming your table structure as Table1 A01-07-2013 1000 C02-07-2013 2000 C03-07-2013 30000 Table2 A01-07-2013 100 A01-07-2013 55 B02-07-2013 40 B02-07-2013 33 C03-07-2013 50 C03-07-2013 90 The following should work SELECT table1.date, SUM(table1.value) / SUM(table2.value) FROM table1 JOIN table2 ON ( table1.date = table2.date ) GROUP by table1.date Since mysql does not have crosstab, you can then format your query as per your requirements, also note that you would probably need to do a cross join and handle no rows from either of the tables

Categories : Mysql

How can I divide my data table called (my_data2) in two samples
To split my_data2 into two arrays of roughly equal size: N = len(my_data2)//2 learning_sample, test_sample = my_data2[:N], my_data2[N:] For example, import numpy as np from statsmodels.formula.api import logit FNAME2 = "C:/Users/lenovo/Desktop/Nouveau dossier (2)/table.csv" FinalTableau = np.savetxt(FNAME2, my_data[index_to_use], delimiter=",") my_data2 = np.genfromtxt(FNAME2, delimiter=',') # converts my binary data series from (1, 2) to (0,1) my_data2[:, 1] -= 1 # print my_data2 N = len(my_data2)//2 learning_sample, test_sample = my_data2[:N], my_data2[N:] x = learning_sample[:, 1] a = learning_sample[:, 3] # x with values 1 and 2 print x form = 'x ~ a' affair_model = logit(form, learning_sample) affair_result = affair_model.fit() print affair_result.summary() print affair_res

Categories : Python

Why doesn't LOCK TABLES [table] WRITE prevent table reads?
The point of LOCK is so that other sessions do not modify the table while you are using it during your specific session. The reason that you are able to perform the SELECT query is because that's still considered part of the same MySQL session, even if you open up a new window.

Categories : PHP

build dynamic sql insert query to merge multiple tables into single table using mapping table
Tim I dont think you need the mapping table at all I would use a series of UNION queries. One query per source. In the SELECT clause for each of those queries you should have a 1:1 column for each coulmn in your destination table, including mapping the value NULL where that destination column is not applicable to that source. (This is where the mapping really happens) Then your INSERT statement is a straight-forward mapping of all the UNIONED queries into the destination table. Pseudo code INSERT INTO Destination(Col1, Col2, Col3, Col4) SELECT T1.Field1 as Col1 ,NULL as Col2 ,T1.Field7 as Col3 ,T1.Field2 as Col4 FROM FirstSource T1 UNION SELECT T2.Field1 as Col1 ,Some Calulation as Col2 ,T2.Field3 as Col3 ,NULL as Col4 FROM FirstS

Categories : Mysql

PHP/MySQL How to SELECT data from two tables which only first row of second table is selected for one row of first table
Just use a GROUP BY, just make sure to include every column in your GROUP BY that you want to use in your SELECT clause, i.e.: SELECT * FROM table1 AS t1 JOIN table2 AS t2 ON t1.tab1_id=t2.id GROUP BY t1.tab1_id ;

Categories : PHP

How to list all the database's tables in datagridview in winforms, and attach a checkbox to each table to carry out re-indexing of selected table?
I ran your code against one of my databases (with a different stored proc) and it works. Are you getting any exception? Try setting timeout on the connection string to something low and running again. The fact that the code works implies that SQL Server/Network Connectivity/Permissions are an issue, and the timeout is so long that you never get an exception back. Some things to try: Does the stored proc execute ok on the server? If so, how much data is it returning? Are you returning a ridiculous amount of data and it's taking an age? Does the user supplied by the connection string have permissions to execute the stored proc? Can you make a connection to the SQL Server using SQL Management Studio using the same credentials?

Categories : C#

How to Inner JOIN Table A+B and reference TABLE A to multiple other tables
Use the ANSI-92 JOIN syntax instead of the old syntax you are using: SELECT u . * , up . * , pi . * , p . * , c . * FROM user u INNER JOIN player_img pi ON ... INNER JOIN player p ON pi.player_img_player_id = p.player_id INNER JOIN country c ON p.player_country = c.country_id LEFT OUTER JOIN user_play up ON pi.player_img_id = up.user_play_entry_player_img_id WHERE pi.player_img_id IS null AND up.user_play_uid = $this->user_id Things to note: There is no condition in your query to how to JOIN with the table player_img pi with other tables. The condition AND up.user_play_uid = $this->user_id might remove the unmcatched rows coming because of the LEFT JOIN in this case you might need to move that condition into a subquery instead of LEFT OUTER JOIN

Categories : Mysql

MySQL tables better to have data in one table or two table with foreign key
For profile, if the same user can have create different profiles, you should create a different table, otherwise just have them in one table. It looks more organized to have it in different table, but its not necessary. For user_roles, if a user can have multiple roles, have them in different tables, otherwise you can put it in the same table. A single table is slightly faster than multiple tables.

Categories : Mysql

Mysql Join 3 tables and output all record in first table regardless another 2 table record is null
$query="select emp.empId, emp.name, q.leavetype, q.quota, l.date, l.day from employee as emp inner join quota as q on emp.empId = q.empId and q.leavetype = 'sick' inner join leave as l on l.empId = emp.empId and l.leavetype = 'sick'"; or this one will give your desire output select e.id, e.name, q.leavetype, q.quota, le.date, le.day from emp as e inner join quota as q on e.id = q.empId left join emp_leave as le on e.id = le.empId and le.leavetype='sick'

Categories : PHP

Table of contingency tables
You can use this instead: within(as.data.frame(t(combn(rownames(ex), 2)), stringsAsFactors=FALSE), { CvsDp <- mapply(function(i,j)chisq.test(ex[c(i,j),c("C","D")])$p.value,V1,V2) AvsBp <- mapply(function(i,j)chisq.test(ex[c(i,j),c("A","B")])$p.value,V1,V2) }) Result V1 V2 AvsBp CvsDp 1 E F 0.91819969 0.608649273 2 E G 0.43257210 0.790459437 3 E H 0.35865125 0.723319426 4 E I 0.96056413 0.089684835 5 E J 0.01440299 0.002829232 6 F G 0.42498245 0.932706790 7 F H 0.36295550 0.982958067 8 F I 0.96863115 0.068473490 9 F J 0.01958004 0.003022993 10 G H 0.99865918 1.000000000 11 G I 0.35499642 0.102779772 12 G J 0.10703032 0.004604047 13 H I 0.28482657 0.080105009 14 H J 0.12305793 0.003324808 15 I J 0.00951511 0.055983338 E

Categories : R

SQL insert into table from tables
To insert into TableC insert into TableCselect b.id,b.name,b.description,a.date from TableA a, TableB b where a.id = b.id To insert into TableD INSERT INTO TableD (ID,Meta, MetaValue) select id,'price',Price from TableA Union select id,'stock',Stock from TableB

Categories : PHP

Why do I need the third table for many to many mapping ? Why can't I just use two tables?
The third table serves as a junction table that defines the many to many relationship. In your example I assume that a Person can have multiple addresses and an address can belong to multiple People. This relationship cannot be modeled using two tables. You may attempt to simply include a foreign key to the Address in the Person table or a foreign key to Person in the Address table. Each of these mappings would have a one side, meaning one of the particular entities corresponds with many of the other. None of these options achieve the many to many relationship and the use of a third table is required to map the more complex relationship. In order to map as a many to many you need to be able to associate multiple instances of both entities with each other. This is traditionally done

Categories : Java

MySql multiple tables or one big table?
From your description I would definetly recommend going with second approach - splitting the data. You should notice that in the first approach you will have roughly one quarter of the table filled with nulls. About nulls effect you can see for example here The most important thing that will determine which is more efficient is how much you will need only get Photographers or only Models. For me its quite clear that probably often - and because of that, with splitted table it will be better. And maybe if you will meet some need of changes, it will be easier to maintain to change it for only one table (if for example some new Model trait will be added). Think in terms of future normalization

Categories : PHP

Combine 2 SQL Server Tables in one table
You want to do a full outer join: select i.ID, coalesce(i.ItemID, p.ItemId) as ItemId, coalesce(i.Date, p.Date) as Date, i.AccountNo, coalesce(i.Quantity, p.Quantity) as Quantity, p.Type, i.Price from Invoice i full outer join Production p on i.ItemID = p.ItemId and i.Date = p.Date and i.Quantity = p.Quantity When the second table has no match in the first, the coalesce() makes sure that the key columns come from the second table.

Categories : C#

Query two tables by a condition from a third table
You don't specify what the relationship is between the tables. The expression c.type refers to a rows, not to the entire table. So, let me assume that c.type = 1 means "there exists a row where c.type = 1". The solution to this problem is then conditional union all: select a.* from tablea a where exists (select 1 from tablec c where c.type = 1) union all select b.* from tableb b where exists (select 1 from tablec c where c.type = 2) This assumes that the columns are the same in a and b. Otherwise, you need to specify the correct set of columns.

Categories : SQL

How to combine identical tables into one table?
You haven't given much info about your table structure, but you can probably just do a plain old insert from a select, like below. The example would take all records that don't already exist Table2 and Table3, and insert them into Table1. You could do this to merge everything from all your 12 tables into a single table. INSERT INTO Table1 SELECT * FROM Table2 WHERE SomeUniqueKey NOT IN (SELECT SomeUniqueKey FROM Table1) UNION SELECT * FROM Table3 WHERE SomeUniqueKey NOT IN (SELECT SomeUniqueKey FROM Table1) --...

Categories : SQL

Table with hierarchy or multiple tables?
It depends: if nearly each category has a parent, you could add a parent serial as a column. Then your category table will look like +--+----+------+ |ID|Name|Parent| +--+----+------+ The problem with this representation is that, as long the hierarchy is not cyclic, some categories will have no parent. Furthermore a category can only have one parent. Therefore I would suggest using a category_hierarchy table. An additional table: +-----+------+ |Child|Parent| +-----+------+ The disadvantage of this approach is that nearly each category will be repeated. And therefore if nearly all categories have parents, the redundancy will approximately scale with that number. If relations however are quite sparse, one saves space. Furthermore using an intelligent join will prevent the second repr

Categories : Database

How to join two tables showing max() of other table
You may not need the distinct but this should be somewhat close for MySQL. SELECT DISTINCT F.ID , F.FruitName , M.Month , MAX(M.Sold) AS Sold FROM Fruits F JOIN Sales S ON F.ID = S.ID GROUP BY F.ID, F.FruitName, M.Month, M.Sold

Categories : Mysql

Get Table CreateBy attribute from sys.tables
This information isn't collected by default. If the modification you are interested in is fairly recent you can get it from the default trace. Failing that you might be able to determine it from the transaction logs. If you need this information ongoing you could create a DDL trigger to log schema changes.

Categories : SQL

How I can cut html table into separate tables?
That's a good exercise to test JSoup's capacity of dom handling. Below is the snippet you need. The code is pretty much self-explanatory (createElement creates an element and so on), but if you need any clarification let me know: Elements tables = new Elements(); for (Element headerTR : headerRows) { Element tbody = doc.createElement("tbody"); Element firstSiblingTR = headerTR.nextElementSibling(); if (firstSiblingTR != null) { Element secondSiblingTR = firstSiblingTR.nextElementSibling(); tbody.appendChild(firstSiblingTR); if (secondSiblingTR != null) { tbody.appendChild(secondSiblingTR); } } Element table = doc.createElement("table"); table.appendChild(tbody); tables.add(table); } Example usage: public static voi

Categories : Java

If IDs from two different tables are equal, display name from another table
Try this: $sql = "SELECT $prtable.username FROM $tbl_name INNER JOIN $prtable ON ($tbl_name.unique_id = $prtable.unique_id)"; When you call INNER JOIN you are fetching all rows from each table and combining them given the ON condition. For more information, see this: http://www.w3schools.com/sql/sql_join_inner.asp

Categories : PHP

How can I create a pivot table from 3 tables?
Since you are using SQL Server there are several ways that you can pivot the rows of data into columns. You can use an aggregate function with a CASE expression: select r.pkid, r.name, max(case when at.typedescription = 'home' then a.street end) homestreet, max(case when at.typedescription = 'home' then a.number end) homeNumber, max(case when at.typedescription = 'home' then a.zipcode end) homezipcode, max(case when at.typedescription = 'home' then a.location end) homelocation, max(case when at.typedescription = 'work' then a.street end) workstreet, max(case when at.typedescription = 'work' then a.number end) workNumber, max(case when at.typedescription = 'work' then a.zipcode end) workzipcode, max(case when at.typedescription = 'work' then a.location end) worklocation,

Categories : SQL

MySQL: M:N Table Linking Two Tables
A couple points first off: Unless you're manually populating the actorid and movieid fields in the actors and movies tables you should have an auto_increment on those columns. If you're using movies.title to link to anything else, you should add a unique index to that column in the movies table. You should ultimately drop the title column from the actors table. Otherwise you'll either have multiple rows for the same actor name (redundant) or only one movie per actor (unrealistic). For the first method you mentioned, you actually can first create the movie_actor table and then populate the existing data: INSERT INTO movie_actor (movieid, actorid) SELECT movieid, actorid FROM movies INNER JOIN actors ON actors.title=movies.title; For the second, you can use a trigger: delimiter // CR

Categories : Mysql

Query two tables (get max from 2 columns on 2nd table)
To find the top selling car: select top 1 c.name from cars c join sold s on s.CarID = c.ID group by c.Name order by sum(Price * SoldUnits) desc To find the car with the single biggest sale: select top 1 c.name from cars c join sold s on s.CarID = c.ID order by Price * SoldUnits desc

Categories : SQL

Insert two columns from two different tables into another table in SQL
Unless you're doing a cartesian join, you've got a mixture of explicit and implicit syntax. I would pick one or the other (being partial to explicit syntax myself): SELECT a.column, c.column FROM Table a left outer join Table b on a.column = b.column left outer join Table c on b.column = c.column or whatever works for your structure.

Categories : SQL

Delete from one table using join on two tables
you can just just an IN clause (or even = ) with a subquery. delete from patient where doc_id in (select doc_id from doctor where doc_name = 'pardeep'); if you use IN, that mean that your subquery CAN return more than one result (so maybe more than one doc_id). if you use = , your query will fail if the subquery returns more than one result.

Categories : Mysql

SQL query from three tables, using the results from the first table
This looks like a fairly straightforward outer join query: select t1.id, t1.tag, t2.Color, t2.Shade, t2.Tint, t3.Sequence, t3.Length, t3.Width from table1 t1 left join table2 t2 on t1.id = t2.id and t1.tag = t2.tag left join table3 t3 on t1.id = t3.id and t1.tag = t3.tag WHERE t1.[Top Weight] = '22' AND t1.[Bottom Weight] = '44'

Categories : SQL

find only one row from table in foreign with another tables
You say: "i want to select only one image of each gallery." This makes sense. I have no idea how your query relates to this question. You don't specify which database you are using. A good approach is to use row_number() to assign a sequential order to the images within a gallery. The key is to do a random sort. The last piece depends on the database. Here is the SQL Server syntax: select gi.* from (select gi.*, row_number() over (partition by Gallery_Id order by newid()) as seqnum from Gallery_Image gi ) gi where seqnum = 1; EDIT: To get information from the Gallery table, join it in: select gi.* from (select gi.*, row_number() over (partition by Gallery_Id order by newid()) as seqnum from Gallery_Image gi ) gi join Gallery g on gi.Gallery_Id =

Categories : SQL

Find all rows in one table that are in two other tables
try this: SELECT r.name FROM recipes r GROUP BY r.name HAVING COUNT(*) = (SELECT COUNT(*) FROM recipes r2 INNER JOIN stores s ON r2.ingredient = s.ingredient AND s.name = 'wal-mart' WHERE r.name = r2.name) fiddle demo

Categories : Mysql

Rename some Tables to Table Name + DDMMYYYY
Well, first of all, it really sounds like you should be loading this tables on one table and use another column for storing the date. Nevertheless, you could use dynamic SQL for what you want: DECLARE @Date NVARCHAR(8), @Table NVARCHAR(500), @Query NVARCHAR(2000) SELECT @Date = CONVERT(VARCHAR(8),GETDATE(),112) SET @Table = 'TABLE_NAME' SET @Query = 'EXEC sp_rename '''+@Table+''','''+@Table+@Date+'''' EXEC(@Query) Oh, and take a look at this link to read about dynamic SQL on SQL Server.

Categories : Sql Server

Creating a table with data from 2 other tables
Have a try with this INSERT INTO tableNew SELECT B.Filename, A.Kabelnummer, B.bla, B.databla, A.data, A.[more data], A.[even more] FROM tblKabelInfo A INNER JOIN tblName1 B ON A.Kabelnummer=CAST(RIGHT(SUBSTRING( B.Filename,1,LEN(SUBSTRING( B.Filename, 0, PATINDEX('%.%', B.Filename)) + '.') - 1),4) Updated to handle upto your 4 digit in filename. 0001 to 9999 Your Query (EDIT) INSERT INTO tableNew SELECT B.[Filename], A.[Vezelnummer], B.[tblVerlies1_Verlies], B.[tblVerlies2_Verlies], A.[KabelNaam], A.[Van], A.[Naar], A.[VezelLengte], A.[TypeKabel], A.[TypeConnector], A.[AantalConnectoren], A.[AantalLassen] FROM tblKabelInfo A INNER JOIN tbl_GL_850 B ON A.Vezelnummer=CAST(RIGHT(SUBSTRING(B.[Filename],1,LEN(SU

Categories : SQL

Can a MySQL trigger be associated to more than one table or by all tables?
No. The syntax doesn't provide for it. It makes no sense to allow it, because the NEW keyword must refer to a particular row definition. If you have two tables with the same row definition, they should be made into the one table, with another column denoting the difference.

Categories : Mysql

style only first tr in table, without nested tables
I think you cant use another class CSS for chiltable same that: <table class="calc1"> <tr><td>title</td><td>title2</td></tr> <tr> <td> <table class = "calc2"> <tr><td>text</td><td>text</td></tr> <tr><td>text</td><td>text</td></tr> </table> </td> <td> text </td> </tr> <table> CSS: .calc1 tr:first-child { background:red; } table td { border:1px solid #000; padding:10px; } .calc2 tr:first-child { background:blue; }

Categories : HTML

using tables when table is not defined in the config
You can fix your problem if you change your code from: <input type="text" name="id" value="<?= $e[$i][$colnams[$ii]]?>"> to: <input type="text" name="id[]" value="<?= $e[$i][$colnams[$ii]]?>"> After this change, you will receive the values as an array.

Categories : PHP

Convert two tables into single table
CREATE value column in first table UPDATE 'value' column by update clause (you can use subselect for selecting value) DROP val_id column DROP second table how big are tables?

Categories : SQL

Merge MySQL tables into one table
With your structure, every SELECT statement over same table, can give different result, especially without primary keys (though even primary keys are not guarantee for ordered result). What you can do is to every row give some ordinal, hoping that in all three tables ordinals will respect initial order, and by those try to join tables.

Categories : Mysql

merge 3 tables in one table;Postgresql
insert into t4 select * from t1 union all select * from t2 union all select * from t3 or, if you want to create table during select: select * into t4 from t1 union all select * from t2 union all select * from t3; SQL FIDDLE EXAMPLE

Categories : Postgresql

Fetching from 3 tables, with one condition on one table
From the information that you have given, the following should work. select order.orderid, order.orderdate, consumers.consumername from order inner join orderproductdetails on orderproductdetails.orderid = order.orderid inner join consumers on consumers.consumerid = order.consumerid where orderproductdetails.fulfilled = 0 I am assuming that you have a 'consumers' table, and that boolean 'false' is represented by the value 0.

Categories : SQL

Retrieving data from two tables at once with one table referencing the other
I don't follow your question very well but this will create the table of results that you want. select table2.* from table2 left join table1 on table2.code = table1.code where table2.Detail1 = 'Yes' or table1.Detail = 'Yes'

Categories : PHP

Update table from results of querying 2 other tables
No plpgsql needed here. For lots of rows at once: UPDATE list l SET state = u.state FROM ( SELECT li.pk_col , CASE WHEN h.hist_date > (now() - interval '1 week') THEN 2 WHEN p.part_number IS NULL THEN 1 ELSE 2 END AS state FROM list li LEFT JOIN part p USING (part_number) LEFT JOIN history h USING (part_number) WHERE li.refNo = <some value> ) u WHERE l.pk_col = u.pk_col -- insert actual pk column AND l.state IS DISTINCT FROM u.state; Or, faster, but a bit more verbose without subquery: UPDATE list l SET state = CASE WHEN h.hist_date > (now() - interval '1 week') THEN 2 WHEN p.part_number IS NULL THEN 1 ELSE 2 END FROM list li LEF

Categories : SQL



© Copyright 2017 w3hello.com Publishing Limited. All rights reserved.