Help with sql query LEFT join - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

Help with sql query LEFT join

Author

Topic

said76
Starting Member

2 Posts

Posted - 2011-05-05 : 20:42:09

Hi,

I wonder if I could get some help with my problem with sql query.

I am creating a database that would keep all the records of our meeting minutes using Access 2010.

Let me first apologize if I post this here.

I have created 4 tables: MeetingMinutes, Agendas, Tasks and SubTasks

- MeetingMinutes Table:
MeetingMinutes_ID (PK or Primary Key)
MeetingMinutes_No
MinutesDate
Attendees

- Agendas Table:
Agenda_ID (PK)
Agenda_No
Agenda_Items
MeetingMinutes_ID (FK or Foreign Key)

- Tasks Table:
Tasks_ID (PK)
Tasks_No
Tasks_Name
Tasks_Actioner
Agenda_ID (FK)

- SubTasks Table:
STasks_ID (PK)
STasks_No
STasks_Name
STasks_Actioner
Tasks_ID (FK)

And my sql query is
SELECT MeetingMinutes.MeetingMinutes_ID, meetingminutes.MeetingMinutes_No, meetingminutes.MinutesDate, meetingminutes.Attendees, Agendas.Agenda_No, Agendas.Agenda_Items, Tasks.Tasks_No, Tasks.Tasks_Name, Tasks.Task_Actioner, SubTasks.STask_No, SubTasks.STask_Items, SubTasks.STask_Actioner, *
FROM ((MeetingMinutes INNER JOIN Agendas ON MeetingMinutes.MeetingMinutes_ID = Agenda.MeetingMinutes_ID) LEFT JOIN Tasks ON Agendas.Agenda_ID = Tasks.Agenda_ID) LEFT JOIN SubTasks ON Tasks.Tasks_ID = SubTasks.Tasks_ID;

The problem I have is I get a lot of empty lines when sending to the report. I think this might be to do with the LEFT join there but I just do not know what else to use. The inner join does not work.

For example, this is what I see in the report

Minutes #1
agendas
1. Hello World number 1
1.1
1.1.

2. Hello World number 2
2.1 Hello World number 2.1
2.1.1 Hello World number 2.1.1

As you can see, I am getting two empty lines in the agenda item number 1. My guess is this happens because in the agenda item no.2, there are more sub-items than that in the agenda item no.1

I wonder how I would tweak my sql query that it would produce only if it sees there are values or entries.

Any help would be greatly appreciated.

Thank you in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-05 : 21:26:50

Without knowing the business logic, it is hard for me to figure out the reason for blank rows, but I would suggest the following. Start with only one table. For example, MeetingMinutes table. Remove all the columns from other tables and the joins from your query. So your query will be like this:


SELECT 
   MeetingMinutes.MeetingMinutes_ID, 
   meetingminutes.MeetingMinutes_No, 
   meetingminutes.MinutesDate, 
   meetingminutes.Attendees
from
   MeetingMinutes

Take a look at the results to see if that makes sense - whether there are blank rows in it etc.

Assuming that it looks ok, now add the next table and its columns to the query. For example, the Agendas table. Then compare the number of rows returned, and whether there are blank records etc.

Doing it this way, one table at a time, will let you see where the joins are causing unwanted results/rows.

said76
Starting Member

2 Posts

Posted - 2011-05-08 : 19:52:10

Thanks for your reply.

I'm sorry that I have not provided you with enough info so that you might be able to help me out.

Basically, the logic behind this is a meeting minutes can have more than one agenda entry. Each of the agenda items can have more than one task. Each task can have more than one sub-task.

So based on the tables I showed in the previous thread, I have four tables made up namely MeetingMinutes, Agendas, Tasks and SubTasks tables.

So here is how the relationships goes between all the tables. I have the Agendas table joined with MeetingMinutes on MeetingMinutes_ID as a foreign key. Then, the Tasks table joined on Agendas via Agendas_ID. The Tasks joined the SubTask on Tasks_ID.

After linking them all together, I created a report to show the minutes.

I actually did as you advised me to do by starting with one table and then two, three and four, and it all works fine.

What does not work is I got empty entries in the report especially in the events as follows

for example, in a meeting minutes, assuming there are two entries of agendas.
Agenda_1 has only one task and that's it.
Agenda_2, on the other hand, has one task and two subtasks entries.

In the report, I received something like this

- Agenda 1 Clean Kitchen
Task 1-1 Dish Washing
Task 1-1-

- Agenda 2 blah blah
Task 2-1 blah blah
Task 2-2 blah blah

As you can see from the above, I do not miss anything there but in the agenda 1, it still shows an empty entry just because in the agenda 2, it has subtask entry.

I do not know if I have to do something with the Access report so that it will not show if the value of the text box is null.

Many thanks in advance

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-08 : 22:27:45

I created test tables in Access (my version is 2007, but I don't think that should make any difference). Then I populated it with sample data and ran your query. I don't see the behavior you are seeing. See my script and the results below. See if that will give you any clues - I suspect the problem is somewhere else outside of the query itself. (In the code below, I wrote the query for SQL server, but exactly the same code works on Access)


-- CREATE THE TABLES
CREATE TABLE MeetingMinutes( MeetingMinutes_ID INT, MeetingInfo VARCHAR(32));
CREATE TABLE Agendas (Agenda_ID INT, AgendaInfo VARCHAR(32), MeetingMinutes_ID INT);
CREATE TABLE Tasks (Task_ID INT, TaskInfo VARCHAR(32), Agenda_ID INT);
CREATE TABLE SubTasks (STasks_ID INT, STaskInfo VARCHAR(32), Task_ID INT);

-- INSERT DATA
INSERT INTO MeetingMinutes VALUES (1, 'Meeting1');

INSERT INTO Agendas VALUES (1, 'Cleaning Kitchen', 1);
INSERT INTO Agendas VALUES (2, 'Blah blah', 1);

INSERT INTO Tasks VALUES (1, 'KitchenTask', 1);
INSERT INTO Tasks VALUES (2, 'Blah Blah task', 2);

INSERT INTO SubTasks VALUES (1, 'Blah Blah Subtask1', 2);
INSERT INTO SubTasks VALUES (2, 'Blah Blah Subtask2', 2);

-- SELECT STATEMENT
SELECT
	MeetingMinutes.MeetingMinutes_ID,
	MeetingMinutes.MeetingInfo,

	Agendas.Agenda_ID,
	Agendas.AgendaInfo,
	Tasks.Task_ID,
	Tasks.TaskInfo,
	SubTasks.STasks_ID,
	SubTasks.STaskInfo

FROM
	(
	    (
	        MeetingMinutes INNER JOIN Agendas ON MeetingMinutes.MeetingMinutes_ID 
	        = Agendas.MeetingMinutes_ID
	    ) LEFT JOIN Tasks ON Agendas.Agenda_ID = Tasks.Agenda_ID
	)
	LEFT JOIN SubTasks
		ON  Tasks.Task_ID = SubTasks.Task_ID;

-- CLEANUP		
DROP TABLE MeetingMinutes;
DROP TABLE Agendas;
DROP TABLE Tasks;
DROP TABLE SubTasks;

And here are my results:


MeetingMinutes_ID    MeetingInfo    Agenda_ID    AgendaInfo    Task_ID    TaskInfo    STasks_ID    STaskInfo
1                    Meeting1       1            Cleaning Kitchen    1    KitchenTask    NULL    NULL
1                    Meeting1       2            Blah blah           2    Blah Blah task    1    Blah Blah Subtask1
1                    Meeting1       2            Blah blah           2    Blah Blah task    2    Blah Blah Subtask2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources