Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

This may seem like an idiot response but can you clarify what you mean by structure so I don't send you the wrong thing?

D

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

the table schema, if you want you can send a backup if its small enough

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many
 

Here you go:

-- 

-- Database: `fsmgroup3`

-- 



-- --------------------------------------------------------



-- 

-- Table structure for table `access`

-- 



DROP TABLE IF EXISTS `access`;

CREATE TABLE `access` (

  `access_id` int(11) NOT NULL auto_increment,

  `access` varchar(10) NOT NULL default '',

  PRIMARY KEY  (`access_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;



-- --------------------------------------------------------



-- 

-- Table structure for table `airport`

-- 



DROP TABLE IF EXISTS `airport`;

CREATE TABLE `airport` (

  `airport_id` int(11) NOT NULL auto_increment,

  `airport_name` varchar(100) NOT NULL default '',

  `airport_code` char(3) NOT NULL default '',

  PRIMARY KEY  (`airport_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;



-- --------------------------------------------------------



-- 

-- Table structure for table `service`

-- 



DROP TABLE IF EXISTS `service`;

CREATE TABLE `service` (

  `service_id` int(11) NOT NULL auto_increment,

  `service` varchar(25) NOT NULL default '',

  PRIMARY KEY  (`service_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;



-- --------------------------------------------------------



-- 

-- Table structure for table `title`

-- 



DROP TABLE IF EXISTS `title`;

CREATE TABLE `title` (

  `title_id` int(11) NOT NULL auto_increment,

  `title` varchar(4) NOT NULL default '',

  PRIMARY KEY  (`title_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;



-- --------------------------------------------------------



-- 

-- Table structure for table `userairportservices`

-- 



DROP TABLE IF EXISTS `userairportservices`;

CREATE TABLE `userairportservices` (

  `userairportservices_id` int(11) NOT NULL auto_increment,

  `usr_id_users` int(11) NOT NULL default '0',

  `airport_id_airport` int(11) NOT NULL default '0',

  `service_id_service` int(11) NOT NULL default '0',

  PRIMARY KEY  (`userairportservices_id`),

  KEY `usr_id_users` (`usr_id_users`),

  KEY `airport_id_airport` (`airport_id_airport`),

  KEY `service_id_service` (`service_id_service`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=12 ;



-- --------------------------------------------------------



-- 

-- Table structure for table `users`

-- 



DROP TABLE IF EXISTS `users`;

CREATE TABLE `users` (

  `usr_id` int(11) NOT NULL auto_increment,

  `usr_access` int(11) NOT NULL default '0',

  `usr_title` int(11) default NULL,

  `usr_fname` varchar(75) NOT NULL default '',

  `usr_lname` varchar(75) NOT NULL default '',

  `usr_add1` varchar(75) NOT NULL default '',

  `usr_add2` varchar(75) default NULL,

  `usr_add3` varchar(75) default NULL,

  `usr_city` varchar(75) NOT NULL default '',

  `usr_prov_state` char(2) NOT NULL default '',

  `usr_pcode` varchar(10) NOT NULL default '',

  `usr_cntry` varchar(75) NOT NULL default '',

  `usr_acode` varchar(4) NOT NULL default '',

  `usr_phone` varchar(15) NOT NULL default '',

  `usr_email` varchar(75) NOT NULL default '',

  `usr_pass` varchar(12) NOT NULL default '',

  PRIMARY KEY  (`usr_id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

thanks, how soon do you need it?

cool if i get back with you in the morning?

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

Of course ... whenever you can. I appreciate it.

Dave

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

sure man, will work on it right after i get to work in the morning

...ahhhh, a fresh mind

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

this should do the trick, the only problem is if you ever add services, you will need to change the query to reflect that

select usr_id_users as USR_ID, airport_id_airport AS AIRPORT_ID,

a.airport_code AS AIRPORT_CODE,

max(if(service_id_service=1, 1, 0)) as JET,

max(if(service_id_service=2, 1, 0)) as GROUND,

max(if(service_id_service=3, 1, 0)) as GLYCOL

FROM userairportservices uas

INNER JOIN airport a

 on a.airport_id = uas.airport_id_airport

-- change this for different users

where usr_id_users = 1 

group by airport_id_airport;

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many
 

Thanks for this. I will not have a chance to get to it until Friday hopefully but will let you hnow how it goes. Can you explain this statement to me?

max(if(service_id_service=1, 1, 0)) as JET

I am not sure what the max statement means so a brief explanation to get me started would be appreciated.

Thanks a lot for all your time and help.

Dave

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many inse
 

basically there will be rows, that will be null (its set to 0 in the the if statement), because the rows are columns, if the service_id != 1 then the row will be null

therefore, the max will pull the highest, which will be 1 if the entry is there

think of it like for columns JET GROUND and GLYCOL

1 0 0
0 1 0
0 0 1

the max, allows us to group these three rows and select "1" if the service is enabled, although its in a different row

let me know if i need to explain it a little better

Re: Pulling related data from relational dB re: thread: Problems with a many-to-many
 

Hey ... just wanted to let you know that I got this working .. and now understand this quite a bit more, thanks to you. I really do appreciate your help.

Cheers

Dave