CARI Infonet

 Forgot password?
 Register

ADVERTISEMENT

View: 2760|Reply: 7

PHP/Mysql -Kasi idea sikit camna nak buat ni

[Copy link]
Post time 20-5-2013 12:33 PM | Show all posts |Read mode
Aku dah google sejak semalam, dah test macam macam tapi tak menjadi..
Harap sesiapa boleh bagi idea:


Table 1:
system_id
system_vendor
system_name

Table 2:
user_id
user_name
user_department

Table 3:
memory_id
memory_slot
memory_capacity

Table kat atas ni contoh aja. Data contoh seperti berikut:

Table 1
system_id   
system_vendor
system_model
X1245  Dell Optiplex 790
X1222 Dell  
Optiplex 7010
X4444
HP Proliant

Table 2
user_id user_name user_department
X1245 Sadiq_segaraga IT
X1222 Abdul Wahub
Muzik
X4444 Abdul Wahid
Burung

Table 3
memory_id memory_slot
memory_capacity
X1245 1 1024
X12452 1024
X1222 1 1024
X4444 1 512
X4444 2 512
X4444 3 512
X4444 4 512

Untuk query table 1 dan 2 join tak de masalah. Masalah nak join query table 3 sekali tu..

Contoh: aku nak query user yang pakai jenis dan model computer yang ram dia 2048. Camna command dia?





Reply

Use magic Report


ADVERTISEMENT


Post time 20-5-2013 03:26 PM | Show all posts
cube pakai query nih :

select table2.*, table3.memory_slot, table3.memory_capacity , table1.system_vendor, table1.system_model
from table2
left outer join table3 on table2.user_id = table3.memory_id
left outer join table1 on table2.user_id = table1.system_id


result :



dari sql statement tu bleh la letak condition ape yg ko nak
"where memory_capacity = 2048"

btw, aku test sql ni kat dalam access ... sorry aku takde mysql.. but aku rase sql statement wise, more or less lebey kurang jer... yg penting ko kene join table pakai outer join ... Last edited by azradiza on 20-5-2013 03:29 PM

Reply

Use magic Report

 Author| Post time 20-5-2013 04:16 PM | Show all posts
azradiza posted on 20-5-2013 03:26 PM
cube pakai query nih :

select table2.*, table3.memory_slot, table3.memory_capacity , table1.syste ...

Terima kasih daun kelapa sawit..

Tapi kat mana yang nak bubuh SUM(memory.memory_capacity) tu? sebab sepatunya query tu resultnya camn

User Department Vendor Model memory
Sadiq_seagraga IT Dell Optiplex 790
2048
Abdul Whid
Burung HP Proliant 2048


Reply

Use magic Report

Post time 20-5-2013 04:24 PM | Show all posts
select table2.user_name,table2.user_department,  table1.system_vendor, table1.system_model,  sum ( table3.memory_capacity) as memory
from  table2
left outer join table3 on table2.user_id = table3.memory_id
left outer join table1 on table2.user_id = table1.system_id
group by
table2.user_name,table2.user_department,  table1.system_vendor, table1.system_model


*edit sket.. tadik missing 1 column Last edited by azradiza on 20-5-2013 04:28 PM

Reply

Use magic Report

 Author| Post time 20-5-2013 11:29 PM | Show all posts
yang WHERE memory='2048' nak letak mana?
Reply

Use magic Report

Post time 21-5-2013 03:38 PM | Show all posts
kena tanya dewa @kambinggila

tak suka database ni, sama hazab bahasa dia dengan regex
Reply

Use magic Report

Follow Us
Post time 21-5-2013 04:58 PM | Show all posts
ko try tambah kat and statement ko macam nih..


and b.user_id in (select memory_id from table3
                        group by memory_id
                        having sum(memory_capacity) = '2048')

aku belasah je ni. tak tengok pon betul betul hahahaha
Reply

Use magic Report

Post time 23-6-2013 11:20 PM | Show all posts
kau tak kan boleh buat sebab:

1. table 1 tak de relation dgn table 2. mcm mana nak tau user apa pakai pc mana?
2. table 1 MUNGKIN tak der relation dengan table 3, unless `Table 1`.system_id merujuk kepada `Table 3`.memory_id. sekali baca macam tak, sebab sistem id refer ke komputer, memory id refer ke RAM, tapi bila tengok rekod match la pulak value dia. kalau table 1 tak der relation dgn table 3, mcm mana nak tau PC apa pakai RAM mana?

bila dah ada relation, lepas join2, dia punya condition ialah `Table 3`.memory_slot * `Table 3`.memory_capacity = 2048

Reply

Use magic Report


ADVERTISEMENT


You have to log in before you can reply Login | Register

Points Rules

 

ADVERTISEMENT



 

ADVERTISEMENT


 


ADVERTISEMENT
Follow Us

ADVERTISEMENT


Mobile|Archiver|Mobile*default|About Us|CARI Infonet

29-3-2024 02:48 PM GMT+8 , Processed in 0.060882 second(s), 34 queries .

Powered by Discuz! X3.4

Copyright © 2001-2021, Tencent Cloud.

Quick Reply To Top Return to the list