query to get results from 4 tables

[icanmakeiteasy]

i am bit confused about my question only please help me to get the result i hav 3 tables table1,table2,table3

table1
uid shiftcode startdate enddate
100 s1 xxxxxx xxxxx
101 s1 xxxxxx xxxxx
102 s1 xxxxxx xxxxx

table2
uid patterncode
103 p1

table3
uid shiftcode patterncode
103 s2 p1

i want shiftcode's of 100 to 103 users how to get plzz help me....

icanmakeiteasy

[Wendelius]

I think your question is lacking some information. Could you explain the logic of combining those rows together. Is it for example that when table1 has shiftcode s1 you want shiftcode s2 from table3 or is the idea to go through table2 and join patterncode, but then again how does pattern code relate to shiftcode in table1 etc.

The need to optimize rises from a bad design.My articles[^]

[venu656]

it is very simple use joins and co related sub queries

[icanmakeiteasy]

thx for reply... user 103 is on bench so his shift code is s2 or any other if user got work for some days simply he moves to table1 with his working days and his work code(s1,s2,s3..etc) just i want work code(shiftcode) of 100 to 103 users between given dates

icanmakeiteasy

modified on Tuesday, February 17, 2009 8:18 AM

[icanmakeiteasy]

i am little poor in joins and sub queries can u plzz give me some idea

icanmakeiteasy

[Wendelius]

So did I understand correctly, table1 contains info about users andtheir work periods and you want to find users having work in certain period. If that's the case, could it be something like

select ...
from table1
where startdate between periodstart and periodend
or enddate between periodstart and periodend

where the periodstart and periodend are the dates you're interested in

The need to optimize rises from a bad design.My articles[^]

[icanmakeiteasy]

thx for reply...

srry... i am mistaken
table3 does not have uid column
table3
shiftcode patterncode
s2 p1

i want the shift code of users (100 to 103) between given periodic times by using given query i can not get the shift code of user 103 where does not exist in the table1, so i have to get the shift code from table3, here table3 having patterncode only, now i have to get the paaterncode from table2... using that i want to show the shiftcode of user 103.... am i clear now??

icanmakeiteasy

[icanmakeiteasy]

i got solved this problem [^] from this forum

icanmakeiteasy