Mysql Join Problem

[Nandini]

Hi

 

I have a two mysql tales as follows

 

Table Teachers:

 

teacher_idfirst_namelast_namestate

1testTHI

2testT2LA

 

 

Table Subjects:

 

teacher_idmain_subjectsub_subjectISBN

1MathsElemntary Maths452635

1MathsAlgebra452336

2EnglishGrammer645455

2EnglishVocabulary7435435

 

Now i want to display, who is teacher having first_name as test and having main_subject as Maths and sub_subject as Elemntary Maths .

 

I am using the following code. But i am getting two rows.

 

select a.*,b.* from Teachers a,Subjects b where a.first_name = 'test' && b.main_subject = 'Math' && b.sub_subject = 'Elementary Math'

 

How can i do this. please tell me

 

[cssfreakie]

You could very well have made a little type.

try the following:

 

SELECT * from teachers join subjects on teachers.teacher_id = subjects.teacher_id
and
main_subject = 'Maths' and sub_subject = 'Elementary Maths'

This should give 1 result.

 

Note the spelling of Elementary Maths...

Also note I added a primary key for Subjects. Also i don't see the logic of storing an isbn number in there (they are bound to a book), nor why you don't make a sub table for sub_subjects

 

I made a little dump on my local host you can directly try import it in phpmyadmin to test.

 

-- phpMyAdmin SQL Dump
-- version 3.3.9
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Jun 15, 2011 at 01:52 PM
-- Server version: 5.5.8
-- PHP Version: 5.3.5

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `teachersDB`
--

-- --------------------------------------------------------

--
-- Table structure for table `subjects`
--

CREATE TABLE IF NOT EXISTS `subjects` (
  `subject_id` int(11) NOT NULL AUTO_INCREMENT,
  `teacher_id` int(11) NOT NULL,
  `main_subject` varchar(50) NOT NULL,
  `sub_subject` varchar(50) NOT NULL,
  `isbn` int(13) NOT NULL,
  PRIMARY KEY (`subject_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

--
-- Dumping data for table `subjects`
--

INSERT INTO `subjects` (`subject_id`, `teacher_id`, `main_subject`, `sub_subject`, `isbn`) VALUES
(1, 1, 'Maths', 'Elementary Maths', 452635),
(2, 1, 'Maths', 'Algebra', 452336),
(3, 2, 'English', 'Grammer', 645455),
(4, 2, 'English', 'Vocabulary', 7435435);

-- --------------------------------------------------------

--
-- Table structure for table `teachers`
--

CREATE TABLE IF NOT EXISTS `teachers` (
  `teacher_id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) NOT NULL,
  `last_name` varchar(50) NOT NULL,
  `state` varchar(2) NOT NULL,
  PRIMARY KEY (`teacher_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- Dumping data for table `teachers`
--

INSERT INTO `teachers` (`teacher_id`, `first_name`, `last_name`, `state`) VALUES
(1, 'test', 'T', 'HI'),
(2, 'test', 'T2', 'LA');

[redixx]

I made a little dump on my local host

 

This made me laugh. 

[cssfreakie]

Language, the greatest invention ever

[Nandini]

thanks its working

But how can i retrieve teacher details who is not in subjects table. All this information should come by using single query. Why because i am using in search.