Results 1 to 15 of 15
  1. #1
    Join Date
    Feb 2012
    Posts
    10

    Exclamation Unanswered: combine two table records by first name

    Hello Senior,
    I am so confuse to get the records from the table.I am giving u clear idea what i mean. I have 2 tables First is Student_detail and another is Employee_detail. Student_detail have 14 fields like (stud_Firstname,stud_Lastname...) and Employee_detail have 17 fields like(emp_Firstname,emp_Lastname...).there is no relationship between these two table and also not in a relationship with any other table in my database.This is a structure of my db. but i want to get the records from these two table whose first name is same for both the tables.as well as the result of this query will first show me Student_detail record first and then Employee_detail record.but not in a one row.it should be display in one by one.

    Like this way:
    HTML Code:
    
    Student_detail :-
    
    stud_First_name    stud_Last_name      std_city      ........
    Shrikant                Joshi           Jalgaon      ........
    Yogesh                  Trivedi         Malkapur     ........
    
    Employee_detail:-
    
    emp_First_name     emp_Last_name       emp_city       ..........
    Tushar                   Patil          Mumbai        ..........
    Shrikant                 Rane           Nasik         ..........
    
    Result of a query:-
    First_name            Last_name        City           ..........
    Shrikant                Joshi            Jalgaon      .........
    Shrikant                Rane             Nasik        .........
    It's my pleasure to share my problem with this forum.

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    In pseudo code:

    Join the two tables, select the student columns and add one column "'S' as Source" (Student record)
    UNION
    Join the two tables, select the corresponding employee and add one column "'E' as Source" (Employee)
    ORDER BY first name, Source DESC
    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
    Feb 2012
    Posts
    10

    Red face

    Mr.Wim,
    I am new in sql queries please will u help me to make a query for your given post.

  4. #4
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Code:
    SELECT 'S' AS Source,
    	stud_First_name as First_name,
    	stud_Last_name as Last_name,
    	stud_city as City
    FROM Student_detail 
    	INNER JOIN Employee_detail ON
    		stud_First_name = emp_First_name
    UNION
    SELECT 'E' AS Source,
    	emp_First_name as First_name,
    	emp_Last_name as Last_name,
    	emp_city as City
    FROM Student_detail 
    	INNER JOIN Employee_detail ON
    		stud_First_name = emp_First_name
    ORDER BY 2, 1 DESC
    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

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Is this what you are looking for?

    Code:
    CREATE TABLE #Student (
    	  First_Name	varchar(50)
    	, Last_Name		varchar(50)
    	, City			varchar(50)
    )
    
    CREATE TABLE #Employee (
    	  First_Name	varchar(50)
    	, Last_Name		varchar(50)
    	, City			varchar(50)
    )
    GO
    
    INSERT INTO #Student (First_Name, Last_Name, City)
    SELECT 'Shrikant', 'Joshi',   'Jalgaon' UNION ALL
    SELECT 'Yogesh',   'Trivedi', 'Malkapur'
    
    INSERT INTO #Employee  (First_Name, Last_Name, City)
    SELECT 'Tushar',   'Patil',   'Mumbai' UNION ALL
    SELECT 'Shrikant', 'Rane',    'Nasik'
    GO
    
    SELECT * FROM (SELECT * FROM  #Student UNION ALL SELECT * FROM  #Employee) AS d
    WHERE First_Name IN
    			(SELECT First_Name 
    			   FROM (
    					  SELECT First_Name FROM #Student
    				   UNION ALL
    					  SELECT First_Name FROM #Employee) AS XXX
    					GROUP BY First_Name
    					  HAVING COUNT(*) > 1
    			) 
    GO
    
    DROP TABLE #Student, #Employee
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Feb 2012
    Posts
    10

    Thumbs up

    Hello Senior,
    I have found a solution of my problem.Actually i used datatype "text" for each of field in my db. as a Mr.Brett Kaiser use datatype "varchar(50)" for the given fields. So i changed my db fields datatype of each as "varchar(50)" and fire a query suggested by Mr.Wim. and I got my Answer

    Thanks to Both of You Senior.

  7. #7
    Join Date
    Feb 2012
    Posts
    10

    Unhappy

    Hello Wim,
    now i again got problem.I want to use all fields of my table then how can i use your query.
    SELECT 'S' AS Source,
    stud_First_name as First_name,
    stud_Last_name as Last_name,
    stud_city as City
    FROM Student_detail
    INNER JOIN Employee_detail ON
    stud_First_name = emp_First_name
    UNION
    SELECT 'E' AS Source,
    emp_First_name as First_name,
    emp_Last_name as Last_name,
    emp_city as City
    FROM Student_detail
    INNER JOIN Employee_detail ON
    stud_First_name = emp_First_name
    ORDER BY 2, 1 DESC

  8. #8
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Would you like to use mine now?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  9. #9
    Join Date
    Feb 2012
    Posts
    10
    Mr.Brett Kaiser
    I used your query and it's work well as i want but if i want to see all the records of the table then how can see it?

  10. #10
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Quote Originally Posted by swapnil24 View Post
    Mr.Brett Kaiser
    I used your query and it's work well as i want but if i want to see all the records of the table then how can see it?

    First you need to tell me what that means

    expand on your sample data and expected results please
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  11. #11
    Join Date
    Feb 2012
    Posts
    10
    Hello Senior,
    Thanks to Brett Kaiser I have found a solution. This is done by the help of Knowledgeable senior's. once again Thank you very much

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    De nada

    This massage is too short
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Senior Brett,

    You ran away with a beer that could have come my way.
    You're on my radar now.


    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

  14. #14
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    On a Whim I only grabbed a beer, when really I prefer tequila I fear
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  15. #15
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Wim View Post
    Senior Brett,

    You ran away with a beer that could have come my way.
    You're on my radar now.


    I'll buy Wim a beer (or two) and take Brett to JJ's for Margaritas. Then everyone can be happy!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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