w3hello.com logo
Home PHP C# C++ Android Java Javascript Python IOS SQL HTML Categories
SQL query to join two tables, adding new records if records missing
If the IP_Data_Yearly table is empty, an INSERT with a subquery that aggregates count by IP should do the trick: INSERT INTO IP_Data_Yearly SELECT IP_Address, SUM(Count) FROM ( SELECT IP_Address, Count FROM IP_Data_January UNION ALL SELECT IP_Address, Count FROM IP_Data_February ) IPCombined GROUP BY IP_Address

Categories : Mysql

Preferred method of adding records to custom tables in Umbraco?
There are two answers to this question: Use a framework/solution like EF; Use Contour If you haven't looked at it, Contour is an Umbraco plugin that does exactly what you need. See here for more information about Contour. However, for more control I use PetaPoco or more recently NPoco (via NuGet) and also Autofac to inject the Database (analogous to DataContext) into the constructor of my Controllers. This is super easy and if you aren't already using an IoC container like Autofac in your builds I would highly recommend looking into it. Especially if you stick with EF, as you could ensure that a single DataContext object was created and disposed for each request, making sure that you didn't have multiple contexts floating about.

Categories : Asp Net Mvc

Fastest way to SQL select records based on values of multiple related records in another table (one-to-many) in MS Access?
The key is to use HAVING to limit you records rather than WHERE. Assuming CompletedTimestamp is NULL where the order is not completed you can use this: SELECT m.OrderID, m.Owner, MAX(r.CompletedTimestamp) AS CompletedDate FROM [Main Table] m INNER JOIN [Related Table] r ON r.OrderID = m.OrderID GROUP BY m.OrderID, m.Owner HAVING COUNT(r.CompletedTimeStamp) = COUNT(*) AND MAX(r.CompletedTimestamp) < '20130821'; Since COUNT(*) will count all records, and COUNT(CompletedTimestamp) will count non null CompletedTimestamps (because COUNT(NULL) = 0), this will only return orders where all related records are compelted. Finally the statement MAX(CompletedTimestamp) < '20130821' will ensure only orders with a max completed date before the specified date are r

Categories : SQL

MS Access query to toggle between records with true and all records
Add the value of your control as a column inside the query: FilterControl1: [Forms]![myForm]![chkActive] and then set the criteria for this column to In ([Active], false). The expression is true if either the control and the column match or if false is selected. false works as the all selector. This may look a bit cryptical, but it also works well for other data types. Say you have a combobox with IDs and added ID = -1, Text ='<all>' with a UNION in the query. Then ColumnN: [ControlName], Criteria: IN ([column], -1) works alike to filter either the selection or -1 for all. I can also confirm that the filter does not work on loading. I guess that's just Access. A Me.Requery in Form_Load seems to work ok.

Categories : Ms Access

SQL Query to find missing records between two tables and then update the second with the missing records from the first
That should be smth looking like: insert into table2 (qid, sid ...) select t1.qid, t1.sid, ... from table1 t1 where not exist (select t2.qid, t2.sid, ... from table2 t2 where t2.qid = t1.qid and t2.sid = t1.sid...)

Categories : Mysql

add records in two different tables
To answer your question: "why is this happen?" Because you are not inserting any data in join_table and therefore it wont do it for you. Solution: You need whats called lastInsertId to be returned once you insert a record in data table. Here is an example in PDO and I would recommend using PDO: //prepare statement $stmt_data = $this->db->prepare(' INSERT INTO data(`firstname`,`lastname`) VALUES (:fname, :lname) '); //bind parameter/variables $stmt_data->bindParam(':fname', $firstName, PDO::PARAM_STR); $stmt_data->bindParam(':lname', $lastName, PDO::PARAM_STR); //insert row $res = $stmt_data->execute(); //get last isnerted ID if ($res) { $id = $this->db->lastInsertId(); //NOW INSERT INTO jointable with this

Categories : PHP

Connect tables with or without records
you have to specify columns on which you want to join these tables select case when p.rateper <= 0 or p.rateper is null then u.unit1 else p.rateper end from units as u full outer join price as p on p.id = u.??? update I think you need some kind of full outer join. I'm still doesn't completely understand what exactly you want, but try this query: select coalesce(u."Unit1", p."id") from Units as u full outer join (select * from price where "rate per" > 0) as p on p."id" = u."Unit1"; sql fiddle demo

