|
Arun Sylvester J wrote: If yes, would you please tell me how?
Following is what I used in google. It would suggest the specific answer is no.
mysql stored procedure access remote database
|
|
|
|
|
I was taught to model one-to-many relations using an intermediate 'link' table to maintain 1NF.
However I'm increasingly seeing one-to-many relationships modelled simply by concatenating the 'many' into a single string and storing that directly in the table.
Consider a table of TASKS, and a table of CATEGORYs, where each task may have multiple categories.
I was taught to model the relationships between the tasks and categories using a third table, eg TASK_CATEGORIES, which comprised a one-to-one mapping.
TASK Table
----------
Task_ID Name Allocated_To etc
1 Fix XYZ Bug Dan ...
2 Add ABC Feature Dan ...
CATEGORY Table
--------------
Cat_ID Name Description etc
1 Bug This is a bug ...
2 Feature This is a feature ...
3 Work This is a work item ...
TASK_CATEGORY Table
-------------------
Task_Cat_ID Task_ID Cat_ID
1 1 1
2 1 3
3 2 2
4 2 3
ie. The task: (Task_ID == 1) has categories: (Bug, Work), etc
However now I'm seeing:
TASK Table
----------
Task_ID Name Categories Allocated_To etc
1 Fix XYZ Bug 1;3 ...
2 Add ABC Feature 2;3 ...
CATEGORY Table (as before)
Clearly (IMO) the first design is more correct, but the second is often easier to work with.
The question for you is: What are the downsides of design #2, and have you seen it in practice?
|
|
|
|
|
Well for a start your foreign key potential is out the window, it makes your use of category basically useless from a data structure POV.
I have seen it once, a project built by Reuters, one of the worst performing applications ever inflicted on us. The only benefit was to make the structure so obscure it was unusable by anyone without the ER diagram.
[edit] I now remember one of the new devs proposing that a while back, offered to terminate him on the spot unless he conformed to a correct data structure [/edit]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
1 is preferable in nearly all cases. 2 is only appropriate for reporting, but remember that whoever gets the report may simply turn around and try to import it.
If you have only a few categories, and they are unlikely to change, then you can make a bitmap:
Cat
1 Bug
2 Feature
4 Work
Bug + Work = 5
|
|
|
|
|
#2 is obvious violation of 1nf. Whenever there is a need to add or remove a category in #2, we will have to read and write the whole table. That is a serious design flaw.
In #1, we can use task id and categorg id as composite primary key for the intermediate table.
|
|
|
|
|
Peter Leow wrote: we will have to read and write the whole table
Excellent point.
|
|
|
|
|
hi friends actually i am working on a mlm project in which members are added in a tree pattern, and get the payment accordingly.
My table structure is as follow:
Id ParentId IsLeft IsRight
1 Null Null Null
2 1 1 Null
3 1 Null 1
4 2 1 Null
5 2 Null 1
6 3 1 Null
7 3 Null 1
8 4 1 Null
9 4 Null 1
10 5 1 Null
the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.
My problem is to find the query which can return the total income of any given node.
enter image description here
According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.
According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has no pairs
According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)
one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)
I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$, but the query has not been able to consider the first condition that is the 2:1 condition
declare @ParentId as int
set @ParentId=1
create table #temp_table_name
(
ParentId varchar(30) null,
)
;with Child as
(
select id,ParentId from tblTestingTree where id=@ParentId
union all
Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree
inner join Child
on tblTestingTree.ParentId=Child.Id
)
insert into #temp_table_name
select c.ParentId from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
group by c.ParentId
having COUNT(c.ParentId)>1
select COUNT(*)*500 as totalmoney from #temp_table_name
drop table #temp_table_name
|
|
|
|
|
This will give you the ration values you are after, then it is just a matter of calculation
SELECT ParentID, SUM(ISNULL(IsLeft,0)) L,SUM(ISNULL(IsRight,0)) R
from TableName
Group By ParentID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear RAH where we place this code in my function so i can get my solution which provided by you
Quote: SELECT ParentID, SUM(ISNULL(IsLeft,0)) L,SUM(ISNULL(IsRight,0)) R
from TableName
Group By ParentID
please suggest us
thanks
|
|
|
|
|
Use SQL Server Management Studio to build the queries you need to get the results to meet your needs. I cannot do your job for you. If it is too hard to work out how to use this code then get another job, this one will be beyond you!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
That wasn't to hard to solve.
But I'm not going to tell you how. And this is why:
Pyramid schemes are illegal in a larger part of the world, and I'm not being part of it!
Alternatively this is a school assignment, where I'm not simply going to tell you the answer. Because I'm might have to work with you in the future, and if you got your degree without actually having understood what you were taught, you're going to be a dead weight for your workmates.
So you'll have to do the work yourself, but I'll assume you're a student and will give you a few pointers on how to get there.
Drop that silly tbl prefix, you're not selling furniture and it's not following standards (ISO-11179).
Drop the temp table, it's a last resort and shows that you're thinking procedurally instead of setbased.
So if you think setbased instead: get two CTEs, one containing the nodes having left children and one having containing the nodes with right children. Now you only need to join those CTEs three times to get the nodes that should get payed $1500.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
Jörgen Andersson wrote: Pyramid schemes are illegal in a larger part of the world, and I'm not being part of it!
Good call.
The OP lists the US as where they are are from and in many jurisdictions in the US a pyramid scheme can lead to a felony conviction.
|
|
|
|
|
I'm trying to write a complex stored procedure that will filter my GridView. Right now I'm using a If statement but I rather to a way with that and some how run the procedure depending on what I type into my text box. Instead of having:
@SearchBy = 'Username' OR
@SearchBy = 'State' OR
@SearchBy = 'AreaNumber'
Also
WHERE
Username LIKE '%' + @SearchVal + '%'
OR State LIKE '%' + @SearchVal + '%'
OR AreaNumber LIKE '%' + @SearchVal + '%'
Is there a way to do something like:
These don't work
@SearchBy = ''
@SearchBy = '' LIKE '%' + @SearchVal + %
This is my code and hopefully it will clear up what I'm trying to say above:
ALTER PROCEDURE [dbo].[SearchEmpRecords_Sp2]
@SearchBy varchar(50) = ISNull,
@SearchVal varchar(50) = ISNull
AS
BEGIN
DECLARE @sql NVARCHAR(1000)
IF
@SearchBy = 'Username'or
@SearchBy = 'State' or
@SearchBy = 'AreaNumber'
BEGIN
SELECT
EmployeeID,
Username,
State,
AreaNumber
FROM Employees
WHERE
Username LIKE '%' + @SearchVal + '%'
OR State LIKE '%' + @SearchVal + '%'
OR AreaNumber LIKE '%' + @SearchVal + '%'
END
ELSE
BEGIN
SELECT
EmployeeID,
Username,
State,
AreaNumber
FROM Employees
END
END
EXECUTE sp_executesql @sql
Also the Username, State, AreaNumber are in my aspx code. They are in a dropdown list and part of a SelectedItem routine. I don't know if you needed to know that but I put it in here just in case.
|
|
|
|
|
The only way to do what you want is using dynamic sql, basically build a string and execute it. A better soluton isto pass in 3 discreet variables and test each one for content.
Where ((Isnull(@UserName,'') == '' OR Table.UserName like @Username)
and ((Isnull(@Sate,'') == '' OR Table.State like @State)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi,
i am a beginner in database...i have two tables like
eid ename pid and second table like pid salary
where i need to get the name of employee who took maximum salary in April month..
please suggest
|
|
|
|
|
Try something like this :
SELECT s.salary, e.ename FROM employees e INNER JOIN salaries s ON s.pid = e.pid HAVING s.salary = MAX(s.salary)
This assumes the first table is called employees and the second table is called salaries. Substitute real names as appropriate.
Hope this helps
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
can i know hoe we can know that maximum salary taken by employee in april month please?
|
|
|
|
|
I will have to leave that up to you to figure out. Mycroft has given you a link to an excellent resource for learning SQL. You will be better off by trying to work it out yourself.
When I was a coder, we worked on algorithms. Today, we memorize APIs for countless libraries — those libraries have the algorithms - Eric Allman
|
|
|
|
|
Spanner, while Wayne has given you the answer I suggest you do NOT use a forum as your primary learning tool. Read up on queries using TSQL, there is plenty of doco and examples available. This will insure you spend less time struggling with really basic, easy to learn stuff (like this question) and more time being productive.
Here is an example of what you can find to help you Visual Representation of SQL Joins[^]
Oh and as a bonus you will not piss off the forum by asking endless very simple questions.&lt;/pre&gt;</pre></pre>
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
First of all, you need a field in the second table to capture date of salary.
|
|
|
|
|
hi to all
how can i optimiza this query
id dont want use select top(1) in nested select
thanks in advance
|
|
|
|
|
On a first look: too many subqueries.
Have a look here: How To: Optimize SQL Queries[^].
I would suggest you to ask Google with sentence: "query performance".
|
|
|
|
|
Maciej Los wrote: too many subqueries.
 I try to avoid subqueries; JOINs tend to work better in many situations.
|
|
|
|
|
Consider changing Top(1) for Max([Time]), but with a proper indexing you shouldn't notice a to big difference.
Politicians are always realistically manoeuvering for the next election. They are obsolete as fundamental problem-solvers.
Buckminster Fuller
|
|
|
|
|
I've built a RDL with SRSS 2008 (actually it's migrated from 2005 by opening and saving in SRSS 2008)
There's a group which its visibility is controlled by input parameters. (Static) rows inside the group sets RepeatOnNewPage as True and KeepWithGroup attribute is set to "Before" as footer also.
I found that the group keeps repeating on each page just only missing in the second last page.
I have tried to set the RepeatOnNewPage be either True or False for the group but the result still be same (missing the footer in 2nd last page).
I am not sure why this happening and hope if some advise can be provided. Thanks very much.
Source can be provided as request.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
