|
Which part of the query are you having trouble with?
It looks like you need to do a LEFT JOIN between Module and Subscribed and a LEFT JOIN between Subscribed and Membership, fetching whichever fields are required.
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
Off the top of my head, this should do it:
select mod.ModuleId, ModuleName,
UserId, SubscribeDate, LastUpdate,
MoreData, UserName
From Subscribed s
Left Join
Membership m
On m.userId = s.userId
Left Join
mod.ModuleId = s.ModuleId
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
I think he meant to have Module as the main table.
SELECT
m.ModuleID,
m.ModuleName,
s.UserID,
s.SubscribeDate,
s.LastUpdate,
u.UserName,
u.MoreData
FROM
Modules m
LEFT JOIN
Subscribed s
ON (m.moduleID = s.moduleID)
LEFT JOIN
Membership u
ON (s.UserId = u.UserId
ORDER BY
ModuleName
|
|
|
|
|
Oh. OK then. Nice catch.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Smells like homework anyway...
|
|
|
|
|
Michael Potter wrote: Smells like homework anyway...
Naaaaaaw. 
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
I'm 51. I haven't done homework since 1974.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Open mouth, insert keyboard - I am wrong again. Sorry about the assumption.
|
|
|
|
|
I ended up writing almost 140 lines of SQL code (using a cursor - what a pain in the ass) to accomplish my goal, but I'm still going to see if I can do it with a select statement, so I'll use Mr Potter's version of your suggestion.
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"...the staggering layers of obscenity in your statement make it a work of art on so many levels." - Jason Jystad, 10/26/2001
|
|
|
|
|
Does anyone know if it's possible to control the T-SQL output from LINQ to SQL in any way? Specifically, I'd like to control the definition of the parameters created for sp_executesql when using variable-length data types (varchar, decimal, numeric).
VS 2008 defines the data types correctly by declaring the size when I drag and drop a table into the designer (O/R mapper?) for my dbml file. But that value isn't considered when the sp_executesql statement is generated. So the size of the gernated parameter is the size of the value passed in (ex. "test@test.com" becomes nvarchar(13) regardless of the size of the column in the database).
Thanks,
Mark
|
|
|
|
|
Ok, I've found one way to do this but it's hardly elegant and it doesn't allow you to take advantage of delayed execution:
DataContext.GetCommand returns System.Data.Common.DbCommand which allows you to access the Parameters collection where you can set the size. If you're using SQL Server you need to set Precision you'll have to cast the parameter to a SqlParameter.
After you've updated the parameter you can call ExecuteReader (don't forget to make sure the connection is open). Then use the DataContext.Translate<tresult(dbdatareader) method="" to="" convert="" the="" datareader="" ienumerable<t="">.
The problem is this doesn't allow for delayed execution because you can't update the actual command used by the query variable.
|
|
|
|
|
Just as an update, I've found a way to inject my workaround (read: hack) into the process in a way that is transparent to the consumer of my class. I'm going to create a class which wraps System.Data.Linq.Table<t> and create a custom implementation of the GetEnumerator() and GetEnumerator<t>() methods which will parse the expression tree (haven't proved this part of the concept yet) and determine the parameter mappings, then I'll be able to reflect over T (I'll have to cache the result of this for performance) to determine the correct value for DbParameter.Size.
When I've completed the ability to parse the expression tree I'll write an article on everything I've learned. Wish me luck!
Mark
|
|
|
|
|
Please how can I insert a table from excel file into an sqlserver table programmatically using C#.That is create a table in sqlserver with a file in excel that already have the table defined.Using c#.
|
|
|
|
|
By searching Google[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I want to query a table for the previous date.
Not sure how to construct the WHERE statement.
Thanks,
|
|
|
|
|
The answer was;
WHERE [TimeStamp]>= Convert(varchar(10), DateADD(DAY, -1, GETDATE()), 101) AND [TimeStamp] < Convert(varchar(10), GETDATE(), 101)
This gave the info for the previous day only.
Brian
|
|
|
|
|
Hi all,
I have the following script to do a back up of SQL2005 database:
BACKUP DATABASE [Repo] TO DISK = N'C:\Program Files\repo.dat' WITH NOFORMAT, NOINIT, NAME = N'repor-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
When i try to look for Repo.dat file under C:\programfiles i cant get it.
Please advice
Thanks
|
|
|
|
|
SQL Server would save it to the filesystem on the machine in which SQL Server is running, not on the client box from where you issue the backup command.
Take care,
Tom
-----------------------------------------------
Check out my blog at http://tjoe.wordpress.com
|
|
|
|
|
hi
My aim is to retrieve a picture from a access database ................
This is the code which i am using
byte[] bt = (byte[])(bindreader[0]);
MemoryStream mst = new MemoryStream();
mst.Write(bt, 0, bt.Length);
pbox.BackgroundImage = Image.FromStream(mst);
its working fine when the image is stored through code it will be in long binary format
when i am inserting image thru access its in the bmp so its creating a problem if any one knows how to tackle this situation please help me .
regards
sindhu tiwari
always innovative
|
|
|
|
|
I am willing to take on datawarehousing course. what things to keep in mind for choosing a course from an institute or for choosing which institute to got for it.
I have worked upto reporting services and integration services in sql server 2005 and would like to go ahead with database side.
I am in mumbai (India) and if someone can suggest me out, it will be a great help.
Thanks.
Jwalant Natvarlal Soneji
BE IT, India
|
|
|
|
|
I went on a course for Reporting Services last month. The trainer just went through the Reporting Services step-by-step book. I could have done that myself.
If you are paying for this yourself, I recommend that you buy the Step-by-Step book and then take a week off to study it.
|
|
|
|
|
hey everyone i am getting this error on sql server 2005:
" An error has occured while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server )"
could anybody help me out here?
Luis E Tineo S
|
|
|
|
|
set the remote connection...
|
|
|
|
|
You must enable remote connections for each instance of SQL Server 2005 that you want to connect to from a remote computer. To do this, follow these steps:
1. Click Start, point to Programs, point to Microsoft SQL Server 2005, point to Configuration Tools, and then click SQL Server Surface Area Configuration.
2. On the SQL Server 2005 Surface Area Configuration page, click Surface Area Configuration for Services and Connections.
3. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Remote Connections, click Local and remote connections, click the appropriate protocol to enable for your environment, and then click Apply.
Note Click OK when you receive the following message:
Changes to Connection Settings will not take effect until you restart the Database Engine service.
4. On the Surface Area Configuration for Services and Connections page, expand Database Engine, click Service, click Stop, wait until the MSSQLSERVER service stops, and then click Start to restart the MSSQLSERVER service.
|
|
|
|
|
hi there,
i m using sql server 2005, and i wanna know how can i change primary key data
for example :
i have 3 tables
1. have 3 columns(EmpID, Salary, RegID)
where EmpID is Primary key and RegID is unique Key
2. have 2 columns(EmpID, Work)
where EmpID is Foreign key
3. have 2 columns(EmpID, Status)
where EmpID is Foreign key
now i wanna change EmpID in all 3 tables,
so i try do it something like this, as i know it will give error and wont work
Update Table1 set EmpID = XX where RegID = XX
can someone tell how can i do this... mean if i change EmpID in table 1 then it automatically change EmpID in others tables
is it possible? or any other way?
thanks
Becoming Programmer...
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
