Friday, February 24, 2006

Playing with SQL is fun :)

A sample of 3 tables join query with self generated row_id

select(select count(*) from dbase_name.table1 join
(dbase_name.table2 join dbase_name.table3 on table2.id = table3.table2_id)
on table2.tbl2field = table1.id where table1." +field+ " like '%" + text_content + "%')
- count(*) + 1 as row_id,
t1.*, t2.* from (select * from dbase_name.table1 join
(dbase_name.table2 join dbase_name.table3 on table2.id = table3.table2_id)
on table2.tbl2field = table1.id where
table1." +field+ " like '%" + text_content + "%') as t1,
(select * from dbase_name.table1 join
(dbase_name.table2 join dbase_name.table3 on table2.id = table3.table2_id)
on table2.tbl2field = table1.id where
table1." +field+ " like '%" + text_content + "%') as t2
where t1.table2_id <= t2.table2_id group by t1.table2_id order by 1 ";

14 comments:

Prometeuz said...

Ayo, headache la after reading your blog....where's my Panadol.

5xmom ~chanlilian.net~ said...

Alicia, U dun mind me quoting your comment in my latest post, I hope? About why women get the limelight and the men get away with them.

Alicia said...

prometeuz : haha go buy

5Xmom : go ahead ;)

KY said...

geek!

Alicia said...

ky : me tadak geek la -.- if me geek then u're a geek too xD

gbyeow said...

Don't take DBMS for granted and rely too much on SQL. If you use the wrong combination, you're going to regret it due to poor performance. I've seen servers lock up (for hours and days) because of bad SQL.

n305er said...

Nerd!!! :P

Chen said...

wah.. this is fun ah?
Made me pening habis :D
Cutting people is more fun than these :D

Dragon City said...

wow.. i really can't see where was the fun.. ^_^

Alicia said...

gbyeow : shuldn't b that bad if it's just some medium sized table join

n305er : -.- i'm not a nerd :(

chen & dragon city : haha.. not fun at all la.. takes me hours to find that ghey solution..

DarReNz said...

alicia you are happening chef nerd lol ...

Jacky said...

Now I know who to ask when I hit into SQL problems :)

FH2O said...

... post some assembly codes and I'll be duly impressed! :)

Alicia said...

darrenz : -.- i'm not a nerd le..

jacky : :/ i'm not a pro.. haha.. i'm still learning .. xD

fh2o : i've books on assembly codes.. buden i didn't really look into it.. hehe