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)
How to inner join 2 table with primary key by no duplicate data in sqlserver 2008 ?
View 5 RepliesI am having trouble with duplicate records. I tested .distinct() in several areas and nothing helps.
I got the search criteria from the PreviousPage. I then used this criteria to search QueryA.
I did a search using QueryA. I took the results, and put the IDs into an array.
I then did a search using QueryB, that matched the IDs in the array. QueryB is used for the ListView and to page records.
Using test records. QueryA results = 2 records. The array.length = 2. QueryB results = 3. ?
What am I doing wrong ?
Some of my Code:
myResults = (from r in QueryA
select r.ID).Distinct().ToArray();
totalrecords = myResults.Length; // = 2
using (ToHealthEN n = new ToHealthEN())
{
[Code]...
I want to get data from Projects(which have CourseId) and related CourseName from Courses table.
I have written following code:
var projects = from n in db.Projects
join c in db.Courses on n.CourseId equals c.ID
orderby n.Name
select new { Project = n, Course = c };
return View(projects.ToList());
and I get error:
The model item passed into the dictionary is of type 'System.Collections.Generic.List1[<>f__AnonymousType22[ProjectManager.Models.Project,ProjectManager.Models.Course]]', but this dictionary requires a model item of type 'System.Collections.Generic.IEnumerable`1[ProjectManager.Models.Project]'.
What I need to do in Controller and in View to display this data?
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'm trying to achieve an outer join in LINQ but I'm not getting my desired result.There are two relational tables between Fruit_Name and Fruit_Ripe.The Fruit_Ripe table has a boolean field (IsRipe) that I use in my condition.The condition let me show all fruits that is not yet rippen (false) or fruits that isn't inputted yet in Fruit_Ripe table.
Dim fruit_salad = From myFruit In _DataContext.Fruit_Name
Group Join myRipeFruit In _DataContext.Fruit_Ripe On myFruit.fId Equals myRipeFruit.fId Into Group
From fruitty In Group.Where(Function(d) Not d.isRipe =
[code]...
I have a situation where I have 3 tables: StockItem, Office and StockItemPrice. The price for each StockItem can be different for each Office.
StockItem(
D
Name
)
Office(
ID
Name
)
[code]...
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.
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'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 RepliesBackground: I use stored procedures exclusively for an ASP.NET application. I am using a DataReader to load a dataset object.
View 4 RepliesI want to generate 30,000 cards and each card must be duplicate check with database. In my card, there are 2 things. Serial No and CardID. If any card already exists then I generate another card id but with the same serial no.
So how faster way I can generate 30,000 card with duplicate check? Which one I have made application, it takes about 25 minutes to insert.
I have a gridview with delete buttons for each record (I've used AccessDataSource). I have loaded the data in and have many duplicate records.
I just want to use the delete button delete each duplicate record while remains many duplicate records. For example:
I used the following query to load in duplicate records:
SELECT * FROM TableA WHERE ([ControlA] IN (SELECT [ControlA] FROM TableA GROUP BY [ControlA] HAVING (COUNT [ControlA]) > 1)))
ID ControlA PIN# FaceValue Date
delete 76637 128232 1234 5 6/4/2006
delete 72722 128232 1234 5 6/4/2006
delete 76638 234567 2345 10 7/3/2006
delete 72723 234567 2345 10 7/3/2006
What is the query to delete single duplicate record instead of deleting all duplicate records?
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 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..
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 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](
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?
Suppose I have two tables "t1" and "t2"
t1 :
id FirstName Age
1 Joe 22
2 James 33
3 Bart 28
t2:
id LastName
1 Coymer
2 Manes
I can combine the data from the two tables using an inner join.
1 Joe 22 CoyLap
2 James 33 Manes
The problem though is that the person "Bart" with an Id of 3 is left out because he does not have an entry in the second table.So how can I include Bart in the output in such a way that the output might look like this:
1 Joe 22 CoyLap
2 James 33 Manes
3 Bart null null
Or spaces can instead appear for Barts last name and age instead of null.Basically I just need Bart in the output as well even though he has no age and last name.
Im trying to join these 2 Containstables sq lstatements together...but I dont know how..they both Containstables...and have no relation to each other....but I need to search both tables.How do I join these two statements?
Select CompanyName, EmailAddress, Tel_nr1, Tel_nr2, PhysicalAddress, CityName,WebsiteAddress from SalesRep_CompanyNomination as P INNER JOIN CONTAINSTABLE(SalesRep_CompanyNomination, (CompanyName),@CompanyName_searchwords) AS KEY_TBL ON
[code]...
Instead of using cross join for select query, I need to used cross join for insert query. How to do this?
View 10 RepliesI have 2 tables:
Attributes
AtrributeVales
attrId(PK) name
id (PK) attrId(FK) value
1
color 1
1 green
2
size 2
2 small
3 1 red
4 2 medium
I need to build some query that will return something like this:
Color Size
Green small
Red medium