Why does my Query return "Empty set (0.00 sec)"?

Question

SOLVED-run UPDATE building SET building_name = "Main Street Building" WHERE building_id = 2; From there, the task 3 query will return proper results.

Task One:

Query:

SELECT first_name, last_name, building_name, room.room_id, meeting_start,meeting_end
FROM meeting, person, person_meeting,room, building
WHERE room.room_id=meeting.room_id
AND meeting.meeting_id=person_meeting.meeting_id
AND person.person_id=person_meeting.person_id
AND room.building_id=building.building_id
AND person.first_name='Tom'
AND person.last_name='Hanks';

Task Two:

Query:

SELECT first_name, last_name, building_name, room.room_id, meeting_start,meeting_end
FROM meeting, person, person_meeting,room, building
WHERE room.room_id=meeting.room_id
AND meeting.meeting_id=person_meeting.meeting_id
AND person.person_id=person_meeting.person_id
AND room.building_id=building.building_id
AND meeting.meeting_id=2;

Task Three:

Query:

SELECT first_name, last_name, building_name, room.room_id,meeting.meeting_id, meeting_start, meeting_end
FROM meeting, person, person_meeting,room, building
WHERE room.room_id=meeting.room_id
AND meeting.meeting_id=person_meeting.meeting_id
AND person.person_id=person_meeting.person_id
AND room.building_id=building.building_id
AND building_name='Main Street Building';

Task Four:

Query:

SELECT count(person_id) 'Count of meeting attendees', meeting.meeting_id,meeting_start,meeting_end
FROM meeting, person_meeting
WHERE meeting.meeting_id=person_meeting.meeting_id
GROUP BY meeting.meeting_id;

Task Five:

Query:

SELECT first_name, last_name, meeting.meeting_id, meeting_start, meeting_end
FROM meeting INNER JOIN person_meeting ON meeting.meeting_id=person_meeting.meeting_id
INNER JOIN person ON person.person_id=person_meeting.person_id
AND meeting_start<'2016-12-25 12.00.00';

Task 1,2,4,5 and 5 all run perfectly. if you all need the task prompts, let me know.

I would advice that you use JOINs to fix your query. Start by just joining two tables, then add the others one by one. I believe that most of your queries can be done by using the LEFT JOIN — acarlstein, Apr 08 '19 at 17:58
Thank you so much, I definitely tried this, but I couldn't get that to work either. I am a newb to this. — Alabama TE 88, Apr 08 '19 at 18:37

acarlstein · Answer 1 · 2019-04-09T15:35:49.790

0

Note: I haven't test this yet.

I changed the queries based on our recent interaction.

This is your original

SELECT first_name, last_name, building_name, room.room_id,meeting.meeting_id, meeting_start, meeting_end    
FROM meeting, person, person_meeting,room, building    
WHERE room.room_id=meeting.room_id    
    AND meeting.meeting_id=person_meeting.meeting_id    
    AND person.person_id=person_meeting.person_id    
    AND room.building_id=building.building_id    
    AND building_name='Main Street Building';

This is the way I would try to build it

Based on page 2 and 3 in the document, you provided, https://snhu.brightspace.com/d2l/lor/viewer/viewFile.d2lfile/76437/9523,2/, I would do the following:

First, I would obtain information about the building.

SELECT building_id
FROM building
WHERE building_name like  '%Main Street Building%';

Note: I am assuming that the building name is correct. However, just in case, I am using the wildcard % before and after the name

Next, I would obtain the information about the room:

SELECT b.building_name, r.room_id
FROM room r
  LEFT JOIN building b
    ON r.building_id = b.building_id
WHERE b.building_name like  '%Main Street Building%';

Following that, I would obtain the information about the meeting:

SELECT b.building_name, r.room_id, m.meeting_id, m.meeting_start, m.meeting_end
FROM room r
  LEFT JOIN building b
    ON r.building_id = b.building_id
  LEFT JOIN meeting m
    ON r.room_id = m.room_id
