SQL Old school *= Question select All rows from table A and rows from B

Im using SQL 2005. Love it.

In the old TSQL world could use

select current.user, weekly.user, weekly.hours

from current, weekly

where current.user *= Weekly.user

 

and get all the current users from current table AND users from weekly table that have weekly hours.

 

Somehitng like

 

dan  12

fred 24

sue null

where sue is in the current table, but not in the weekly table.

I've tried outside joins like this

 

SELECT     dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorked
FROM         dbo.ViewCurrentUser LEFT OUTER JOIN
                      dbo.PS_HR_Hrs ON dbo.ViewCurrentUser.HRDeptName = dbo.PS_HR_Hrs.CSDLong
WHERE     (dbo.PS_HR_Hrs.DateWorked BETWEEN '7/6/08' AND '7/12/08')
GROUP BY dbo.ViewCurrentUser.HRDeptName, dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorked

 

but not much luck.

 

any help is much appreciated.

 

Dan 

0
Harperator
7/14/2008 6:24:15 PM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

5 Replies
871 Views

Similar Articles

[PageSpeed] 38
Get it on Google Play
Get it on Apple App Store

Left Join Syntax: 

select current.user, weekly.user, weekly.hours

from current LEFT JOIN weekly ON current.user = Weekly.user

 

No way to know your Left join sample without further information about the view/table information.

 


Limno

0
limno
7/14/2008 6:54:45 PM

T-riffic, Thanks

Follow up ? if I may?
Difference between outside left join and left join and inside left join?

Thaks a bunch!

Dan

0
Harperator
7/15/2008 2:06:08 AM

Inner Join - returns all matching records from both tables

Left Join - returns all records from the table on the left, along with matches (if any exist)

Right Join - returns all records from the table on the right, along with matches (if any exist)

Both the left and right joins can have the optional keyword 'outer' added, so a left join is the same as a left outer join

 

HTH

 

 


Steve
0
drktrnq
7/15/2008 2:19:59 AM

 

Harperator:
SELECT     dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorked
FROM         dbo.ViewCurrentUser LEFT OUTER JOIN
                      dbo.PS_HR_Hrs ON dbo.ViewCurrentUser.HRDeptName = dbo.PS_HR_Hrs.CSDLong
WHERE     (dbo.PS_HR_Hrs.DateWorked BETWEEN '7/6/08' AND '7/12/08')
GROUP BY dbo.ViewCurrentUser.HRDeptName, dbo.ViewCurrentUser.UserName, dbo.PS_HR_Hrs.DateWorked

It looks like your where clause if causing your the problem.  You've written a condition in your where clause for the right table of your left join query, and that might be limiting the number of rows returned.  This is of course my complete guess on the basis of my experiences with join s in the past, you'll have to provide us with the table/view structures, some sample data and your expected results for us to help you better.

Here is a similar thread.


Thanks,
Dhimant Trivedi
"When the going gets tough, tough gets going."

"Mark as Answer" the post(s) which helped you solve the problem
0
dhimant
7/15/2008 9:11:58 AM

 Thanks All!

DONT know why this was so tough!

Well actually, part of the reason is because I want to add a column to a pivot table, that returns many to one row, but when I broke the code down to one function at a time and removed the pesky where clause it worked like a charm!


Thanks again..
Dan

 

PS here is the code:

 

Select ViewCurrentUser.UserName, PS_HR_HRs.username, hoursworked

from viewCurrentUser left join PS_HR_HRs
on ViewCurrentUser.UserNAme = PS_HR_HRs.username
where hrdeptname ='dean of students' 

0
Harperator
7/15/2008 12:26:04 PM
Reply:

Similar Artilces:

Sql question( select the first, second row in the table)
In MS Sql server, no "first" function,how to select only the first row?By the way, how to select only the seoncd row?Thx There is no particular order in which SQL stores the records. You need to specify the ORDER BY when you are talking about first/second etc. So if you want only first rowof a particular column you can do SELECT TOP 1   <col1>,   <col2>FROM   <table>ORDER BY   <column> ASC|DESCFor second row there a couple of ways: Lets say you have some date column and you want the 2nd latest row. One...

I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row.
 Hi, Let say I've 4 rows in a table. I want to select top 3 rows except the first from 4 rows of a Table. It should select from top 2 row,top3 row and top4 row. Thanks in advance Asghar Ali Mohammed http://www.aliwebdev.com(Web Designer and Developer)Do not forget to "MARK AS ANSWER" on the post that helped you. If you can use Linq, you can use Skip. http://msdn.microsoft.com/en-us/vcsharp/aa336757.aspxThanks, EdMicrosoft MVP - ASP/ASP.NET Hii dear..U can fire the Query select Top(3) from Table1 order by id desc.. "A conclusion is whe...

SQL call to count the total rows in Table B for each user in Table A
I have 2 tables:   TableA: Name UserA UserB UserC   Table B: Name               Data UserA              xxx UserB              asdasd UserB              ewrsad UserC              dsafasc UserA        &nbs...

Update row in Table A from row in Table B
Hello, I have 2 tables.... table A and table B. Ok, In a stored procedure, I pass in an int value @ID I want to get the row from Table B WHERE ID =@ID....and then UPDATE the Row in Table A that has the same ID...with the values from table B. Table A ID | Col1 | Col2 ----------------------- 2 a a Table B ID | Col1 | Col2 ----------------------- 2 b b I would like to update A.Col1 with B.Col1 and A.Col2 with B.Col2 Can someone help me out with this syntax? Thanks a lot. update tableA ...

