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

34 Answered Questions

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

25 Answered Questions

24 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2053756 View
  • 1380 Score
  • 24 Answer
  • Tags:   sql duplicates

18 Answered Questions

[SOLVED] PostgreSQL "DESCRIBE TABLE"

37 Answered Questions

29 Answered Questions

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

19 Answered Questions

[SOLVED] Drop all tables in PostgreSQL?

  • 2010-07-24 23:24:05
  • AP257
  • 452951 View
  • 756 Score
  • 19 Answer
  • Tags:   postgresql

18 Answered Questions

[SOLVED] Show tables in PostgreSQL

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

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

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

41 Answered Questions

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

Sponsored Content