Categories : SQL

Update SQL records with other 2 tables
You can do the following UPDATE: WITH CTE1 AS ( SELECT * FROM Info1Tbl UNION ALL SELECT * FROM Info2Tbl ), CTE2 AS ( SELECT *, RN=ROW_NUMBER() OVER(PARTITION BY phone ORDER BY lastupdated DESC) FROM CTE1 ) UPDATE A SET A.fname = B.fname, A.lname = B.lname, A.address = B.address FROM EmptyTbl A INNER JOIN CTE2 B ON A.phone = B.phone WHERE B.RN = 1; SELECT * FROM EmptyTbl The results: ╔════════╦═════════╦═══════╦════════════════╦════════════╗ ║ CUSTID ║ FNAME ║ LNAME ║ ADDRESS ║ PHONE ║ ╠════════╬═════════╬═══════╬═════════

Categories : Sql Server

merging records from two tables into third using SQL
Assuming table C is empty, then something like this would do the trick INSERT INTO C SELECT A.* FROM A LEFT OUTER JOIN B ON A.ID = B.ID WHERE (A.TIME1 > B.TIME1 OR B.TIME1 IS NULL) UNION SELECT B.* FROM B LEFT OUTER JOIN A ON B.ID = A.ID WHERE (A.TIME1 <= B.TIME1 OR A.ID IS NULL)

Categories : SQL

JOIN with records in two tables
I'd try something like that. Not exactly the result you're expecting though, but I can't seem to figure out what I'm doing wrong. select a.id1, a.name, b.id2 from ( select *, ROW_NUMBER() OVER (ORDER BY id1) % (select COUNT(1) from table2 ) + 1 as mnr from table1 ) a LEFT JOIN (select *, row_number() OVER (ORDER BY id2) as nr from table2) b ON a.mnr = b.nr

Categories : Sql Server

how to group by, and select from two tables, need two records for each given id
You need to remove the WHERE condition that is limiting the results on QuoteObservations.id =1 OR QuoteObservations.id = 2 Here is the revised SQL, with that condition removed. I have also moved the JOIN conditon into the JOIN clause. SELECT QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, max(QuoteObservations.asOfTime) as asOfTime, QuoteObservations.dataProviderId, QuotePoints.quoteType FROM QuoteObservations INNER JOIN QuotePoints ON QuoteObservations.quotePointId = QuotePoints.id WHERE QuotePoints.quoteType in (1,2) group by QuoteObservations.id, QuoteObservations.value, QuoteObservations.quotePointId, QuoteObservations.dataProviderId, QuotePoints

Categories : SQL

CodeIgniter Fetching Records From More Than Two Tables
Try if it works for you: $sql = "select storytags.cover_title from storytags, storytag_invitations where ( storytags.id != storytag_invitations.storytag_id and storytags.user_id != storytag_invitations.user_id ) and storytag_invitations.user_id = 3"; $rs = $this->db->query($sql);

Categories : Mysql

show only duplicate records from 2 tables
You can extend your where clause with something like the following: AND t2.meta_value IN (SELECT meta_value FROM wp_postmeta GROUP BY meta_value HAVING COUNT(meta_value) > 1) Caveat: I don't have access to a MySQL DB at the moment, so this is the Trnas-SQL syntax which should be similar if not identical

Categories : Mysql

Joining tables without getting the unmatched records
A LEFT JOIN keeps the unmatched result from the first table: SELECT one.id AS id, one.tag AS tag FROM one LEFT JOIN two ON one.id = two.id WHERE one.tag = two.tag OR two.tag IS NULL; The first condition one.tag = two.tag gets the matching result; the second two.tag IS NULL gets what are available in table one but not two. Checkout the demo here. Let me know if it works.

Categories : Mysql

selecting records from tables using HQL Query..........@
Selected column must be appear before from So, change your Query from Query query=session.createQuery ("from b.Customer_name PurchaseDetailBean p,BookingBean b where p.Booking_Id=b.Booking_Id "); to Query query=session.createQuery ("b.Customer_name from PurchaseDetailBean p,BookingBean b where p.Booking_Id=b.Booking_Id ");

Categories : Misc

