Forum › Internet Service & Networking › PHP/Mysql -Kasi idea sikit camna nak buat ni

sadiq_segaraga Publish time 20-5-2013 12:33 PM

PHP/Mysql -Kasi idea sikit camna nak buat ni

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
X1245Dell 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?

azradiza Publish time 20-5-2013 03:26 PM

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 :

http://img842.imageshack.us/img842/6582/testsql.jpg

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

sadiq_segaraga Publish time 20-5-2013 04:16 PM

azradiza posted on 20-5-2013 03:26 PM static/image/common/back.gif
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

azradiza Publish time 20-5-2013 04:24 PM

select table2.user_name,table2.user_department,table1.system_vendor, table1.system_model,sum ( table3.memory_capacity) as memory
fromtable2
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

sadiq_segaraga Publish time 20-5-2013 11:29 PM

yang WHERE memory='2048' nak letak mana?

perang_saudara Publish time 21-5-2013 03:38 PM

kena tanya dewa @kambinggila

tak suka database ni, sama hazab bahasa dia dengan regex

kambinggila Publish time 21-5-2013 04:58 PM

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

bobok Publish time 23-6-2013 11:20 PM

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

View full version: PHP/Mysql -Kasi idea sikit camna nak buat ni