Results 1 to 5 of 5

Thread: Search

  1. #1
    Join Date
    Sep 2010
    Posts
    2

    Unanswered: Search

    I need to search the database based on some conditions for which i need a query. The situation is following

    City table having CityID as primary key and CityName
    PracticeArea table having PracticeAreaID as primary key and PracticeAreaName
    PracticeCourts table having PracticeCourtID as primary key and CourtName

    Lawyers Table having LawyerID as primary key and CityID as foreign key from city, LawyerName and some other info regarding lawyers.

    LawyersPracticeArea table having LawyerID as foreign key from lawyers and PracticeAreaID as foreign key from Practicearea (this table shows that which lawyers are practicing in which practice areas) one lawyer can practice in many areas

    LawyerPracticeCourt having lawyerID as foreign key from lawyers and PracticeCourtID as foreign key from PracticeCourt (this table shows that which lawyers are practicing in which court) one lawyer can practice in many courts

    Now there are 3 dropdown lists on web page City, practice area and practice court user may select city = A, practice area = Any, Practice court = Any
    or City = Any, PArea = B, PCourt = c

    Idea is to find all lawyers having the three selected criteria.

    Can any one please tell me what will be the query? i am too bad in making query having joins.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    I accept VISA and PayPal payments for solving school tasks

    You will have to adjust the WHERE clause, depending on user input.

    Code:
    CREATE TABLE City(
    	CityID	int not null,
    	CityName char(20)
    	)
    
    CREATE TABLE PracticeArea(
    	PracticeAreaID	int not null,
    	PracticeAreaName char(20)
    	)
    	
    CREATE TABLE PracticeCourts(
    	PracticeCourtID	int not null,
    	CourtName char(20)
    	)
    	
    CREATE TABLE Lawyers(
    	LawyerID	int not null,
    	CityID	int not null,
    	LawyerName char(20)
    	)
    
    CREATE TABLE LawyersPracticeArea(
    	LawyerID	int not null,
    	PracticeAreaID	int not null
    	)
    
    CREATE TABLE LawyerPracticeCourt(
    	LawyerID	int not null,
    	PracticeCourtID	int not null
    	)
    
    SELECT Lawyers.LawyerID, Lawyers.LawyerName, City.CityName, PA.PracticeAreaName, PC.CourtName
    FROM Lawyers
    	INNER JOIN City ON
    		Lawyers.CityID = City.CityID
    	INNER JOIN LawyersPracticeArea as LPA ON
    		Lawyers.LawyerID = LPA.LawyerID
    	INNER JOIN PracticeArea as PA ON
    		LPA.PracticeAreaID = PA.PracticeAreaID
    	INNER JOIN LawyerPracticeCourt as LPC ON
    		Lawyers.LawyerID = LPC.LawyerID
    	INNER JOIN PracticeCourts as PC ON
    		LPC.PracticeCourtID = PC.PracticeCourtID
    WHERE	--City.CityID = 1 AND
    		PA.PracticeAreaID = 2 AND
    		PC.PracticeCourtID = 3
    
    DROP TABLE City
    DROP TABLE PracticeArea
    DROP TABLE PracticeCourts
    DROP TABLE Lawyers
    DROP TABLE LawyersPracticeArea
    DROP TABLE LawyerPracticeCourt
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  3. #3
    Join Date
    Sep 2010
    Posts
    2
    First of All thanks a lot for reply and a Free query

    Well students are the poorest creation ever made on earth. They only PAY every where! School, college, university, Tuition and now the web is demanding from them as well

    Can you please tell me how to handle the "any" situation. do i need to have subquery in where clause? i mean where city is any...

    Thanks.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ShiningTears View Post
    Can you please tell me how to handle the "any" situation.
    The "any" option in dynamic search SQL, Part 1

    The "any" option in dynamic search SQL, Part 2

    Code:
    select a, b, c, d, e
      from sometables
     where 1 = 1
        if a is not "any"
       and a = a
        endif
        if b is not "any"
       and b = b
        endif
        if c is not "any"
       and c = c
        endif
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Quote Originally Posted by ShiningTears View Post
    First of All thanks a lot for reply and a Free query
    "Free query", what query are you referring to? I hope you were not referring to My Query™ © ® (though I'm pretty sure my lawyer silently is wishing you are).
    Well students are the poorest creation ever made on earth. They only PAY every where! School, college, university, Tuition and now the web is demanding from them as well
    You almost forgot to mention the amounts they are forced (often at gunpoint) to spend in bars, restaurants, dancings, ... No-one has ever said life would be easy or cheap for students.
    Can you please tell me how to handle the "any" situation. do i need to have subquery in where clause? i mean where city is any...
    You could alter My Where Clause™ © ® in
    Code:
    WHERE	City.CityID = COALESCE(@CityId, City.CityID) AND
    	PA.PracticeAreaID = COALESCE(@PracticeAreaID, PA.PracticeAreaID) AND
    	PC.PracticeCourtID = COALESCE(@PracticeCourtID, PC.PracticeCourtID)
    r937's solution is suited for dynamic SQL. With the above solution you can use static SQL. I don't know what solution will be the fastest, probably r937's solution.

    All My Queries™ © ® and My Clauses™ © ® can be licensed, not bought.

    Excerpt from the EULA.

    Subject to the terms of this EULA, WIM hereby grants you a non-exclusive, non-transferable license, without rights to sublicense....

    You may not (i) sell, lease, license, sublicense, distribute or otherwise transfer in whole or in part the Queries and/or Clauses to another party; (ii) provide, disclose or make available to, or permit use of the Queries and/or Clauses in whole or in part by, any third party without WIM’s prior written consent...

    Limited Warranty. WIM provides his Queries and/or Clauses without any warranties of any kind, express, implied, statutory, or in any other provision of this eula or communication with you, and WIM specifically disclaims any implied warranties of merchantability, fitness for a particular purpose, and non-infringement....


    Since I have left Java and Python programming, I face this kind of EULA's more and more. And after each EULA I read, I feel myself dragged a bit away. To a place where helping out someone else without expecting a direct benefit from that act is considered naive, laughable, not-done, plain wrong, ...

    I accept VISA and PayPal payments for solving school tasks.
    This is not a place where people will do your homework. The normal reaction on this forum is: "Show us what you have found so far."
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

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