Can I select a list of tables that have records in SSMS?
Try this: CREATE TABLE #tab_info ( [name] NVARCHAR(128), [rows] CHAR(11), reserved VARCHAR(18), data VARCHAR(18), index_size VARCHAR(18), unused VARCHAR(18) ) INSERT #tab_info EXEC sp_msForEachTable 'EXEC sp_spaceused ''?''' SELECT * FROM #tab_info WHERE [rows] > 0

Categories : Sql Server

Show records from two tables where columns is equal
I have EID's (2,3,4) under my account select distinct B.A, B.B, A.EID from TableB B inner join TableA A on A.EID=B.DEP where A.EID in (2,3,4) SQL FIDDLE

Categories : Mysql

Retrieving Records from Multiple Tables MySQL
You should look into Sql Joins. They are what you need to join the results of two tables.

Categories : PHP

how to insert update and delete records in tables
You can create a stored procedure for that. In order to create a stored procedure, you can use the following SQL query: Create procedure [dbo].[NewUser] @Yourparameter1 int , @Yourparamtere2 As Insert into dbo.Users ( // Db columns Column1 , Column2 ) values ( @Yourparameter1 @Yourparameter2 ) And in your C# code, create a SQLParameter collection and pass it to the procedure. It will work fine and smoothly, and this way your code will be simpler.

Categories : Sql Server

How to find list of tables having no records in SQL server
Try this: SELECT t.NAME AS TableName, p.rows AS RowCounts FROM sys.tables t INNER JOIN sys.partitions p ON t.object_id = p.OBJECT_ID WHERE t.NAME NOT LIKE 'dt%' AND t.is_ms_shipped = 0 AND p.rows = 0 GROUP BY t.Name, p.Rows ORDER BY t.Name The query goes to the sys.tables and other catalog views to find the tables, their indexes and partitions, to find those tables that have a row count of 0.

Categories : Database

mysql win loss records and joining three tables
I think you want to join team to the user, not the game. I expect that user table has a team_id (or team) column which is a foreign key references team(id). I think the only change needed in your query is one line. Just change this: JOIN team AS t ON g.away=t.id OR g.home=t.id to JOIN team AS t ON t.id = u.team That should be sufficient to fix the problem. But I'd write the query a bit differently. Something like this: SELECT u.name , t.name AS team , SUM( CASE WHEN (g.awayScore > g.homeScore AND g.away=t.id) OR (g.homeScore > g.awayScore AND g.home=t.id) THEN 1 ELSE 0 END ) AS wins , SUM( CASE WHEN (g.awayScore < g.homeScore AND g.away=t.id) OR (g.homeScore < g.awayScore AND g.home=t.i

Categories : Mysql

Link records in two tables without extra SubmitChanges
If you have a mapping between the Item and SubItems tables you only need to link it like that var yourItem = new Item(); ... var subItem = new SubItem(); ... yourItem.SubItems.Add(subItem ); dataContext.Items.Add(yourItem); dataContext.SubmitChanges();

Categories : C#

SQL Statement to pull records from multiple tables
If you have a fixed set of values in each of the Name fields, then you can hard-code them into columns, so you get 1 row per contact with columns representing each 'flavour' of reference data. Something like below (I haven't added it for the 4 phone numbers as you didn't supply the possible values for Name in that case, but you should be able to get the drift): SELECT [Contacts].[ID] ,[Users].[LoginName] ,[Contacts].[JobTitle] ,[Contacts].[Company] ,[Contacts].[WebPageAddress] ,[Contacts].[FirstName] ,[Contacts].[LastName] ,email1.[Address] as email1Address ,email2.[Address] as email2Address ,Address1.[Address1] as HomeAddress1 ,Address1.[Town] as HomeAddressTown ,Address1.[County] as HomeAddressCounty ,Address1.[Country] as HomeAddressCountry ,Address1.[Postcode

Categories : Mysql

Deleting records from Master- Details tables
Change your first delete statement to: DELETE FROM Landings.LandingDetails WHERE LandingId IN ( SELECT Landings.LandingId FROM Landings.LandingHeaders WHERE Landings.LandingHeaders.LandingDate1 <= @cutoffdate )

Categories : SQL

Get ID pairs between 2 tables with matching child records
You can try following query also - SELECT t_2.Field_1, t_1.Field_1 --1 FROM table_1 t_1, table_2 t_2 --2 WHERE t_1.Field_2 = t_2.Field_2 --3 GROUP BY t_1.Field_1, t_2.Field_1 --4 HAVING COUNT(*) = (SELECT COUNT(*) --5 FROM Table_1 t_1_1 --6 WHERE t_1_1.Field_1 = t_1.Field_1) --7 AND COUNT(*) = (SELECT COUNT(*) --8 FROM Table_2 t_2_1 --9 WHERE t_2_1.Field_1 =t_2.Field_1) --10 Edit First the requested set of result is the combination of Field1 from both the tables where respective Field2 is exactly same. so for that you can

Categories : SQL

how to retrieve and loop records from 2 tables in rails2.3
If you just need these two columns, I think you don't need to add additional joins, just add two columns in your array, you have got the transport and vehicle record in the controller. I assume you have "transport_date" in "transport" model and "no" in "vehicle" model: @mem = [] @tran = Transport.find_all_by_month_and_vehicle(date,vehicle) tran.each do |t| @mem << [Student.find_by_id(t.mem_id), t.transport_date, vehicle.no] if t.mem_type=="Student" @mem << [Employee.find_by_id(t.mem_id), t.transport_date, vehicle.no] if t.mem_type=="Employee" end and change your view: @mem.each do |m| <tr> <td><%= link_to m[0].first_name} %></td> <td > <%= m[0].age %></td> <td id="date"> m[1] </td&

Categories : Mysql

Join two tables and show distinct records
As you are joining on the PART_ID column, the result you are getting is what you would expect to get. For example, there are two C0001 in the LOG-OUT table and these will join to the same C0001 in the PART RETURN table as there is nothing to distringuish the rows in the return table from each other: PART LOG-OUT TABLE -> PART RETURN TABLE C0001 07/29/2013 501 -> C0001 09/04/2013 STORE C0001 10/01/2013 605 -> C0001 09/04/2013 STORE You need another criteria to join on, or you need to join on some more unique identifier in order to achieve the results you are looking for.

Categories : Ms Access

Entity Framework Distinct records from multiple tables
Well this should work without many changes, though i would have wrote it entirely in expressions: var distinctValues = (from a in dataContext.A_Table join b in dataContext.B_Table on a.EmpID equals b.EmpID join c in dataContext.C_Table on b.SomeID equals c.ID where a.IsActive == true && a.ID == id select new NewClass() { ID = c.ID, Name = c.Name }).ToList() .GroupBy(x=>new {ID = x.ID,Name = x.Name}) .Select(x=>new {ID = x.Key.ID,Name = x.Key.Name});

Categories : C#

Selecting unique records from multiple tables in MySQL
Just get the top 1 record from each table, then union the results: SELECT 'outbox', * FROM outbox WHERE `from` = 'userA' ORDER BY timestamp DESC LIMIT 1 UNION ALL SELECT 'inbox', * FROM inbox WHERE `from` = 'userA' ORDER BY timestamp DESC LIMIT 1 Notes: 1) note the backticks around from. FROM is a reserved word in pretty much EVERY sql database under the sun, so your query would never work without barfing with a syntax error 2) note the hardcoded 'inbox' and 'outbox' strings in the two subqueries - they're there to tell you WHICH table the found records came from.

