5 Replies - 1118 Views - Last Post: 16 October 2009 - 11:41 AM

#1 kummu4help   User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 245
  • Joined: 05-August 08

need to retrieve data from 3 tables

Post icon  Posted 14 October 2009 - 09:07 PM

Hi,
i am using mysql 5.0.
i have 3 tables with following structrues..

Quote

ChatHistory
1.Id - varchar(10)
2.MsgType - tinyint(1)
3.MsgContent - longtext
4.UserType - tinyint(1)
5.UserID - varchar(10)
6.In_Timestamp - DateTime

Teacher
1.UserID - varchar(10)
2.UserName - varchar(255)
3.DealingSubject - varchar(255)

Student
1.UserID - varchar(10)
2.UserName - varchar(255)
3.SchoolName - varchar(255)
4.Class - varchar(255)

now i need to display chat history.. here i have to get the username from either teacher or student table basing on the usertype in chat history table.

i.e if usertype = 1 then i need to get username from teacher table
if usertype = 2 then i need to get username from student table

i had a querty like this
SELECT
		H.MsgType,
		H.MsgContent,
		H.UserType,
		U.UserName,
		H.UserID
FROM
		ChatHistory H
LEFT JOIN
		Teacher U ON H.UserID = U.UserID
WHERE
		In_Timestamp>'2009-9-10'



with the above query i can able to get details if UserID is available in Teacher table.. but how can i change the above querty so that it gets username based on usertype from either student or teacher table automatically.. i have to get the rows using query only.. i am not supposed to use stored procedures..

thanks for anyhelp..

This post has been edited by kummu4help: 14 October 2009 - 09:08 PM


Is This A Good Question/Topic? 0
  • +

Replies To: need to retrieve data from 3 tables

#2 kummu4help   User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 245
  • Joined: 05-August 08

Re: need to retrieve data from 3 tables

Posted 14 October 2009 - 10:20 PM

i even tried with the following query.. but it is also not working..
					SELECT						
						H.UserID,
						U.UserName,
						H.MsgContent,
						H.UserType,
						H.MsgType,
						H.In_Timestamp
					FROM
						ChatHistory H
					CASE H.UserType	
						WHEN 1 THEN(
							 LEFT JOIN 
							 Teacher U ON H.UserID=U.UserID
						)
						WHEN 2 THEN(
							 LEFT JOIN 
							 Student U ON H.UserID=U.UserID
						)
					END
					WHERE
						H.In_Timestamp > '2009-10-10' 



Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7507
  • View blog
  • Posts: 15,558
  • Joined: 16-October 07

Re: need to retrieve data from 3 tables

Posted 15 October 2009 - 05:05 AM

Something like:
SELECT
		H.MsgType,
		H.MsgContent,
		H.UserType,
		IsNull(T.UserName, S.UserName) as UserName
		H.UserID
FROM ChatHistory H
	LEFT OUTER JOIN Teacher T ON H.UserID = T.UserID
	LEFT OUTER JOIN Student S ON H.UserID = S.UserID
WHERE
		In_Timestamp>'2009-9-10'




You have messages in the system? There must be at least one originator and one recipient, yes?

A better design might be:
[b]User[/b]
1.UserID	  -	   int
2.UserName	  -	   varchar(255)

[b]Message[/b]
1.MessageId	  -	   int
2.MsgType	  -	   tinyint(1)
3.MsgContent	  -	   longtext
4.In_Timestamp	  -	   DateTime

[b]MessagePerson[/b]
1.UserID int
2.MessageId int
3.IsSender  tinyint(1) -- boolean (1 for true)
4.SchoolId int
5.ClassId int
6.IsTeacher  tinyint(1) -- boolean (1 for true)
7.DealingSubjectId int



SELECT M.MessageId, M.MsgType, M.MsgContent, MP.UserName
	FROM Message M
		INNER JOIN MessagePerson MP 
			ON M.UserID = MP.UserID
	WHERE M.In_Timestamp>'2009-9-10'



The above design assumes you'll have a few more tables. It also allows for you to have multiple recipients. And avoid duplication of messages.

Hope this helps.
Was This Post Helpful? 0
  • +
  • -

#4 kummu4help   User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 245
  • Joined: 05-August 08

Re: need to retrieve data from 3 tables

Posted 15 October 2009 - 06:42 PM

thanks baavgai..
i will try this out and if i had any problems then i will come again
thanks for reply
Was This Post Helpful? 0
  • +
  • -

#5 kummu4help   User is offline

  • D.I.C Head
  • member icon

Reputation: 4
  • View blog
  • Posts: 245
  • Joined: 05-August 08

Re: need to retrieve data from 3 tables

Posted 16 October 2009 - 10:26 AM

hey baavgai,
i would like to share my solution with you... with the help provided by you, i modified the query as follows and it solved my problem

Quote

SELECT
H.MsgType,
H.MsgContent,
H.UserType,
GREATEST(IFNULL(T.UserName,0),IFNULL( S.UserName,0)) as UserName,
H.UserID
FROM ChatHistory H
LEFT JOIN Teacher T ON H.UserID = T.UserID
LEFT JOIN Student S ON H.UserID = S.UserID
WHERE
In_Timestamp>'2009-9-10'


if you have time, can u tell me the diff between leftjoin and left outer join

but thanks again for the help
Was This Post Helpful? 0
  • +
  • -

#6 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7507
  • View blog
  • Posts: 15,558
  • Joined: 16-October 07

Re: need to retrieve data from 3 tables

Posted 16 October 2009 - 11:41 AM

View Postkummu4help, on 16 Oct, 2009 - 11:26 AM, said:

if you have time, can u tell me the diff between leftjoin and left outer join


Nothing at all. Same thing, slightly different syntax.

View Postkummu4help, on 16 Oct, 2009 - 11:26 AM, said:

GREATEST(IFNULL(T.UserName,0),IFNULL( S.UserName,0)) as UserName


You shouldn't need GREATEST. Assuming UserName is a string, returning a 0 might also be odd.

This should work, too:
IFNULL(T.UserName, IFNULL(S.UserName,'NA')) as UserName


Was This Post Helpful? 1

Page 1 of 1