How to query from database

Question

0.00/5 (No votes)

See more:

hi guys

I want to get records that haven't Age under 18

I have two tables family, members

family:

id	name
1	Famliy1
2	Famliy2
3	Famliy3
4	Famliy4

members:

id	fid	name	Age
1	1	AAAAA	11
2	1	BBBBB	19
3	1	CCCCC	14
4	2	DDDDD	18
5	2	EEEEEE	29
6	2	FFFFFF	23
7	3	GGGGG	13
8	3	HHHHH	14
9	3	IIIIII	16
10	4	JJJJJJ	22
11	4	KKKKK	24
12	4	LLLLLL	25

I want to get family name that haven't member under 18

Posted 21-Oct-15 4:46am

Ammar Al-hamdabni

Updated 21-Oct-15 4:52am

v4

Add a Solution

Comments

phil.o 21-Oct-15 10:50am

What have you tried?

Ammar Al-hamdabni 21-Oct-15 11:18am

I couldn't make query

phil.o 21-Oct-15 11:20am

You couldn't. Which means?

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Accepted Answer · 2015-10-21T05:05:00

Solution 1

Try:

SQL

SELECT m.id, m.name, f.name, m.Age
FROM Members m
JOIN Family f ON m.fid = f.id
WHERE m.Age > 17

ok I want to get the last three records in members table because they haven't members under 18

Your data doesn't do that: FID 2 has no members under 18.
But try this:

SQL

SELECT m.id, m.name, f.name, m.Age FROM Members m
JOIN (SELECT Fid FROM Members GROUP BY Fid HAVING MIN(AGE) > 17) j
ON j.Fid = m.FID
JOIN Family f ON m.Fid = f.Id

To get just the last three rows, you need to exclude 18 year olds as well:

SQL

SELECT m.id, m.name, f.name, m.Age FROM Members m
JOIN (SELECT Fid FROM Members GROUP BY Fid HAVING MIN(AGE) > 18) j
ON j.Fid = m.FID
JOIN Family f ON m.Fid = f.Id

Posted 21-Oct-15 5:05am

OriginalGriff

Updated 21-Oct-15 6:11am

v2

Comments

Ammar Al-hamdabni 21-Oct-15 11:13am

I get which they have > 17

Ammar Al-hamdabni 21-Oct-15 11:21am

I want to get family name that hasn't members under 18 I means there is no children

OriginalGriff 21-Oct-15 11:27am

Sorry?
I'm not sure what you mean here: Using the data in your question, exactly what results are you expecting to get?

Ammar Al-hamdabni 21-Oct-15 11:45am

ok I want to get the last three records in members table because they haven't members under 18

sorry for English

OriginalGriff 21-Oct-15 12:11pm

Answer updated

Ammar Al-hamdabni 21-Oct-15 13:21pm

Thank you OriginalGriff

OriginalGriff 21-Oct-15 13:59pm

You're welcome!

Richard Deeming · Accepted Answer · 2015-10-21T05:33:00

Solution 2

Try this:

SQL

SELECT
    F.id,
    F.name
FROM
    family As F
WHERE
    Not Exists
    (
        SELECT 1
        FROM Members As M
        WHERE M.fid = F.id
        And M.Age < 18
    )
;

Posted 21-Oct-15 5:33am

Richard Deeming

Comments

Ammar Al-hamdabni 21-Oct-15 12:02pm

Thank you very very much