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

braindrops
braindrops used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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ãoIT Engineer
Distinguished Expert 2017

Commented:
braindrops, is this issue solved?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial