Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Mar 2005
    Posts
    92

    Unanswered: simple SQL query or design problem?

    Dear All

    I have posted this a few days ago, but probably didnít describe it properly so Iíll attempt it again.

    Iím wondering if itís the design that needs to be changed or I simply canít put this together.

    I have 3 tables.

    1. people (peopId, peopFName, peopSName etc.)
    2. codes (codeId, codeName)
    3. codedPeople(codePeopleId, peopId, codeId)

    Codes represent different skills that people might have, like:

    Technology-Founder
    Technology-Chief Executive Officer
    Technology-Systems Designer
    etc.

    people, clearly holds data about people.

    CodedPeople holds data on which people in the db are coded what skills.

    What I need is a query that returns all distinct people records and takes a number of codeNames as input.

    So if I have person1 and person2 in the db and they are coded as:

    Person1 is a t-Founder
    Person1 is a t-CEO

    Person2 is a t-Systems Designer

    Query 1, looking for all t-CEO
    Result 1: person1

    Query 2, looking for all t-CEO AND t-Founder
    Result 2: person1

    Query 3, looking for all t-CEO OR t-Systems Designer
    Result 3: person1, person2

    Query 4, looking for all t-Founder AND t-Systems Designer
    Result 4: -


    I have:

    [ code]
    SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
    FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
    INNER JOIN codes ON codes.codeId = codedPeople.codeId
    WHERE ( parameter )
    ORDER BY peopSName, peopFName
    [ /code]

    And I add in parameters from my front end to the WHERE clause, like in the example:

    Parameter = ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' )


    This works fine when using OR but doesnít work when trying to use AND in a sense of a person is coded with skill1 AND skill2 AND skill3 etc.

    Any idea how to implement what I need to achieve?

  2. #2
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    Look this example, try in pubs db.......

    Code:
    use pubs
    go
    create table #people (peopId int, peopFName varchar(40), peopSName varchar(40))
     create table #codes (codeId int, codeName varchar(40))
     create table #codedPeople(codePeopleId int, peopId int, codeId int)
    go
    insert into #people 
    select 1,'personF1','personL1' union
    select 2,'personF2','personL2'
    go
    insert into #codes 
    select 1,'t-CEO' UNION
    SELECT 2,'t-CFO' UNION
    SELECT 3,'t-Founder'
    go
    insert into #codedPeople 
    select 4,2,3 union
    select 1,1,1 union
    select 2,1,3 union
    select 3,2,2 
    
    --select * from #people
    --select * from #codes
    --select * from #codedPeople
    ----u should create a view here
    --create view t
    --as
    select * into #t from (
    SELECT p.peopId, p.peopFName, p.peopSName, c.codeId, c.codeName
    FROM #people p 
    INNER JOIN #codedPeople d 
    ON p.peopId = d.peopId
    INNER JOIN #codes c 
    ON c.codeId = d.codeId
    ) as t1
    -------------------------And condtion example
    select #t.* from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CEO','t-Founder') 
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CEO','t-Founder'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CEO','t-Founder')
    ------- or condition example-------------
    select #t.* from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CEO')
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CEO'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CEO')
    union
    select #t.* from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CFO') 
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CFO'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CFO')
    come back if uave doubts
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  3. #3
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by mallier
    Look this example, try in pubs db.......

    Code:
    use pubs
    go
    create table #people (peopId int, peopFName varchar(40), peopSName varchar(40))
     create table #codes (codeId int, codeName varchar(40))
     create table #codedPeople(codePeopleId int, peopId int, codeId int)
    go
    insert into #people 
    select 1,'personF1','personL1' union
    select 2,'personF2','personL2'
    go
    insert into #codes 
    select 1,'t-CEO' UNION
    SELECT 2,'t-CFO' UNION
    SELECT 3,'t-Founder'
    go
    insert into #codedPeople 
    select 4,2,3 union
    select 1,1,1 union
    select 2,1,3 union
    select 3,2,2 
    
    --select * from #people
    --select * from #codes
    --select * from #codedPeople
    ----u should create a view here
    --create view t
    --as
    select * into #t from (
    SELECT p.peopId, p.peopFName, p.peopSName, c.codeId, c.codeName
    FROM #people p 
    INNER JOIN #codedPeople d 
    ON p.peopId = d.peopId
    INNER JOIN #codes c 
    ON c.codeId = d.codeId
    ) as t1
    -------------------------And condtion example
    select #t.* from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CEO','t-Founder') 
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CEO','t-Founder'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CEO','t-Founder')
    ------- or condition example-------------
    select #t.* from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CEO')
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CEO'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CEO')
    union
    select #t.* from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CFO') 
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CFO'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CFO')
    come back if uave doubts



    Whoa, thatís cool, itís working. Actually I left the codeId and codenames columns in however, I donít really need them, they were for tests only Ė sorry about that, I hope that didnít cause you much trouble.

    The OR condition works with the original and rather simple:

    WHERE codename = ĎCode1í OR codename = Ďcode2í

    Well it seems to work.


    Iím still trying to fully understand the query you wrote! Does it need to be that complex, now that I donít actually need the codeId nor the codename to be returned within the result itself?

    Thanks for your help, itís much appreciated!

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Good luck dealing with more complex logic such as:
    (codeName LIKE 't-CEO') OR (codeName LIKE 't-CFO') and (codeName LIKE 'janitor')
    ...and also, the LIKE operator is pointless without wildcards.
    I strongly suggest that you revise your application to eliminate free-form entry of sql clauses. It will be a source of continuing error and frustration for users and admins alike.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up re

    glad to know that helps,I ve seen ur question on friday itself.i wrote the query on that day itself,but i didnt have time to test it before posting.
    Code:
    ------give the column names u want in that select query---
    select #t.peopId, #t.peopFName, #t.peopSName   from (
    select min(peopFName)as peopFName from 
    #t where codeName in ('t-CEO','t-Founder') 
    group by peopFName
    having count(*)>(select count(*)-1 from #codes where codeName in ('t-CEO','t-Founder'))
    ) as tm ,
    #t where tm.peopFName=#t.peopFName and #t.codeName in ('t-CEO','t-Founder')
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  6. #6
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by blindman
    Good luck dealing with more complex logic such as:
    (codeName LIKE 't-CEO') OR (codeName LIKE 't-CFO') and (codeName LIKE 'janitor')
    ...and also, the LIKE operator is pointless without wildcards.
    I strongly suggest that you revise your application to eliminate free-form entry of sql clauses. It will be a source of continuing error and frustration for users and admins alike.

    What do you mean by free-form entry?

    The app uses a form with a finite selection of given text string options represented as check boxes, and sticking 'AND' and 'OR' operators between.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, that is better. But will the form allow mixing of AND and OR operators? If so, I'm not sure mallier's code (or anybodies) will handle the potential complexity. I confess I have not looked at mallier's code very deeply...
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Jun 2003
    Posts
    269

    Thumbs up Re

    my code handle 'and' and 'or' operator.But it wont support wildcards.And he has to write a logic to separte which are going to come under 'and' and 'or.'Pls go thru my code.i have created temp table,so anyone can execute that in our pubs db
    Last edited by mallier; 11-21-05 at 12:06.
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  9. #9
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by mallier
    my code handle 'and' and 'or' operator.But it wont support wildcards.And he has to write a logic to separte which are going to come under 'and' and 'or.'Pls go thru my code.i have created temp table,so anyone can execute that in our pubs db

    From my few test runs so far, it looks like it works perfectly. Just trying to write logic to use different query for AND and OR.

    Thanks again for your help!

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Ok, I've taken your invitation and reviewed your code, and the only description I can come up with is "Obfuscatory".

    Why do you use unnecessary nested queries? This statement:
    Code:
    select	*
    into	#t
    from	(SELECT	p.peopId,
    		p.peopFName,
    		p.peopSName,
    		c.codeId,
    		c.codeName
    	FROM	#people p 
    		INNER JOIN #codedPeople d ON p.peopId = d.peopId
    		INNER JOIN #codes c ON c.codeId = d.codeId) as t1
    is more simply stated as
    Code:
    SELECT	p.peopId,
    	p.peopFName,
    	p.peopSName,
    	c.codeId,
    	c.codeName
    into	#t
    FROM	#people p 
    	INNER JOIN #codedPeople d ON p.peopId = d.peopId
    	INNER JOIN #codes c ON c.codeId = d.codeId
    And in this statement:
    Code:
    select	#t.*
    from	(select	min(peopFName) as peopFName
    	from	#t
    	where	codeName in ('t-CEO','t-Founder') 
    	group by peopFName
    	having	count(*)>
    		(select	count(*)-1
    		from	#codes
    		where codeName in ('t-CEO','t-Founder'))) as tm,
    	#t
    where	tm.peopFName=#t.peopFName
    	and #t.codeName in ('t-CEO','t-Founder')
    ...the use of min(peopFName) is pointless as you are grouping by peopFName, the subtraction of 1 from your count total can be avoided by simply using >= instead of >, and lastly you should be using standard ANSI JOINs to link your tables for clarity instead of WHERE clauses. Like this:
    Code:
    select	#t.*
    from	#t
    	inner join --tm
    		(select	peopFName
    		from	#t
    		where	codeName in ('t-CEO','t-Founder') 
    		group by peopFName
    		having	count(*)>=
    			(select	count(*)
    			from	#codes
    			where codeName in ('t-CEO','t-Founder'))) as tm
    		on #t.peopFName = tm.peopFName
    where	#t.codeName in ('t-CEO','t-Founder')
    Same issues apply with you UNION clause. (Not even going to go into the potenial problems with "select *...")

    But my big concern with this is that it does not seem to satisfy the poster's requirement of submitting the WHERE clause as a parameter (presumably a string). Is this going to be all dynamic SQL? Will the dynamic statement be created by the interface, or within the stored procedure based upon the parameter clause passed? Your example is completely hard-coded for two specific logical clauses. I just don't see this being pratically extensible to an enterprise application.

    gorgenyi, you titled your post "simple SQL query or design problem?" Well, I think you can see that it is NOT a simple SQL query, but is a design problem as I suggested before.

    On the plus side, if you continue with this it is guaranteed to keep you busy and employed for months to come. Have fun with it.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by blindman
    Ok, I've taken your invitation and reviewed your code, and the only description I can come up with is "Obfuscatory".

    Why do you use unnecessary nested queries? This statement:
    Code:
    select	*
    into	#t
    from	(SELECT	p.peopId,
    		p.peopFName,
    		p.peopSName,
    		c.codeId,
    		c.codeName
    	FROM	#people p 
    		INNER JOIN #codedPeople d ON p.peopId = d.peopId
    		INNER JOIN #codes c ON c.codeId = d.codeId) as t1
    is more simply stated as
    Code:
    SELECT	p.peopId,
    	p.peopFName,
    	p.peopSName,
    	c.codeId,
    	c.codeName
    into	#t
    FROM	#people p 
    	INNER JOIN #codedPeople d ON p.peopId = d.peopId
    	INNER JOIN #codes c ON c.codeId = d.codeId
    And in this statement:
    Code:
    select	#t.*
    from	(select	min(peopFName) as peopFName
    	from	#t
    	where	codeName in ('t-CEO','t-Founder') 
    	group by peopFName
    	having	count(*)>
    		(select	count(*)-1
    		from	#codes
    		where codeName in ('t-CEO','t-Founder'))) as tm,
    	#t
    where	tm.peopFName=#t.peopFName
    	and #t.codeName in ('t-CEO','t-Founder')
    ...the use of min(peopFName) is pointless as you are grouping by peopFName, the subtraction of 1 from your count total can be avoided by simply using >= instead of >, and lastly you should be using standard ANSI JOINs to link your tables for clarity instead of WHERE clauses. Like this:
    Code:
    select	#t.*
    from	#t
    	inner join --tm
    		(select	peopFName
    		from	#t
    		where	codeName in ('t-CEO','t-Founder') 
    		group by peopFName
    		having	count(*)>=
    			(select	count(*)
    			from	#codes
    			where codeName in ('t-CEO','t-Founder'))) as tm
    		on #t.peopFName = tm.peopFName
    where	#t.codeName in ('t-CEO','t-Founder')
    Same issues apply with you UNION clause. (Not even going to go into the potenial problems with "select *...")

    But my big concern with this is that it does not seem to satisfy the poster's requirement of submitting the WHERE clause as a parameter (presumably a string). Is this going to be all dynamic SQL? Will the dynamic statement be created by the interface, or within the stored procedure based upon the parameter clause passed? Your example is completely hard-coded for two specific logical clauses. I just don't see this being pratically extensible to an enterprise application.

    gorgenyi, you titled your post "simple SQL query or design problem?" Well, I think you can see that it is NOT a simple SQL query, but is a design problem as I suggested before.

    On the plus side, if you continue with this it is guaranteed to keep you busy and employed for months to come. Have fun with it.



    So given the initially mentioned 3 tables of people, code, codedPeople, what would your implementation be? - I'm not having much fun with this one actually

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would STRONGLY encourage you not to allow mixes of AND and OR operations in your filter. Make the user choose one or the other. Then call a separate sproc depending on their choice.
    OR clauses are easy, though optimizing them may be tricky.
    AND clauses can be handled using subcounts similar to what mallier proposed. I've had success in the past by having the interface store the various AND values in a temporary table and then referencing the temp table in your joins, though you have to ensure that the same connection is used for both actions, and that each user has a different connection.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  13. #13
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by blindman
    I would STRONGLY encourage you not to allow mixes of AND and OR operations in your filter. Make the user choose one or the other. Then call a separate sproc depending on their choice.
    OR clauses are easy, though optimizing them may be tricky.
    AND clauses can be handled using subcounts similar to what mallier proposed. I've had success in the past by having the interface store the various AND values in a temporary table and then referencing the temp table in your joins, though you have to ensure that the same connection is used for both actions, and that each user has a different connection.




    Thanks for this! I much appreciate your input. This project has been an enormous learning curve!

    So you do agree though with the table structure as it is. Or would you have a completely different approach?

    It did cross my mind to de-normalise and have a free text based column in the people table, where the ASP.NET logic puts the given strings as comma separated values, on which then I can simply search / filter.

    I would say in 99% users wouldnít want to mix ĎANDí and ĎORí operators, as it has no practical sense, so thatís a good point.

    All in all, Iíd really love to know if you agree with the table implementation or if youíd change it.

    Thanks again!

  14. #14
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Your design is in fine 3rd normal form, and I would leave it that way. The only issue I might bring up is the surrogate key on the codedPeople table. As you defined it, this table is nothing more than the implementation of a many-to-many join between the codes table and the people table. Logically, it is hardly a table at all; you could think of it as a work around for the lack of a "many-to-many" foreign key type in SQL. Your natural key on this is going to be a composite of the peopleID and the codeID, so unless there are coding reasons for creating a codePeopleId I would just set your natural key as your primary key and drop the surrogate key altogether.

    Do NOT "de-normalise and have a free text based column in the people table, where the ASP.NET logic puts the given strings as comma separated values" unless you agree never to post on the forum again. We will find you. We know where you live, we know where you work, we know where you shop. We run databases, remember?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  15. #15
    Join Date
    Mar 2005
    Posts
    92
    Quote Originally Posted by blindman
    Your design is in fine 3rd normal form, and I would leave it that way. The only issue I might bring up is the surrogate key on the codedPeople table. As you defined it, this table is nothing more than the implementation of a many-to-many join between the codes table and the people table. Logically, it is hardly a table at all; you could think of it as a work around for the lack of a "many-to-many" foreign key type in SQL. Your natural key on this is going to be a composite of the peopleID and the codeID, so unless there are coding reasons for creating a codePeopleId I would just set your natural key as your primary key and drop the surrogate key altogether.

    Do NOT "de-normalise and have a free text based column in the people table, where the ASP.NET logic puts the given strings as comma separated values" unless you agree never to post on the forum again. We will find you. We know where you live, we know where you work, we know where you shop. We run databases, remember?


    Ok, so I know the design is good enough, I can sleep well tonight!

    I'll have a go at implementing the logic that handles whether AND or OR is used and let you know how I got on! Thanks for discussion and also for query for AND operator, it's been an enormous help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •