I have a problem with a sql server query, what am I missing - why doesn't it work? This is what I want to retrieve:
tblMeetings: ID, subject, description
tblOccasion: dateStart, dateStop
WHERE Meetings.status = 'bokad'
AND Meeting.ID = Occasion.meetingID
Below is two alternatives for the same result, with different error messages:
[Code]....
[dbo].[Meetings](
To write a join query with or condition. It means a query has two inner join, here it is possible to fetch the records, if one inner join is true and other is false. I got a record when only two join conditions are true.
View 1 Repliesi need one sql query for join two numbers...example:
a=15
b=45
c=a+b that answer i need 1545.. not 60...i dont about that query..
How to make sql query run faster . Below is my query:
SELECT [FieldValue2] = case isdate(FieldValue2) When '1' then
case isdate(FieldValue4) When '0' then
dbo.GetCountagingDay(FieldValue2, getdate(), '') -1
else
'0'
end
else
'0'
end
FROM test
this script work with join and sum
and works good,but i want to make total sum of the result of this query for all rows of sum column
[Code]....
i am working with HQL in Nhibernate (in a C#.net Application )
i tried to implement this query
select distinct Course_Title from Course_Info join CMM_BatchCourse_Map on Fk_Course_Id= Course_Id where Fk_Batch_Id= (select Batch_Id
from CMM_Batch_Info where Batch_Title = 'BSC P1')
(THIS query is correctly in SQL SERVER )
but i tried to implement this in through HQL it gives semantic error
public IList<Course> GetByBatchName(string batchTitle)
{
ICriteria criteria = Session.CreateCriteria<Course>();
Session.CreateQuery("select distinct CourseTitle from Course join CMM_BatchCourse_Map on FkCourseId = CourseId where
Fk_Batch_Id= (select Batch_Id from CMM_Batch_Info where Batch_Title = 'BSC P1')" );
return criteria.List<Course>();
}
HOW TO WRITE THIS QUERY IN HQL ?
The following query uses join operation, i want the same query to execute without using join operation. Is it possible to do so? If yes than how can i do it?select jname, jcode from heardt inner join judge ON heardt.jud1 = jcodThe reason i am trying to do this is because i am using odbc connection for mysql and join operations are not getting executed at all as web page is loading for infinitely long and not giving any output. That is the reason why i want to try without using join operation
View 7 RepliesI ned to run a query with two joins, the second join does not work please help with the correct syntax.
[Code]....
I have this code:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
[code]...
i have two queries they are:
query1:
select CustID,NoOfChits from tblCust1 where [Group]='A' and CustID='a001'
query2:
select count(TKCustID) as Taken from tblAuctionGroup where [Group]='A' and TKCustID='a001'
Results of 1st query was:
----------
CustID NoOfChits
a001 2
Results of 2nd query was:
----------
Taken
4
But i want to like below:
--------------------------
CustID NoOfChits Taken
a001 2 4
in the above queries, 1st query displayed two columns and 2nd query displayed one column. but i would like to append the both results in single table or single record, i mean all results should be in same row in same result.
We have two related tables (inventory, inventoryLocalization) and a t-sql query works on them as follows..
select inv.[code],
coalesce(inL.name, inL2.name) as [name],
coalesce(inL.description, inL2.description) as [description]
from dbo.[inventory] inv
left join dbo.[inventoryLocalization] inL on inv.code = inL.code and inL.language = 'de'
left join dbo.[inventoryLocalization] inL2 on inv.code = inL2.code and inL2.language = 'en'
where inv.[code] like '15.' + '%'
order by inv.[code];
to performance improvement, we will try to use these tables from cache not from RDBMS directly.
for an asp.net 4.0 project, by c#. We've took both tables to chache with a DataSet and we can reach both of them through that DataSet.
DataSet ds = UtilityCache.getCachedDataSet();
"ds.Tables[0]" is inventory Table and "ds.Tables[1]" is inventoryLocalization Table
But how can we convert above T-Sql syntax to linq2Sql. is it possible to use coalesce for select statement and to join on two tables with multiple conditions ?
i have a form that my i use to look up whether or not there is an email address in the database. i wrote a query with this basic structure:
select *
from table A, table B
where table A.email = "email" or table b = "email"
i though that would return all instances that the email occurred in either table. what i am getting back is a bunch of null or other unrelated records from table A with a matching email in table B. the thing is, these two tables primary key to join them. i thought i wouldnt need to do that though in this case.
here is the actually query i am using if it makes things more clear:
select lm.cemailaddr, lm.b1emailadd, b.emailaddr
I have this code in my controller:
public ActionResult Index()
{
MembershipUser currentUser = Membership.GetUser();
Guid UserId = (Guid)currentUser.ProviderUserKey;
using (var db = new MatchGamingEntities())
{
var MyAccount = from m in db.Accounts..........
I want to be able to do a join query between my two tables Accounts and BankTransactions. This is a one to many relationship, there can be multiple BankTransactions per Account. I want to query this and display the account information including all the bank statements that are associated with it. I did a join in order to get it, but I am having trouble handling the model. I keep getting this error:
LINQ to Entities does not recognize the method 'System.Collections.Generic.List1[MatchGaming.Models.BankTransaction] ToList[BankTransaction](System.Collections.Generic.IEnumerable1[MatchGaming.Models.BankTransaction])' method, and this method cannot be translated into a store expression.
I know I'm missing something here but I can't figure out what it is. I've got a query joining three tables....accounts, payments, and a table linking the two (there is a M:M relationship).I'm trying to pull a list of all accounts in the account table that have a payment that needs to be resequenced, and also the maximum payment priority if there are any payments that haven't been fully paid. If all payments HAVE been fully paid, I want to return a 0.
It's that last bit of logic that I can't get right. If I include that in the where clause, I get only the accounts that have a payment that hasn't been fully paid. If I take it out, I get all the accounts I get, but I get the highest payment priority whether or not the payment has been fully met.
Here is the query....how do I include the where clause criteria but still include all accounts?
select distinct
bat.acct_id,
isnull(max(isnull(crt.pymt_priority, 0)), 0)[code].....
[Code]....
Well I'm not familiar with SP's yet, and I can't find a single example of what I want to do. basically I want to retrieve the town & county where the customers id are equal (note there will only ever be 1 result)
I can't figure out how to address the results to be stored within the output variables.
I'm trying to build a linq2Entity/gridview function for a forum, that draws data from multible tables.The result should be: Get the latest 10 threads that user(xx) has replyed in, that is not disabled or has a subject that is not allowed.Include information on the last post, thread owner user, and last post user, in each thread.
ere is my code... is returns the correct threads and some that should not be there.
[Code]....
I have some text files I need to parse in order to display my data, and what I have now are two text files with lots of redundant data. Instead of this I would like to replace the redundant data with a number referencing the text in another text file.
View 1 RepliesFor context: First of all, I am new to LINQ, as I have been using SubSonic for quite some time now.
I have two tables, Users, and Affiliates. They both have a very typical schema. The FK that joins them is the UserId field, which is in the Affiliates table. I want to create a LINQ query that pulls the Username from the Users table using the AffiliateId value. The AffiliateId is a primary key of the Affiliates table.
I have tried to accomplish this using many variations of the following code:
[Code]....
In the above query, I expect to get a single row result set. However, I instead receive the entire table of results.
How can I make this work? I have yet to see an example or article out there to do what I am trying to do.
I have a foreach loop which finds checked checkboxes and later I need to make SQL-expression like "id=2, id=3, id=5, id=10" for where clause.
So, using PHP I would add IDs to array, which is not fixed where and later I would use function implode (Join in .NET).
But for the method String.Join I need an array, which I can't do becouse of I don't know the number of checked checkboxes.
TABLE 1
SELECT I.STUNA
,S.SNO
,SUM(B.AMT * Y.YEAR) AS AMT
FROM STUDENT S
LEFT JOIN INFO I ON S.SNO = I.SNO
LEFT JOIN BONUS B ON S.SNO = B.SSNO
LEFT JOIN YEAR Y ON S.SNO = Y.SSNO
STUNA SNO AMT
JOHN A 10
LISA B 20
ALLEN C 100
TABLE 2
SNO AMT
A 1
B 2
C 3
D 5
I WANT TO HAVE RESULT LIKE THIS (TABLE1 + TABLE2)
STUNA SNO AMT
JOHN A 11
LISA B 22
ALLEN C 103
EDDIE D 5
I TRIED USE THIS QEURY
SELECT I.STUNA
,S.SNO
,SUM(B.AMT) AS AMT
FROM STUDENT S
LEFT JOIN INFO I ON S.SNO = I.SNO
LEFT JOIN BONUS B ON S.SNO = B.SSNO
LEFT JOIN
(
SELECT SNO, SUM(AMT) AS AMT
FROM TABLE2
GROUP BY SNO
) T2 ON S.SNO = T2.SNO
GROUP BY I.STUNA,S.SNO
THE RESULT IS
STUNA SNO AMT
JOHN A 11
LISA B 22
ALLEN C 103
THE "EDDIE" WAS MISS. ALSO I TRY USE CROSS JOIN... THE RESULT IS STILL NOT CORRECT.
I have a book table and a author table which contains book id for the book tbl and author id for the author tbl.
View 29 RepliesI have my data split in 3 tables for cascading dropdownlists.
How can I make an update statement of this select statement?
[Code]....
[Code]....
i need to join two tables in different databases, how it can be done??? and if i want to use sqldatasource control to bind results to some data-bound control how can I configure my sqldatasource control???
View 3 RepliesI have faced the peculiar case in Sql server's Inner join. I have 2 tablesFor example:Table Name: A and B I have shown the below code.
Select
* From A
Inner
Join B
ON A.JobCode
=B.JobLookupID
This select statement return 500000 records but both table did not have that much recordsI have shown the Records in every table:A=19396 and B= 25366 records.How it will return more then 5 lakh records in above select statemenet?. Please let me know if any one faced this issue earlier?
I have table with 'usercard' name , and a related tables :
- Computer
- Laptop
- Fax
- UPS
Now , Every Employee has one or more than one from these devices , When I used INNER JOIN in my select query , I didn't get what I need
the result show a one user in 4 rows,(user have all devices)