|
|
Hi guys.
i want join the results this table below with matching records of another table. How could i do it. I have tried using the right join on class table but seems like not getting the right results. could you hlep me joining the class table with all all those records are only in class table.
Thanks
select ucv.cf_value+', '+ucv2.cf_value as FullName
from users u
left join CUSTOM_FIELDS_VALUE ucv on ucv.cf_id=3
left join CUSTOM_FIELDS_VALUE ucv2 on ucv2.cf_id = 2
right join class on class.instructorName= ucv.cf_value+', '+ucv2.cf_value
where u.uid=ucv.uid and u.uid=ucv2.uid
order by ucv.cf_value+', '+ucv2.cf_value asc
|
|
|
|
|
Oh dear have you got a problem. Looks like you are using the instructors name as a key field for joining tables - WRONG. Each table should have an ID field (usually and int IDENTITY(1,1)) as the primary key and then a foreign key to maintain data integrity.
Your join structure is wrong. A left join to UCV2 and THEN a right join the to class means that you will get records, wait I'm have no idea what you would get but it would be a mess.
Take a look at this Visual Representation of SQL Joins[^] for some clarification
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello -
I'm in the process of using a script that will help with re-indexes. I keep getting the following errors.
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'ELSE'.
My database name is Backdrop and the table is dbo.PJTRAN and the index is pjtran0. Here is the script if someone can help me understand why I keep getting those errors.
IF EXISTS (SELECT [object_id],[index_id],[avg_fragmentation_in_percent] FROM sys.dm_db_index_physical_stats
(DB_ID(N'Backdrop'), OBJECT_ID(N'PJTRAN.PJTRAN0'), NULL, NULL , 'DETAILED')
WHERE [avg_fragmentation_in_percent] < 30)
BEGIN
ALTER INDEX ALL ON PJTRAN REORGANIZE
END
ELSE
ALTER INDEX ALL ON PJTRAN REBUILD
Thank you..
|
|
|
|
|
Have you executed the script in Query Analyzer?
|
|
|
|
|
This is for a SQL 2005 database. I'm using SSMS.
|
|
|
|
|
If what exists, the structure does not make sense, you are selecting a row and then ask if something exists. Change the script to:
DECLARE
@Count INT
SELECT @Count = COUNT(*)
FROM sys.dm_db_index_physical_stats
(DB_ID(N'Backdrop'), OBJECT_ID(N'DBO.table'), NULL, NULL , 'DETAILED')
WHERE [avg_fragmentation_in_percent] < 30
IF @Count > 0
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have a stored procedure that I am doing dynamically and for some reason the error thrown is
Msg 102, Level 15, State 1, Line 4<br />
Incorrect syntax near '.'.<br />
The stored procedure is this:
ALTER PROCEDURE [dbo].[proc_AgencySearchDarrell] <br />
@Mortgagee varchar(50) = NULL,<br />
@ClosingDateBegin datetime = NULL,<br />
@ClosingDateEnd datetime = NULL,<br />
@MortgageId int = NULL,<br />
@County varchar(50) = NULL <br />
AS<br />
<br />
SET NOCOUNT ON<br />
<br />
DECLARE @SQL Nvarchar(4000)<br />
<br />
<br />
SELECT @SQL = 'SELECT DISTINCT M.MortgageId, <br />
T1.Agent, <br />
T1.Agency, <br />
T3.InsCompanyUW,BS1.FirstName' + ' ' + 'BS1.LastName,<br />
BS2.FirstName' + ' ' + 'BS2.LastName,<br />
L1.BrokerageBus, <br />
L2.Originator, <br />
L3.Mortgagee,<br />
RE1.Appraiser, <br />
RE2.BuyersBroker, <br />
RE3.BuyersSalesperson,<br />
P1.StreetAddress, <br />
P2.City,<br />
CONVERT(varchar,M.TransClosingDate,101) AS TransClosingDate,<br />
P3.County<br />
FROM Mortgage M LEFT OUTER JOIN TitleInfo T1<br />
ON M.MortgageId = T1.MortgageId <br />
LEFT OUTER JOIN TitleInfo T2<br />
ON M.MortgageId = T2.MortgageId <br />
LEFT OUTER JOIN TitleInfo T3<br />
ON M.MortgageId = T3.MortgageId <br />
LEFT OUTER JOIN BuyerSellerInfo BSI1 <br />
ON M.MortgageId = BSI1.MortgageId<br />
LEFT OUTER JOIN BuyerSeller BS1 ON BSI1.BuyerId = BS1.BuyerSellerId <br />
<br />
LEFT OUTER JOIN BuyerSellerInfo BSI2 <br />
ON M.MortgageId = BSI2.MortgageId<br />
LEFT OUTER JOIN BuyerSeller BS2 ON BSI2.SellerId = BS2.BuyerSellerId <br />
LEFT OUTER JOIN LoanInfo L1<br />
ON M.MortgageId = L1.MortgageId <br />
LEFT OUTER JOIN LoanInfo L2<br />
ON M.MortgageId = L2.MortgageId <br />
LEFT OUTER JOIN LoanInfo L3<br />
ON M.MortgageId = L3.MortgageId <br />
LEFT OUTER JOIN RealEstateInfo RE1<br />
ON M.MortgageId = RE1.MortgageId <br />
LEFT OUTER JOIN RealEstateInfo RE2<br />
ON M.MortgageId = RE2.MortgageId <br />
LEFT OUTER JOIN RealEstateInfo RE3<br />
ON M.MortgageId = RE3.MortgageId <br />
LEFT OUTER JOIN PropertyInfo P1<br />
ON M.MortgageId = P1.MortgageId <br />
LEFT OUTER JOIN PropertyInfo P2<br />
ON M.MortgageId = P2.MortgageId <br />
LEFT OUTER JOIN PropertyInfo P3<br />
ON M.MortgageId = P3.MortgageId <br />
WHERE 1=1 '<br />
<br><br />
IF @MortgageId IS NOT NULL<br />
SELECT @SQL = @SQL + ' AND (M.MortgageId = COALESCE(@MortgageId,M.MortgageId)) '<br />
<br><br />
IF @Mortgagee IS NOT NULL <br />
SELECT @SQL = @SQL + ' AND (L3.Mortgagee = COALESCE(@Mortgagee,L3.Mortgagee)) '<br />
<br><br />
IF @County IS NOT NULL<br />
SELECT @SQL = @SQL + ' AND (P3.County = COALESCE(@County,P3.County))'<br />
<br><br />
IF @ClosingDateBegin IS NOT NULL<br />
SELECT @SQL = @SQL + ' AND (M.TransClosingDate BETWEEN @ClosingDateBegin AND @ClosingDateEnd)'<br />
<br />
<br><br />
<br><br />
Exec sp_executesql @SQL<br />
<br />
Could it be the concatenated name in my FROM clause?
Also note that when it wasn't in dynamic sql it worked fine. We were fighting with the WHERE clause and needed that to be dynamic. I just couldn't find a way to make the Statement (SELECT AND FROM) static and the WHERE dynamic and have it work.
Any suggestions?
Thanks
|
|
|
|
|
Without attempting to run it myself if I get these errors with dynamic sql I always do a quick print @sql and see what I've got. Copy it into a new query window and run it. That should give you more of an idea.
me, me, me
"The dinosaurs became extinct because they didn't have a space program. And if we become extinct because we don't have a space program, it'll serve us right!"
Larry Niven
|
|
|
|
|
Great idea, I'll try that.
Thanks
|
|
|
|
|
This worked and it was the concatenation of the FirstName and LastName's
Thank you again for the time.
|
|
|
|
|
Swelborn wrote: This worked and it was the concatenation of the FirstName and LastName's
Yup, that was on Line 4 [of the dynamic part] - exactly as the error message prompted.
|
|
|
|
|
Pls is it possible to generate crystal report from dataset created in code at runtime?
Thanks.
|
|
|
|
|
Short answer is Yes.
Have your application create a dataset, then assign the dataset to the CrystalReportViewer and you're done.
There are lots of examples of using Crystal Reports here in Code Project, just search them out.
|
|
|
|
|
pls i have done this already, i get an error: this report does not have a table.
pls help.
thanks
|
|
|
|
|
Could I write this as a exists statement we use this quite often and I read it has very poor performance.
WHERE `cl_client_common`.`UniqueNumber-900` IN(5337, 4439, 4725, 4096, 8078, 5912, etc, etc)
We do not know what the values will be until the user selects the clients they want.
All of the examples I see are using a subquery for the EXISTS statement and the only way I can think to write that with a subquery would be to use an IN in the subquery.
Thank you any help is appreciated.
Humble Programmer
|
|
|
|
|
If you are storing the list of numbers in a temp table or similar, you could use
WHERE `cl_client_common`.`UniqueNumber-900` IN (select NUMBER from TEMPTABLE)
or simply by doing an inner join to the temp table...
Otherwise, the way you are doing it will have to do!!
|
|
|
|
|
Learn to use the sql profiler, it will take you query and give you an execution plan, it may even suggest what fields need indexes to optomise your query, it will certinaly give you the cost of each process. You can then decide on the best approach.
As DM said you have lots of options, temp table, table variable, sub query. Use and in() or an inner join or even exists as suggested earlier. I'm not sure if the exists is a better solution, I would be tempted to go with the table variable on a large table os just a sub query as you have done. Depends on the size of the data sets and the indexing.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I generally prefer to use a join.
I caution against IN unless the values are hard-coded (which is likely a bad design in its own right).
I especially dislike subqueries in WHERE clauses.
I used to have to use them with Oracle, but I don't recall ever doing them with SQL Server.
|
|
|
|
|
We are joining the tables.
The values are not really hard coded...
The user is presented with a grid with checkboxes they can choose one or as many "clients" as they want in the grid.
Then we use the primary key for the record on the grid to create the new sql statement.
So user selects primary key 1,2,3,4 then our IN statement will be `UniqueNumber IN (1,2,3,4)
Humble Programmer
|
|
|
|
|
Greetings, all...
I'm using Visual Studio 2008 Express with C# and SQL Server 2008 backend. I'm wanting to design a lookup table form where the user can select the table from a list of items on the left (probably a TreeView with a node for each entry), then have the grid on the right reflect the contents of that table (Users, Config settings, Security, etc.). But I don't have to have a table adapter for each table that could be on the form and would like to re-use the component by setting the properties on the fly. Has anyone done this before?
As a corrolary to this, if the user double-clicks on an entry in the grid, I would like to pop-up a form to allow the user to edit that entry. Again, I would like to use a single form to do this, without having a different form and table adapter, binding source, etc., for each possible table.
Has anyone done this before and can offer any suggestions? Or would the code to do it all run-time outweigh what it would take to just have separate table adapters and forms for each possible lookup table?
Thanks in advance!
- Bert
|
|
|
|
|
This belongs in the design and architecture forum, certainly not the database forum, this has absolutely nothing to do with database.
Having said that, this is our standard design. First you need to chuck out the tableadaptor concept, you are using the MS widget/wizard crap things. Do some research into Data Access Layer, it will change the way you manage your data.
I have a utility that takes a datatable and binds it to a datagridview, formats the DGV and the various columns based on the data types of the datatable.
I have a giant switch statement that reacts to the node click event, gets the currentview (enum of all the tables to be displayed) from the node and decides what object to get a datatable from. The datatable is then passed to the DGV loader utility.
Then on the double click event of the DGV I already know what the currentview is from the loader so I know what form to display based on the currentview. I have the ID of the record clicked on (first filed always has the ID and is not visible in the DGV) so I pass the ID to the form in the constructor and pop a dialog.
The dialog gets the record (or an empty form for add) and saves the record back to the database. It returns a dialogresult to the main form.
If the main form get an Ok result it reloads the datatable from the database and refreshes the DGV with the new record in it.
As I said bog standard do it every day UI.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for the quick reply. I apologize for the posting in the wrong location. This crossed over to several areas, so I wasn't sure where it fit best.
I'm admittedly new to working with .NET and data access, having done most of my Windows coding in Delphi recently. Time to shift some paradigms, but that's just fine with me. 
Thanks again!
- Bert
|
|
|
|
|
Hi:
I have data that looks like this:
iYear RecordID Group
2010 1 A
2010 2 A
2010 3 A
2010 4 D
2010 5 D
2009 6 C
2009 7 C
but I need to rank or apply row numbering on the Groups sorted by the RecordID to make the data look like this:
iYear RecordID Group Group Ranking
2010 1 A 1
2010 2 A 1
2010 3 A 1
2010 4 D 2
2010 5 D 2
2009 6 C 3
2009 7 C 3
Any help would be greatly appreciated.
Thx
|
|
|
|
|
I would think you could use a case statement to accomplish that but that is probably not the best way.
Humble Programmer
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