Data Controls insert rows into gridview then insert rows from gridview into SQL Table
Hello there, I'm still new at performing miracles with code . Currently I am having trouble with my latest coding adventure I have a web application which records jobs and labour assigned to jobs. The issue in particular I'm having is the way in which labour is to be assigned. I want to use standard controls (Textbox, Dropdownlist) to record the values into a gridview (ie,put multiple rows into the gridview using the controls). Then, when all the inserting is complete, the user clicks a submit button to insert all the rows from the gridview into a database table. The reason for...

how can i add row to table of sql with vb.net code?
how can i add row to table of sql with vb.net code?  You can use System.Data.SqlClient classes :  Using con As New SqlConnection(" your connection string goes here ") Dim com As New SqlCommand("Insert into Person (id,name) values(@id,@name) ", con) com.Parameters.AddWithValue("@id", 1) com.Parameters.AddWithValue("@name", "Nomi1") com.Connection.Open...

Select rows from a table based on multiple rows of another table
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

SQL Table Rows
How is it possible to cycle through an SQL table row by row, without having to increment an ID by 1 in a while loop and go SELECT * FROM table WHERE id=$id ? And how can I find out how many rows are in the table? Dan Dear Dan, I don't know if I understood what you really want. Therefore I will try to help, ok. I use Postgres as Database. You can find out how many rows have in a table after you run a SQL like this. #!/usr/bin/perl use Pg; $db = Pg::connectdb("dbname=database"); # This is a way to do it. $result = $db->exec("SELECT ...

Select rows from a table based on multiple rows of another table #3
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #4
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. ...

Select rows from a table based on multiple rows of another table #2
I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. Sorry technical problems! I am working in a database associated with our scheduling package and I am trying to select rows in one table based on multiple rows in another table how ca I do this. Here is an example. I have a table which holds all of my project info and I have another table which holds discriptive project codes like project type, project category, progect life cycle.So in the pro...

SQL Data displayed row by row
Hi,  I was wondering if it was possible to get some help, what I am trying to do is create a ASP webpage that will display Data from an sql database row by row and if there is no data in one row then it will display a message saying "No Data in row"  Any help would be appreciated  Thanks, Michael   mstoten: I was wondering if it was possible to get some help, what I am trying to do is create a ASP webpage that will display Data from an sql database row by row and if there is no data in one row then it will display a message saying "No Data i...

adding html table columns from SQL table rows
I want to put data from an SQL table into an HTML table, but I want to start a new row in the HTML table when it is 5 columns wide. I have done this in classic ASP with the following code: TABLE NAME How can I accomplish the same thing with asp.net? I have created my SqlDataSource and the SelectCommand no problem, but I'm kind of stuck how to loop through the recordset. Why do you want to do it manally? Data binding is for such purposes only. Fill a DataSet or DataTable with the SqlCommand and do: GridView.DataSource = DataSet.Ta...

How do I display data from an sql query row by row?
I have the following code (at bottom of msg) in a page I'm trying to write. It works ok with a datagrid but I don't want a datagrid on the page as they look ugly (by default). Questions ... 1. Can I use a datagrid to display data with a custom layout? 2. How can I access data obtained from an sql server stored procedure row by row? I'm told you do it with a Dataset but I don't know how. The site is to look like this ... http://www.numbersix.co.nz/temp/intranet.jpg ... that's the existing site in ASP but must be converted to ASP.NET to keep up with things. Can anyone hel...

Web resources about - SQL Old school *= Question select All rows from table A and rows from B - asp.net.sql-datasource

West Lothian question - Wikipedia, the free encyclopedia
He illustrated his point by pointing out the absurdity of a Member of Parliament for West Lothian being able to vote on matters affecting the ...

Ohio Removes Question About Driver's Licenses From Job Form
Ohio has removed the question asking job applicants if they have a driver's license from the state's online hiring system.

A Question for Adam
Is there any plausible scenario in which the United States does not get involved in the current Saudi/Iran imbroglio and just treat it like a ...

All-inclusive honeymoon destinations and other travel questions answered
Q: My nephew is getting married in August and has asked for my help planning his honeymoon to the Caribbean. He doesn’t have a lot of money to ...

‘Star Wars: The Force Awakens’ script reveals answers to our burning questions
One of the things many of us did this Christmas was see Star Wars: The Force Awakens in theaters. The first movie in the new Star Wars saga is ...

"When Chris Wallace asks Bush if he squandered his initial advantage by failing to come up with a 'message' ...
"... which the media cares about more than he does. Instead, Bush wants to talk about how he's 'on the ballot in every state,' which is 'hard' ...

At Some Point Something Will Make Everyone Question If They Still Want To Acquiesce To A Lesser-Of-Two ...
I keep seeing one of those annoying automated spam tweets-on-endless-repeat from The Hill : "Billionaire George Soros said he regretted backing ...

This is the big legal question at the heart of the sexual assault case against Bill Cosby
Bill Cosby has been charged with aggravated indecent assault of one woman after dozens of others came forward in the past year to claim the comedian ...

Question #6 for 2015: Will real wages increase in 2016?
Earlier I posted some questions for next year: Ten Economic Questions for 2016 . I'll try to add some thoughts, and maybe some predictions for ...

Tom Oates: Quarterback, safety among the biggest questions for Badgers ...
Chippewa Herald Tom Oates: Quarterback, safety among the biggest questions for Badgers ... Chippewa Herald Replacing stellar safeties Michael ...

Resources last updated: 1/4/2016 6:06:20 AM