DataSource Controls :: Join Query Or Union?
Jun 30, 2010
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.
View 3 Replies
Similar Messages:
Jan 27, 2010
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
View 4 Replies
Feb 18, 2010
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 Replies
Jun 10, 2010
if there is no relation bettwenn two tables why do i need union for example like below Please
can u inform me
SELECT categoryId
FROM categories
UNION
SELECT shipperId
FROM shippers
View 3 Replies
Apr 11, 2010
in my application i should make an union query thay union 15 tables
and this query begin with 150000 rows and will grow .
i need get just 10 rows from this query
i want make this query as best as possible
2 qustion
-----------------------------------------------------------1------------------------------------------
********View1*******
select id, lang, active from table1
union
select id,lang,active from table2
...--same way to all other 15 tables
*******SP***********
select top 10 from View1 where active=1 and lang='he' order by id desc
or
********View1*******
select id, lang, active from table1 where active=1 and lang='he'
union
select id,lang,active from table2 where active=1 and lang='he'
...--same way to all other 15 tables
*******SP***********
select top 10 from View1 order by id desc
---------------------------------------------2---------------------------------------
becauseof that this query is very complicate i want to know if i need take just 10 from each table
------------------------------------------------3--------------------------------------
if there is an idea how make this query better, i will glad to know.
View 7 Replies
Feb 15, 2010
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].....
View 6 Replies
Apr 8, 2010
[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.
View 2 Replies
May 25, 2010
For 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.
View 9 Replies
Apr 8, 2010
how to sort the records when using union
View 2 Replies
Aug 31, 2010
I need to use Union for below Linq queries. I could use Union it if it was simple "select new whith{" but Now that I Specified Class, I encounter Error using union
[Code]....
View 3 Replies
May 1, 2010
SQL Select Union and total number of returned rows?
View 3 Replies
Aug 4, 2010
I am a .net developer.I am not that strong in SQL.I need a simple query.This is from Northwind Database.
select 'Select All' as CustomerID,'Select All' as City union(select CustomerID, City from Customers
order by City)
i need to get output like 1st row should be Select ALl Select All and next rows should be output of this stat..(select CustomerID, City from Customers order by City)
i am not getting this...i can do by keeping the second query result in temp table and then using union... but i am looking without temp tables or simple query.
View 2 Replies
Jun 1, 2010
I'm new to ASP.net 3.5 and trying to create a test site so I can figure out how to use it in the real world. I have created a FormView (Formview1) based on a Client table using Linq Data Sources. The page includes a DropDownList to lookup Clients. I have added a list option (<asp:ListOption value="" Text="(Select Client)" and set AppendDataBoundItems=true so my added option is displayed. I decided to try to use Formview with only the Edit and Insert Modes, and I have set the Default Mode = Insert. When you open the page, a blank Client record is displayed.
The Client record contains serveral lookup fields and they have drop down lists for each. When I select a client from the Client DropDownList, is get the following message 'ddlClientType' has a SelectedValue which is invalid because it does not exist in the list of items I had this problem before and decided the best way to handle this (in the interim) was to turn off foreign key constraints for each of the lookup fields (e.g. ClientType) in SQL Server. That worked for 2-3 days. Now the problem has started again.
When I develop in other applications, I would simply create a lookup data source based on a Union Query with the actual data and a blank record. How can you do this using Linq DataSources. Can you get access and modify the queries?
View 1 Replies
Feb 25, 2010
let me know the types of joins available in SQL, so far i've used only Inner joins and left joins.
But have been clear and comfortable using inner joins only.
give me a more clear picture on all the types of joins available and their importance.
View 3 Replies
Jul 7, 2010
I have three tables nameTbl contains the following columns: BarID, Name, Description locTbl contains locID and Location namelocationTbl contains BarID(from the nameTbl) and locID(from the locTbl)
View 2 Replies
Oct 26, 2010
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 ?
View 1 Replies
Sep 4, 2010
I have a gridview with a sqldatasource with SelectCommand as below:
[Code]....
but it's not workling.....
View 1 Replies
Apr 27, 2010
I need to join some tables to get the matching records. I often have to ask around to see how certain tables could be joined. Is there any way to query the information schema or some thing else to see what columns/values match in certain tables in order to figure out how tables should be joined.
View 4 Replies
Feb 11, 2010
Anyone know if it's possible to inner join tables using two different connection strings? I'm using vb.
View 3 Replies
Feb 13, 2010
I can join with a regular SQL table fine but this won't work:
SELECT normalTbl.*, tmp.fld1 FROM normalTbl
LEFT JOIN #Temp tmp ON tmp.joinID = normalTbl.joinID
All tmp.fld1 values are shown as NULL even though fld1 contains valid values and the joinID matches the normalTbl (when I call SELECT * from #Temp). If I do the same exact thing with a regular table, fld1 shows the correct value. What am I doing wrong?
View 4 Replies
May 5, 2010
I am trying to update a Single Record in a table. I also trying to join columns of that single record with another table to update prices the prices of that record.
Here is my current query.
[Code]....
View 2 Replies
May 4, 2010
pull data from 3 databases, same query with the same result set (data can vary) and i want to combine those results in to a single datareader so i can easily dump it to a gridview.
View 2 Replies
Feb 12, 2010
I would like to create a gridview where all of the items in one table are listed and a checkbox is checked if that item also shows up in another table. The difficulty comes in the fact that table are not only in different databases, but possibly on different servers.
Is there any way to do this via ASP.NET connection strings, so that it doesn't matter where the databases live? I'd rather use connection strings, because I don't want to hard-code credentials into the queries (OPENROWSET or whatnot).
View 1 Replies
Apr 20, 2010
How to use Left and Right Outer Join in C# using Linq with Eg.
View 2 Replies
Jun 30, 2010
I was wondering in which order MSSQL executes a command. Say if I have this statement:
select top 1 * from table1 t1
inner join table2 t2 on t2.productid = t1.id
order by t1.buydate desc
what is done first:
- find if there's a matching record in t2 through the join
OR
- sort the results in t1 based on the buydate?
And when I have a large database with many records in both t1 and t2, what are possible performance issues I might hit?
View 5 Replies
