MYSQL complex query joins

i have students table with following information. Students

id,
registerno,
name

Internal marks table Student Internal Mark is information is added here because it may contain one or more entries

id,
student_id
subject_id

Internal marks entries table In this for internal mark totally 3 entries will be made (entry 1, entry 2 and entry 3)

id,
internalmarkentry_id
entryno_id
totalhours
attendedhours
maxmark
securemark

internal mark entryno entry1, entry 2 ...

id,
name,

Now i need to find out for internal marks are not available for the entry

I made a query like this

SELECT
	Student.registerno,
	Student.name,
	Subject.id,
	Subject.subject_code,
	Subject.subject_name,
	InternalMarkEntryNo.name,
	InternalMarkEntries.total_hours,
	InternalMarkEntries.attendent_hours,
	InternalMarkEntries.max_mark,
	InternalMarkEntries.secured_mark
FROM
	students AS Student
	JOIN internal_marks AS InternalMark ON InternalMark.student_id = Student.id
	JOIN internal_mark_entries AS InternalMarkEntries ON InternalMark.id <> InternalMarkEntries.internalmarks_id
	JOIN internal_mark_entry_nos as InternalMarkEntryNo on InternalMarkEntryNo.id = InternalMarkEntries.entry_id
	JOIN subjects AS Subject ON InternalMark.subject_id = Subject.id

Student Contain internal marks for n subjects, n subjects should have 3 entries and that entries comes from internalmark entry_nos table.

I need to find students, with subjects without any entry

tisuchi

3 years ago

Level 70

@vidhyaprakash85 Is there any particular reason not to use eloquent here?

vidhyaprakash85

3 years ago

Level 2

@tisuchi because i may have 10K records which will slow down so only.

tisuchi

3 years ago

Level 70

@vidhyaprakash85 10K record is not big amount of record to fetch via eloquent!

vidhyaprakash85

3 years ago

Level 2

@tisuchi many relations are there so only

tisuchi

3 years ago

Level 70

@vidhyaprakash85 Maybe try this:

SELECT
    Student.registerno,
    Student.name,
    Subject.id,
    Subject.subject_code,
    Subject.subject_name,
    InternalMarkEntryNo.name,
    InternalMarkEntries.total_hours,
    InternalMarkEntries.attendent_hours,
    InternalMarkEntries.max_mark,
    InternalMarkEntries.secured_mark
FROM
    students AS Student
    JOIN internal_marks AS InternalMark ON InternalMark.student_id = Student.id
    JOIN internal_mark_entries AS InternalMarkEntries ON InternalMark.id <> InternalMarkEntries.internalmarks_id
    JOIN internal_mark_entry_nos as InternalMarkEntryNo on InternalMarkEntryNo.id = InternalMarkEntries.entry_id
    JOIN subjects AS Subject ON InternalMark.subject_id = Subject.id
```

vidhyaprakash85

3 years ago

Level 2

@tisuchi https://ibb.co/dp4xLNr many duplicate records came

vidhyaprakash85

3 years ago

Level 2

@tisuchi

SELECT
	InternalMark.student_id,
	InternalMark.subject_id,
	Subject.subject_code,
	Subject.subject_name
FROM
	internal_marks AS InternalMark,
	subjects AS Subject
WHERE
	InternalMark.id NOT IN (
		SELECT
			internalmarks_id
		FROM
			internal_mark_entries
			WHERE internal_mark_entries.entry_id NOT IN (SELECT id from internal_mark_entry_nos)
	)
	AND InternalMark.subject_id = Subject.id

i got the answer but i need to know for which entry its not there how to find any idea sir ji

Please or to participate in this conversation.