Advanced SQL Query

[gadjodilo77]

Hi,

I have a difficult question. I am busy building some sort of registration application. With this application it is possible to register devices. But it has also to be possible to attach device to eachoter (for instance attach a computer to one or more monitors or attach a monitor to one or more computers). Herefore I have made a database with two tables; one to register the devices (they get a unique device_id which is primary key auto_increment) and another table to attach devices.

The attach table is made of 2 colomns:

monitor_id (which is a device_id of the devices table [of the monitor attached])
and the computer_id (which is a device_id of the devices table [of the computer attached])

For instance in the devices table there are a monitor with id 2 a monitor with id 1 and a computer with id 3.

I want to attach monitor 1 and 2 to computer 3

In the attach table it would look like this:
attach table
comp_id mon_id
3 1
3 2


devices table:
devices_id device_type device_name
1 comp pc_A
2 mon mon_ww
3 mon mon_wa

Now on a page I want to display every data I know of device 3 (the computer) So the devices_id of the computer from the device table is used in the url. Then I made a query to see the data for this device. However I also want to see which devices including there names and some other data stored for them (also) in the devices table are attached to it!

Is this possible? (First I have to get info for the device with id 3 (same as url Id) then I have to look into the table attach for the device with id 3 and see what device are attached, these are devices 1 and 2. How can I now use these ID's to show the device names (in the devices table) for both these devices on the same page?

Thank you in advance. Hopefully someone can help me and tell me if this is possible..

Gr, kabbi

 

[r937]

your sample data doesn't make sense, but i understand what you need

select c.device_name as comp_name
     , m.device_name as mon_name
  from devices as c
inner
  join attach
    on attach.comp_id
     = c.devices_id
inner
  join devices as m
    on m.devices_id = attach.mon_id   
 where c.devices_id = 1

r937.com | rudy.ca

 

[gadjodilo77]

Wow, Thank you! I'll try this code immediately

gr

 

[gadjodilo77]

One question I have one devices table...

devices table:
devices_id device_type device_name
1 comp pc_A
2 mon mon_ww
3 mon mon_wa


where do the c.device_name and m.device_name come from then?

... maybe a stupid question but is it possible to explain?

gr,

 

[r937]

they both come from the devices table, but from different "copies" of the table (actually, different rows)

notice that the devices table has been named twice in the query, with different table aliases

r937.com | rudy.ca

 

[gadjodilo77]

Super! I do not really understand how this query works... try to find out. It works very good however!

Again, thank you very much!

gr,

 

[gadjodilo77]

Hello,
I still have one more question. If I want to make an "update page" for the pc where it is possible to delete some of the devices(monitors) attached to the pc from the attach table (so the deleted monitors formely attached to the pc 1 in the attach table will still exist in the devices table). I have to show the the attached devices in a form or something and then mark with a checkbox behind the forms, which display the attached devices, if the device has to be deleted or not in the attach table from the computer 1 (So that that device will no longer be attached to the computer).

one problem is that there could be one monitor attached to a computer but there also could be unlimited monitors attached to that computer.

Is it possible to make the form dynamic in any way so that it just shows formfields with attached monitors (depending on the amount of attached monitors)
And that all the formfields will have a different nam (like monattached_1 till monattached_... for instance)

I would also like to have a dynamic query for the attached devices (per device) in the devices table so that I also can get more information about the attached devices and perhaps do an update for them in the devices table....


I'm not a very experienced user of coldfusion. Is this all possible in Coldfusion?

 

[gadjodilo77]

Hi,

I have another question about SQL query's... I believe this is a difficult one too.

I have a form with information about a certain device (computer pc_A with devices id 1... see above). With help of you in this page I can also show the devices which are attached to it (monitor mon_ww and mon_wa).

These devices are being showed in a form, what I want to do in this page is to delete the device out of the attach table but not out of the devices table. (I use a if/ifelse statement to select the right form)

In the form the device name is showed so I suppose I have to build a query that says in the end something like WHERE device_name = #FORM.device_name#

(However in the attach table there is no "device_name" field, only in the devices table there is. Otherways I would know how to do it..)

Does anyone know how to do this?

Thank you in advance

Gr.