Categories : PHP

Mysql find unmatched records from large two tables
It would be better to left_join 2 tables, so, we take all records from files table and add to them appropriate records from downloads. Filenames from files which have null as a pair from downloads are the ones we need, so, we filter to get only such ones. select f.filename from files as f left join downloads as d on f.filename=d.filename where d.filename is null and f.filename<>'' My implementation is just 2 simple scans: mysql> explain select f.filename from files as f left join downloads as d on f.filename=d.filename where d.filename is null and f.filename<>''; SIMPLE f Using where; Using index; SIMPLE d Using where; Using index; Not exists Original one uses subquery: mysql> explain SELECT * FROM files WHERE filename <> '' AND filename NOT IN

Categories : PHP

Copy/Paste records in Tables that have autonumber => same ID until refresh
You're trying to paste a number into an autonumber field. First of all, can you do this in code? Secondly, do you really NEED to see that autonumber immediately? The table needs to refresh, so whether you press F5 or whether you close the table/query, either one will update the autonumber. The next time you open it, you'll see the new number.

Categories : Ms Access

Count records, different tables, group by, timestamp, in interevals
I'm going to suggest a slightly different format, with the following headings: Year Month BuriedSpice CableCoil The reason is that year/month could have many rows. But the tables going into the query -- even if more than 2 -- are fixed. All SQL queries have a fixed number of columns, so this format makes sense. The following takes this approach using a union all query: select yr, mon, sum(BuriedSpice) as BuriedSplice, sum(CableCoil) as CableCoil from ((select year(UpdatedOn) as yr, month(UpdatedOn) as mon, count(*) as BuriedSplice, 0 as CableCoil from (select UpdatedOn from BuriedSplice group by UpdatedOn having count(*) = 1 ) bs group by year(UpdatedOn), month(UpdatedOn) ) union all (select year(Up

