By Lithicas


2018-05-16 15:03:34 8 Comments

I’ve ran in to some problems with the following SQL assignment. I’m to retrieve the ID, firstname and lastname of any member who is registered in section that contains the word ‘xyz’.

So far I’ve managed the following:

SELECT m.id, p.firstname, p.lastname FROM member m
INNER JOIN person p ON m.id = p.id
WHERE m.id IN (SELECT id FROM membersection);

How do I go forward from here? I have no idea how to retrieve the sectionid from the membersection table then fetch the section name from the section id using that ID so I can check if the section name contains the previously stated word.

member:

id member_number registration_date

membersection:

memberid sectionid

person:

id firstname lastname

section:

id name

3 comments

@Jiggles32 2018-05-16 15:13:56

It's not super obvious what's going on with your Data Relationships, but this would be the basic route you might want to take (LEFT JOIN as I do not know the relationship):

SELECT m.id, p.firstname, p.lastname, ms.sectionid
FROM member m
INNER JOIN person p ON m.id = p.id 
LEFT JOIN section s ON m.id = s.id
LEFT JOIN membersection ms ON m.id = ms.memberid
WHERE s.name = 'xyz'

@Jeff Breadner 2018-05-16 15:13:53

There is some ambiguity in your question with regards to how your data are structured; what are the primary and foreign keys?

But, making some assumptions, you're almost there, you can chain multiple join statements together:

select
  m.id,
  p.firstname,
  p.lastname
from
  member m
  inner join person p on
    m.id = p.id
  inner join membersection ms on
    m.id = ms.memberid
  inner join section s on
    ms.sectionid = s.id
where
  s.name like '%xyz%'

@sticky bit 2018-05-16 15:12:51

Just keep joining. And in the end use LIKE to check if section.name contains 'xyz'.

SELECT m.id,
       p.firstname,
       p.lastname
       FROM member m
            INNER JOIN person p
                       ON p.id = m.id
            INNER JOIN membersection ms
                       ON ms.memberid = m.id
            INNER JOIN section s
                       ON s.id = ms.sectionid
       WHERE s.name LIKE '%xyz%';

Related Questions

Sponsored Content

25 Answered Questions

18 Answered Questions

[SOLVED] Show tables in PostgreSQL

  • 2009-04-20 19:07:39
  • flybywire
  • 1108425 View
  • 1204 Score
  • 18 Answer
  • Tags:   postgresql

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 1892117 View
  • 943 Score
  • 19 Answer
  • Tags:   sql oracle

33 Answered Questions

[SOLVED] Fetch the row which has the Max value for a column

36 Answered Questions

29 Answered Questions

[SOLVED] Find all tables containing column with specified name - MS SQL Server

17 Answered Questions

[SOLVED] PostgreSQL "DESCRIBE TABLE"

  • 2008-09-20 20:47:48
  • Mr. Muskrat
  • 872015 View
  • 1488 Score
  • 17 Answer
  • Tags:   postgresql

41 Answered Questions

[SOLVED] Table Naming Dilemma: Singular vs. Plural Names

23 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 1948202 View
  • 1315 Score
  • 23 Answer
  • Tags:   sql duplicates

19 Answered Questions

[SOLVED] Drop all tables in PostgreSQL?

  • 2010-07-24 23:24:05
  • AP257
  • 432736 View
  • 727 Score
  • 19 Answer
  • Tags:   postgresql

Sponsored Content