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

19 Answered Questions

[SOLVED] Show tables in PostgreSQL

  • 2009-04-20 19:07:39
  • flybywire
  • 1303163 View
  • 1365 Score
  • 19 Answer
  • Tags:   postgresql

21 Answered Questions

[SOLVED] Drop all tables in PostgreSQL?

  • 2010-07-24 23:24:05
  • AP257
  • 480691 View
  • 785 Score
  • 21 Answer
  • Tags:   postgresql

26 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2199443 View
  • 1489 Score
  • 26 Answer
  • Tags:   sql duplicates

30 Answered Questions

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

19 Answered Questions

[SOLVED] PostgreSQL "DESCRIBE TABLE"

34 Answered Questions

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

25 Answered Questions

37 Answered Questions

19 Answered Questions

[SOLVED] Get list of all tables in Oracle?

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

41 Answered Questions

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

Sponsored Content