WHERE b.building_name like  '%Main Street Building%';

Finally, I would obtain the information of the IDs of people that will be in that meeting and show their names:

SELECT b.building_name, r.room_id, m.meeting_id, m.meeting_start, m.meeting_end, p.first_name, p.last_name
FROM room r
  LEFT JOIN building b
    ON r.building_id = b.building_id
  LEFT JOIN meeting m
    ON r.room_id = m.room_id
  LEFT JOIN persom_meeting pm
    ON m.meeting_id = pm.meeting_id
  LEFT JOIN person p
    ON pm.person_id = p.person_id
WHERE b.building_name like  '%Main Street Building%';

If by any chance, these queries do not work, I would advice that you ensure that the relationships exists between the tables.

This means that there should be a building_id match, a room_id match, a meeting_id match and a person_id match between the tables. Plus, I would check that the building name is spelled correctly since its case-sensitive.

The reason I use LEFT JOINs is so I can display all the information of the previous tables (all the rows) plus the records in which the IDs match the foreign keys IDs. As explained here: http://www.acarlstein.com/?p=4168

Also, in http://www.acarlstein.com/?p=4194, I am showing how a Two Left (Outer)Joins would work.

However, in your case, it is more like a chain that is being build so it doesn't match the diagram but you can perhaps get the idea of what I mean.

edited Apr 09 '19 at 15:35

answered Apr 08 '19 at 18:07

acarlstein

1,799
2
13
21

Thank you so much, but it still returns empty set every single time. It has me stumped, because all of my other task, all worked seamlessly. – Alabama TE 88 Apr 08 '19 at 18:33
You are missing some id/foreign_key relationship between tables OR you don't have the data in the tables. – acarlstein Apr 08 '19 at 18:37
I would advice that you include the record (row) of each table that you want to have a relationship. Perhaps, that way we can help you. – acarlstein Apr 08 '19 at 18:38
I added all of my Queries, I can add the prompts if necessary too. Thank you so much for all the help. Sorry for the long page, but I tried to avoid it looking like a paragraph. – Alabama TE 88 Apr 08 '19 at 19:12
Since we don't have access to the data itself. Is there a way that you can add the single row (result) of each of these queries that you wish to match? I need the information to see if you are not getting results because there is no relationship (data not matching between tables). – acarlstein Apr 08 '19 at 19:27
Can I direct message you, so I can actually give you access to the data and the playground we use for this? – Alabama TE 88 Apr 08 '19 at 19:57
Sure, just give me the rows that should be related. – acarlstein Apr 08 '19 at 19:58
https://snhu.brightspace.com/d2l/lor/viewer/viewFile.d2lfile/76437/9523,2/ Let me know if you can access this document. – Alabama TE 88 Apr 08 '19 at 19:59
Here is a link to a photo of what the Output should look like. https://drive.google.com/open?id=1AjbBBEIGbJNN3KcsJ6i2WEQKcuA2Nx5U – Alabama TE 88 Apr 08 '19 at 20:11
Ok. Lets tackle the first row together, the one that has ```Tom``` as the first_name. Can you pull the row of each table that has that information? I want to see if the relationships matches. For example, do room.room_id matches meeting.room_id? – acarlstein Apr 08 '19 at 20:18
@AlabamaTE88, I updated the answer based on the document you shared. Let me know how it goes. – acarlstein Apr 09 '19 at 14:08
Thank you so much, I finally found it. I needed to run UPDATE building SET building_name = "Main Street Building" WHERE building_id = 2; – Alabama TE 88 Apr 12 '19 at 16:31
@AlabamaTE88 That's is awesome. So, the issue was a relationship. Glad you found that out. Leave your vote when you get your time please. Thanks. – acarlstein Apr 13 '19 at 13:38

Why does my Query return "Empty set (0.00 sec)"?

1 Answers1