Categories : SQL

Joining multiple Tables in Oracle gives out duplicated records
Distinct will do it but it's not the correct approch. You need to add another join (mr1.caseid = mr3.caseid) because mr2 and mr3 rows must be related to the same row in mr1, otherwise you end up with 2 pairs, onde for each tabled joined to your primary table (mr2). First answer in SO, so forgive me if it wasn't that clear.

Categories : SQL

unable to fetch records from multiple tables using join
Let Yii worry about joining your tables. Your relations looks fine so you should be able to access them directly For example, what does this return? foreach ($rows as $task) { if ( isset($task->prj_user) ) echo $task->prj_user->username . "<br>"; } Or this? this->widget('zii.widgets.grid.CGridView', array( 'dataProvider'=>new CActiveDataProvider('Task'), 'columns'=>array( 'id', 'prj_user.username', 'prj_user.title', 'prj_user.roles', ) )); ->with() is used for eager loading, so at this point you probably don't need it. In fact, unless I misread you completely, you can remove your criteria all together.

Categories : PHP

Fetch all records with child tables which have many to many relationship using LINQ
If you are using entity framework then you can use the .Include() method http://msdn.microsoft.com/en-us/library/bb738708.aspx It's hard to give an example without knowing your actual situation but your query might lok something like this: var data = context.TableA.Include("TableB"); And if you wanted TableC also then... var data = context.TableA.Include("TableB.TableC");

Categories : Mysql

Select MySQL records from multiple tables and sorting
I think you can achieve this with an INNER JOIN query SELECT c.id, c.caption FROM customers a INNER JOIN details b ON a.customers_ID = b.customers_ID INNER JOIN fields c ON b.field_id = c.id WHERE a.progress = ? ORDER BY b.last_name ASC

Categories : PHP

Mysql join two tables with counpe oft the different type of records
You can use a query like this: SELECT campaign.name, SUM(status=1) totalsent, SUM(status=0) totalunsent FROM campaign INNER JOIN sendmail ON campaign.campaignid = sendmail.campaignid GROUP BY campaign.name

Categories : Mysql

WCF SQL adapter issue while inserting the records into a SQL Server 2012 tables
The Root Node Name and Namespace properties you configured on your WCF-SQL Adapter do not correspond to the Root Node Name and Namespace present on the message that you are sending to the Adapter. You might want to check the message you are sending and change the adapter properties accordingly; or map the message to the correct Root Node Name and Namespace.

Categories : Wcf

ASP Classic/MSSQL records displaying in multiple tables, not in rows
Here is how it should be <table border="1"> <thead> <tr> <td>Key Field</td> <td>Server Application</td> <td>Field 3 header</td> <td>Field 4 header</td> <td>Field 5 header</td> <td>Field 6 header</td> <td>Field 7 header</td> </tr> </thead> <tbody> <% Do while not objRec.EOF %> <tr> <td><%=objRec.Fields("keyfield").Value%></td> <td><%=objRec.Fields("server_application").Value%></td> <td><%=objRec.Fields("environment").Value%></div></td> <div align="center"><%=objRec.Fields("ip_address").Value%></td> <td&g

Categories : Sql Server

Entity framework with lambda to insert records into linked tables
Assuming your UI lets a user select an Activity and a Task from select lists (dropdownlists) and that the value returned from each is the relevant ID, you'd create your ActivityTask like so: public void InsertActivityTask(int actid, int taskid) context.activitytasks.AddObject(new ActivityTask { activityID = actid, taskID = taskid }); context.SaveChanges(); }

Categories : C#



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