asked on

SQL Query to match two columns of one table with that of another Table

Table 1 : SLOTS
Columns : StdId, StudentName , Slot1, Slot2

Value examples for
Slot1 : SUN-01, SUN-02, THU-04 etc
Slot1 : FRI-01, WED-02, THU-03 etc

Table 2 : TIMINGS

Columns : ClassId, ClassDay, ClassTiming, ClassLocation
FRI-01, FRIDAY, 04:30 - 05:30

Value examples for
ClassId : FRI-01, SUN-02, WED-04 etc
ClassDay : FRIDAY, WEDNESDAY, SUNDAY etc
ClassTiming : 4:30 - 5:30, 09:00 - 10:00 etc,

Need to display records from the two tables in this manner
StdId , StdName, Details of Slot 1 (i.e FRIDAY 09:00 - 10:00), Details of Slot 2 (i.e SUNDAY 09:00 - 10:00)
example :
1 STUDENT NAME , FRIDAY 4:30 - 5:30, SUNDAY 10:00 - 09:00
2 STUDENT NAME2 , MONDAY 4:30 - 5:30, TUESDAY 4:30 - 5:30

So, the Slot1 and Slot2 from Table 1 should pick up its related records from Table 2 and display them

Using PHP, mySQL, PDO . ALL fields are VarChar

Please Guide

bcnagel

Hello.

How about trying something like the suggestion below?

CREATE TEMPORARY TABLE Slots ENGINE=MEMORY 
as (
	select *
		from (
			select '1234' as StdId, 'Bill' as StudentName, 'FRI-01' as Slot1, 'SUN-01' as Slot2
			union
			select '1234' as StdId, 'Bill' as StudentName, 'MON-01' as Slot1, 'WED-01' as Slot2
			union
			select '2345' as StdId, 'Nancy' as StudentName, 'FRI-01' as Slot1, 'SUN-01' as Slot2
			union
			select '2345' as StdId, 'Nancy' as StudentName, 'MON-01' as Slot1, 'WED-01' as Slot2
			) as der
	)


CREATE TEMPORARY TABLE Timings ENGINE=MEMORY 
as (
	select *
		from (
		select 'MON-01' as ClassId, 'Monday' as ClassDay, '09:00 - 10:00' as ClassTiming, 'Room 01' as ClassLocation
		union 
		select 'WED-01' as ClassId, 'Wednesday' as ClassDay, '09:00 - 10:00' as ClassTiming, 'Room 03' as ClassLocation
		union
		select 'FRI-01' as ClassId, 'Friday' as ClassDay, '09:00 - 10:00' as ClassTiming, 'Room 20' as ClassLocation
		union 
		select 'SUN-01' as ClassId, 'Sunday' as ClassDay, '09:00 - 10:00' as ClassTiming, 'Room 11' as ClassLocation
		) der
	)


select s.StdId, s.StudentName, t1.ClassDay + ' ' + t1.ClassTiming as Slot1Details, t2.ClassDay + ' ' + t2.ClassTiming as Slot2Details
	from Slots as s
	left join Timings as t1
	on t1.ClassId = s.Slot1
	left join Timings as t2
	on t2.ClassId = s.Slot2

Open in new window

Vitor Montalvão

braindrops, is this issue solved?

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.