SQL Server :: Get All Data From Tables When Inner Join Is Not Enough?
Sep 28, 2010
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.
View 3 Replies
Similar Messages:
Sep 10, 2010
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.
View 6 Replies
Sep 22, 2010
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 Replies
Jan 13, 2011
I 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
View 17 Replies
Aug 18, 2010
I need to join two tables accross two different databases and return an ADO dataset.
ie: select * from database1.table1 a inner join database2.table2 b on
a.key=b.key where some_condition=true
My main problem is how to specify the SqlCommand parameters specifically the connection string for both databses. Is this possible?
View 4 Replies
Dec 23, 2010
Just looking through database design and wondering what would be the better option. I have customers, who all have orders, pickings, deliveries and invoices. Now an invoice can't exist without a delivery, a delivery can't exist without a picking and a picking can't exist without an order. So I could set the tables up in a linear fashion. e.g.
Customer -> Orders -> Picking -> Delivery -> Invoice
then in Orders, there would be an ID field, with a customerID field and in Picking, there would be an ID field, with an orderID field and in Delivery, there would be an ID field, with pickingID and in Invoice, there would be an deliveryID field. Linking them altogether, again, in a 'linear' fashion.
The problem is if I want to get, say, all the invoices for a specific customer, my query would be something like
[Code]....
My other option would be to have in a customerID column in each of the tables meaning a much easier query
[Code]....
View 3 Replies
Mar 5, 2011
I have two tables: Projects and ProjectsData and I want to execute query with join and get the result in the View.
In the Controller I have this code:
[Code]....
What I should use in the View to extract this data. I tried that:
[Code]....
but it doesn't work...
View 2 Replies
Jan 29, 2010
I have 2 different datatables. 2 datatables from 2 different servers.
My first datatable is this:
SELECT DISTINCT a.name, a.create_date, a.Modify_Date, a.Object_Id
FROM dbname.sys.objects a
and my 2nd datatable is the same, but from a different server connection string
SELECT DISTINCT b.name, b.create_date, b.Modify_Date, b.Object_Id
FROM dbName.sys.objects b
Basically, I want to compare which procedures are on one server and not the other (dev and prod)
So after I have my 2 datatables i want to create a data relationship on the name and then do a left join to see whats in a and not b.
View 1 Replies
Mar 4, 2011
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?
View 2 Replies
May 25, 2010
I need to retirve data from two tables...when i click a value in dropdown list, i need to retrieve values from two tables based on that id( which i have selected in dropdown)..so when i have written query ( "Select assign_issue.issue_assigned_to, assign_issue.created_on,assign_issue.last_Date,
issue_register.issue_desc,issue_register.issue_Priority from assign_issue INNER JOIN Issue_register ON assign_Issue.Issue_ID = issue_register.Issue_ID where issue_register = '" + ddlissueid.selecteditem.text + "'")..query is executing ..when it comes to dr = cmd.executereader()..Dr is unable to read..it is showing false when i trace..unable to read into while loop.
View 1 Replies
Dec 4, 2010
have table COLLECTIONS and table CLOTHES.As an example, when showing the items on Listview, collection HAWAII
would be written on the top, and its texts and images on the itemTemplate.Then, collection WINTER and its texts and images on the itemTemplate.And so on.P.S: I´m connecting the Listview visually thru SQLDataSource, not programatically.How can i do it ? On the layout template using a JOIN on the SQL Statement ?
View 1 Replies
Jun 10, 2010
I have a particular scenario where I am populating two data tables seperately. These datatables have one field which is unique. What I need is to join these two datatables on the basis of that one field and get one datatable. I do not want to merge, I need to join. And I can not join in SQL before populating the data Tables.
View 7 Replies
Sep 8, 2010
i need join 3 tables using inner join...
View 6 Replies
Jun 23, 2010
if you have two table inner join like SELECT* From A INNER JOIN B on A.ID = B.IDSay A and B tables both have Username values but A's username is Test, and B's username is Test2When I do Eval("Username") it always print out "Test", how can I configure it to print out "Test2"... meaning how to specify which table's value to put using eval.. is there something like Eval(B.("Username"))??
View 6 Replies
Jan 24, 2011
I need to make a category filter. For this purpose I have two tables "Ingredient" and "Category"
My Category Table looks like this:
[Code]....
And my ingredient table:
[Code]....
I want to join the two tables so when the KategoriID match it should then return all from that category.
[Code]....
[Code]....
View 2 Replies
Sep 9, 2010
I was trying to find out how I can join more than 2 tables using the LINQ-to-SQL syntax. For instance, joining 2 tables in SQL:
SELECT * FROM Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1WHERE T2.Key='17';
can be expressed as:
var Result = from T1 in DbContext.Table1 join T2 in DbContext.Table2 on T1.Key equals T2.ForeignKey where T2.Key=17 select new { T1, T2 };
But how would I join 3 or more tables using LINQ? For example:
SELECT * FROM (Table1 AS T1 INNER JOIN Table2 AS T2 ON T1.Key=T2.Column1) INNER JOIN Table3 AS T3 ON T3.Key=T2.Column2 WHERE T2.Key='37';
I've been searching and experimenting and I cannot seem to find any informraiton on this. One example I found involves putting the result for the frist join into a temp object, and then performing the second join. I'm not sure performance-wise if that's the same as doing a 3-table join directly using a single SQL statement.
View 4 Replies
Nov 23, 2010
I have a scenario, I will pass a StatusID , According to that StatusID , The rows will be fetched from a table[tblEventStatus] and newly fetched row has to fecth the data from another table [tblEvent].
Ex: StatusID - Value I am Passing
Table 1: tblEventStatus - Column:SysID - StatusId - EventID
Table 2: tblEvent - Column:SysID - EventID - EventDesc
I want to use the Linq - I tried like this, But It doesnt work
var query1 = from objtblEventStatus in db.tblEventStatus
where objtblEventStatus.StatusId== StatusId
join objev_events in db.tblEvent on objtblEventStatus.EventID equals
(objev_events == null ? null : objev_events.UID)
select new { Category = objev_events.EventID , Name = objev_events.EventDesc};
View 1 Replies
Dec 9, 2010
I have T_articles to save the articles.
each article has authorCode , and categoryCode.
each article can be modified by the author several times.
and I save every modification in the T_articles in a difference row.
now I want to dislpay for each autor:
author name, article name , article category's name, last modified date
In SQL I should to it like that:
[Code]....
I try to write it in Linq so:
[Code]....
so I got error:
[Code]....
and again I got an error:
[Code]....
View 4 Replies
Nov 9, 2010
Here's my DB structure:
I have three tables.
User, Details, Optional
User has fields: ID, FirstName, LastName
Details has fields: ID, MyDetails1, MyDetails2, User_ID
Optional has fields: ID, MyOptions1, MyOptions2, User_ID
There is a 1 to many relationship with User & Detail (User ID & Detail User_ID)
There is a 1 to many relationship with User & Optional (User ID & Optional User_ID)
With that said, one user can have many details and/or many optionals
Now I'm trying to build a linq query that will say:
"where user id = x, select FirstName, LastName, all MyDetails1 from every record where User_ID == x, all MyOptions1 from every record where User_ID == x"
After the merge I should have one string that lists the distict user & all of their many fields:
FirstName + LastName + (MyDetails1 + MyDetails1 + MyDetails1..etc) + (MyOptions1 + MyOptions1)
Anyone try this before?
I tried this with JOIN but this doesn't do the trick for what I'm looking for.
So if I had 3 MyDetails records associated with ID == 2, I would get three results like:
John Doe MyDetails_X
John Doe MyDetails_Y
John Doe MyDetails_Z
View 1 Replies
Jan 8, 2011
The main problem is that I recieve the following message:
"base {System.SystemException} = {"Unable to create a constant value of type 'BokButik1.Models.Book-Author'. Only primitive types ('such as Int32, String, and Guid') are supported in this context."}"
based on this LinQ code:
[Code]....
How shall I solve this problem to get a class instance that contain with property title and Book-AuthorID?I also have tried making some dummy by using "allbooks" relation with Code Samples from the address
http://www.hookedonlinq.com/JoinOperator.ashx. Unfortunately, still same problem.
I also have taken account to Int32 due to entity framework http://msdn.microsoft.com/en-us/library/bb896317.aspx. Unfortunatley, still same problem.
Using database with 3 tables and one of them is a many to many relationship. This database is used in relation with entity framework
Book-Author
Book-Author (int)
BookID (int)[code]....
View 1 Replies
Aug 3, 2010
I am new to MVC and am trying to join 2 SQL tables together and return them in a Json. One table is OutreachProgram. The other table is UserInfo. I want to return the rows from the OutreachProgram table that match on CenterID with the UserInfo table.
The code that exists now will pull all rows from the OutreachProgram table. I can't figure out how to add the UserInfo table to the query to make it work.
JsonGrid <OutreachProgram> grid =
new
JsonGrid<OutreachProgram>(db.OutreachProgram,
new
OutreachProgramFilter());
public Array GetItems(IQueryable<OutreachProgram>
source)
{ return (from r
in source.AsEnumerable()
select
new {
ID = r.OutreachProgramID, CenterID = r.CenterID, DateOutreachProvided = r.DateOutreachProvided.ToShortDateString(),
NameOfProvider = r.NameofProvider.ToString(),
}).ToArray();
}
View 2 Replies
Nov 19, 2010
I have a gridview showing values from two tables accommodation and pic (left join on accommodation). If there is no pic uploaded I want to display the row without the pic. So in the gridview I first check to see if the picid is null, if it is i don't render any 'a href' code , if it's isn't i do.
The problem that i'm having is that instead of the image showing up in the gridview itemtemplate i get
','_blank','toolbar=no,menubar=no'))" >
Here is my .aspx code
SelectCommand="SELECT pic.picid, aspnet_Users.Username, accommodation.location, accommodation.type,accommodation.description, convert(varchar,accommodation.createdate,101) as createdate
View 2 Replies
May 20, 2010
I have a Winform that has fields need to be filled by a user. All the fields doesn't belong to one table, the data will go to Customer table and CustomerPhone table, so i decided to do multiple inserts. I will insert appropriate data to CustomerPhone first then Insert the rest data to Customer table.
View 3 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
Aug 23, 2010
I have a select query which is executing well. Now, I want to add one more field to that query. That field is not in the current query table, It is in the another table.
How do I join those two tables and get that field value in the existing select query.?
View 5 Replies
