Databases :: SQL Joins - Getting Duplicate Contact ID
Jun 24, 2010
This query should not fetch duplicate contactID(i.e Same ContactID).but when i execute the query i get duplicated ContactID.I know it is a problem with Joins. Could anyone tell me what is wrong with my Query,
Here is my query
select ContactTracking.Contact_ID
FROM
ContactTracking ContactTracking
LEFT OUTER JOIN Issues Issues ON ContactTracking.Issue_ID = Issues.Issue_ID
LEFT OUTER JOIN HRA hra ON hra.HcnID = ContactTracking.HcnID
INNER JOIN HPS_COMMON.dbo.wrk_user wrk_User ON ContactTracking.UserID = wrk_User.id_User
INNER JOIN HPS_COMMON.dbo.def_Usertype def_Usertype ON wrk_User.id_UserType = def_Usertype.id_UserType
WHERE
ContactTracking.contact_date>'10-jan-2009'
AND ContactTracking.Call_end_time is not null
AND Contact_ID in ('122734','122738')
I am making a simple contact us page for myslef as whomever wants to contact me can contact me on my gmail or at yahoo.com my
[Code]...
[IMG]http://i40.tinypic.com/2yufwhl.jpg[/IMG]Whenever i run this code it gives me the following error [IMG]http://i39.tinypic.com/2roglja.jpg[/IMG]Language-VS2008 ASP.NET(C#)i simply want all these details which are filled by anyone should come to my mail.
now the system always prompts a error message about insert duplicate key into Sybase database.how can i prompt a message box instead of this error message when i insert duplicate key into sybase database table. any code can check what type of error then prompt different alert message.
I have Revision column in Quote table with time stamp (MySql table) now i am viewing the entire date and time my end user want to view only Date he doesnot want to see time how can i pull only date.i am displaying revision column in two ways one in grid view and another one in field value
Query in my Grid view
Dim query As String = "SELECT QuoteNumber,Revision,Vendor,PartNumber,status,Customer,Requestor from quotes"
On select check box in grid view, filed values will be loaded with data.
Is it possible to do an optional join as follows?:
private void OptionalJoin(string phrase) { from t in db.Table // (if phrase != string.Empty then) join t2 in db.Table2 on t.Id equals t2.Id select t }
Although I'm aware that I can copy/paste this and move the conditional logic outside the query, the query is ~200 lines and there are 4 different optional joins I would like to incorporate, so it would get messy and be difficult to maintain. I'm looking for a clean, streamlined way to do this.
I have two tables.[Table.Game] Columns are "PK_id" "username" and "couponID" [Table.Coupons] Columns are "PK_id" "CouponID" and "Points" The two columns "CouponID" are associated with eachother. Let say i have two rows with the user "harry" in [Table.Game] this person has two different couponID. In [Table.Coupons] this user "harry" has "CouponID" 1 and 2. Column "Points" have 10 and 20. To the question how do u sum this two different point values that have different "CouponIDs". This does work if i have only one "CouponId". But not when the user has 2 different CouponIDs. Values is 0
var points = (from p in linq.Coupons join g in linq.games on p.couponID equals g.couponID where g.username == username && g.couponID == p.couponID select (int)p.win).Sum();
I have a select statement. I need to get a field value from the first statement. GOAL: Below is what I am trying to accomplish - the code below is not correct - Please modify the statement with the correct SQL Syntax. I want to query a table and based on the results I want to set the "If THE Else"statement to query then table with diffent inner joins.
SELECT * FROM CONTENTITEM CI INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID IF CI.FORMTYPE = 200 OR 300 SELECT * FROM CONTENTITEM CI INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID INNER JOIN CONTENTFORMFIELD CF ON CF.ITEMID = CI.ITEMID WHERE CI.ITEMID = @ITEMID ELSE SELECT * FROM CONTENTITEM CI INNER JOIN CONTENTCATEGORY CC ON CI.CATEGORYID = CC.CATEGORYID WHERE CI.ITEMID = @ITEMID
I have a SQL select statement with inner joins to get a total number of records for the statement. I have tried using count with group by's but it just counts one for each record instead of giving me the total number of records for the query. Here is the query:
I have one question regarding how to make the below mentioned type of query more optimized so that mine query perfomace can be increased.
Select a.name1,a.address1,b.field3,c.field4,d.field6,e.field7 from a inner join b on a.id=b.id inner join c on c.id=b.id inner join d on d.id=c.id iner join e on e.id=d.id union all Select a1.name1,a1.address1,b1.field3,c.field4,d.field6,e.field7 from a1 inner join b1 on a1.id=b1.id inner join c on c.id=b1.id inner join d on d.id=c.id iner join e on e.id=d.id union all Select a2.name1,a2.address1,b2.field3,c.field4,d.field6,e.field7 from a2 inner join b2 on a2.id=b2.id inner join c on c.id=b2.id inner join d on d.id=c.id iner join e on e.id=d.id
All the above three select query at last using the same inner join condition, means last two inner join condition are the same for all the select query.
Is there any way so that this query can be optimized?
Inner Join:- Returns a row when there is matching table in both tables. we can use comparison operator like =,<,>,<>
I created two tables
TableA
ID OID Type
1 1 A 2 6 B 3 7 C 4 10 D
TableB
ID Value 1 10 2 30 3 40 5 60 6 70
Basic Inner Join select Type From TableB as a inner join TableA as b on a.ID=b.OID Result Type
A B
If i want only the type which has not match in tableB then i can use not in subquery i get the result if i m using select Type From TableB as a inner join TableA as b on a.ID<>b.OID then i get
I have a web service with a dataset and I need to fill a gridview in my web page with data from multiple tables (like 4). How can I retrieve data from my 4 tables and construct a datatable with a table adapter in the dataset? I need to create this in the web service to bind the gridview in my page. In SQL I create a view with the joins of the 4 tables and added the view to the dataset. It works fine, but for other windows in my web page I need to use like 8 trough 10 tables (just to retrieve one field of each one) and I don't know if making that huge joins is the more efficient way to retrieve data from the server. There is another way to make joins? (Make separate queries for each table and create one table in memory) How?
One of my peer wrote a query based on multiple tables using joins. One of the table is in a different database The query was taking much time
1. Create an SP 2. Create a temp table and copy all records from the external db 3. Avoid using Left Join as much as possible and use Inner Join 4. Create and use indexes 5. Remove tables/columns that are not necessary
In this scenario, I would like to suggest to use Covering Index, but how can it be created for multiple tables and a temp table?
I have a web page with a GridView and a table outside the gridview to display more information of the record when it is selected (like record details). I'm using data of five tables for this particularly window. I use one table of the five to bind the gridview the other tables are for the details, but I use like 1 or 2 fields of those tables. I'm binding the page with web services. I need some advice on which is the better way for doing this to make a faster web page.
In sql server I made a view with the five tables (all the tables relations are working perfectly). In the web service I call the view; and in the web page I call the web service to bind the page. Everything is working. All I want to do is retrieve from the server all the necessary data only once when the page loads and bind the five tables data (like 18 fields total) in the gridview which I only show 12 fields the other 6 fields are hidden, those 6 are for the details which I get with jQuery (that's another topic). Bind everything only once and manage the data on client side. Everything is working perfectly. Here is my doubt.
How are the maximum table joins I can make? To retrieve the data via web and not get a server time out error. Which is a better way to make a faster page? Make 2 views one for the gridview and one for the details and put each view in a web service; or make one view with all the table joins and one web service. Right now all is working but I have to make other pages that use different tables and joins and I don't know if the server gets me the time out error when the page is fully working.
Regarding Linq to SQL with .SQLEXPRESS and VS2010 on Windows 7, I am trying to add a list of entries to an empty table that I have confirmed programmatically does not have duplicate keys. Some info about the situation:Note: The key strings have Unicode characters (English and Chinese mixed).
The gist of the problem is that we have an alumni table (one record per person) and also a couple of other tables (one to many) that have degree info and interest info. In a search screen in our app you can search for criteria that spans all three tables (there are actually more fields and tables than shown in the example below but I am trying to keep it simple).
The code below works (properly returns people without degrees for example) but still feels a little clunky or over-engineered to me. Are there easier ways to do this? NOTE: I have been through quite a few iterations/approaches to making the correct data be returned.
I want to generate "contactus.aspx" using database. I tried gridview,detailview to display all details from contact us table, but its not looks good like any other site's contact us page. What are the other options to display the contact details?
I am trying to add a new text box in a contact form that I use, but I know 0 C#. I know this is probably easy once you know how to do it but I don't. I have posted my code below.
i want to develop a data access layer ,it can support multiple database like oracle ,Mssqlserver and Mysql using enterprise library and C#.net 3.5 based on database connection
