| 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
|
| 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
|
| 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.
|
| 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
|
| 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
;
|
| 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?
|
| 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
|
| 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.
|
| 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'
|
| 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
|
| 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
|
| 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
|
| 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
|
| 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.
|
| 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.
|
| 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)
--...
|
| 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
|
| 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
|
| 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.
|
| 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
|
| 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
|
| 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,
|
| 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
|
| 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
|
| 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.
|
| 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.
|
| 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'
|
| 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 =
|
| 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
|
| 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.
|
| 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
|
| 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.
|
| 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;
}
|
| 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.
|
| 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?
|
| 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.
|
| 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
|
| 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.
|
| 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'
|
| 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
|
