Multiple Column Select From Different Tables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Multiple Column Select From Different Tables

Author

Topic

tha1mukky
Starting Member

3 Posts

Posted - 2012-05-04 : 21:29:01

Hi, i have a problem that i need to solve fast. I am using Sql Server 2008, i have created my database, tables and populated all the tables. The problem is (As as administrator, i need to produce a complete list of all pupils of all grades in one school who are absent on a certain date and listed in the order from grade 1 to 13). I have 8 tables, but I thing i should be concentrating on pupil, school and absence_report tables. Hope to hear from someone really soon, thanks.

MMa129

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2012-05-04 : 22:55:50

You have not provided enough information to answer this question. Please provide the structure of the tables and sample data.
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Success is 10% Intelligence, 70% Determination, and 22% Stupidity.
\_/ _/ _/\_/ _/\_/ _/ _/- 881

tha1mukky
Starting Member

3 Posts

Posted - 2012-05-05 : 03:18:28

Hi Vinnie, thanks for your reply, let me post the tables structure and sample data.
Tables
Pupil Table
CREATE TABLE PUPIL
(PUPIL_ID VARCHAR(10) NOT NULL PRIMARY KEY,
NAME VARCHAR(35) NOT NULL,
GENDER VARCHAR(6) NOT NULL,
FORM VARCHAR(10) NOT NULL,
TERM VARCHAR(10) NOT NULL,
SCHOOL_ID VARCHAR(10) NOT NULL
FOREIGN KEY(SCHOOL_ID) REFERENCES SCHOOL(SCHOOL_ID));
GO

PUPIL_ID NAME GENDER FORM TERM SCHOOL_ID
AC228 Alvin Cetwater Male 6 3rd HMS013

School
CREATE TABLE SCHOOL
(SCHOOL_ID VARCHAR(10) NOT NULL PRIMARY KEY,
NAME VARCHAR(35) NOT NULL,
ADDRESS VARCHAR(60) NOT NULL,
PHONE VARCHAR(15) NOT NULL,
EMAIL VARCHAR(25) NOT NULL);
GO

SCHOOL_ID NAME ADDRESS PHONE EMAIL
CSA095 Capital Science Academy 40 Lafone Ho Lafone Street, Edinburgh, SE1 2NA 1227845930 csa@gmail.com

Absence_Report
CREATE TABLE ABSENCE_REPORT
(REF_ID VARCHAR(10) NOT NULL PRIMARY KEY,
PARENT_ID VARCHAR(10) NOT NULL,
PUPIL_ID VARCHAR(10) NOT NULL,
SCHOOL_ID VARCHAR(10) NOT NULL,
FORM VARCHAR(10) NOT NULL,
REASON VARCHAR(40) NOT NULL,
REPORT_DATE VARCHAR(12) NOT NULL,
ABSENCE_DATE VARCHAR(12) NOT NULL,
RETURN_DATE VARCHAR(12) NOT NULL
FOREIGN KEY(PUPIL_ID) REFERENCES PUPIL(PUPIL_ID));
GO

REF_ID PARENT_ID PUPIL_ID SCHOOL_ID FORM REASON REPORT_DATE ABSENCE_DATE RETURN_DATE
AC228/6 JC10-AC228 AC228 HMS013 6 Traveling 18-05-2010 19-05-2010 24-05-2010

MMa129

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-05 : 10:09:57

You would do something like this:

DECLARE @school_id INT = 1234;
DECLARE @date date = '20120502';

SELECT
	ar.pupil_id,
	p.NAME
FROM
	Absence_Report AR
	INNER JOIN Pupil p ON p.pupil_Id = ar.pupil_id
WHERE
	p.school_id = @school_id
	AND ar.absent_date = @date
ORDER BY
	p.name

Couple of comments though.

1. I don't see any column that tells you what grade a student is in.
2. If you have the ability to redesign the tables, use proper data types for the columns - for example, REPORT_DATE, ABSENCE_DATE etc. should be DATE data type rather than VARCHAR data type.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources