SELECT on multiple tables help

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

SELECT on multiple tables help

Author

Topic

Magur
Starting Member

4 Posts

Posted - 2007-07-20 : 06:38:59

Hi all! I just registred (very nice site) and have problem with getting some data from multiple tables, I would like to get result in one result set and best would be in one sql query.

I have DB for miniMessenger proggy, what i try to do is retrieve list of contacts.

Table containing user account information.

CREATE TABLE `account` (
  `id_account` mediumint(8) unsigned NOT NULL auto_increment,
  `userdata_id` mediumint(8) unsigned NOT NULL default '0',
  `login` varchar(15) NOT NULL default '',
  `pwd` varchar(15) NOT NULL default '',
  `messenger_id` mediumint(8) unsigned NOT NULL default '0',
  `logged` tinyint(1) NOT NULL default '0',
  `ost_login` varchar(11) default NULL,
  PRIMARY KEY  (`id_account`),
  UNIQUE KEY `messenger_UN` (`messenger_id`),
  UNIQUE KEY `userdata_UN` (`userdata_id`)
)

INSERT INTO `account` VALUES (1, 1, 'User', 'fatimah', 4118394, 0, NULL);
INSERT INTO `account` VALUES (2, 2, 'Admin', 'haslo', 3333333, 0, NULL);

Contact list, first field is contact number (like 4356789 - MESSENGER id) next to this number is its contact number, auth - if contact was authorised, ban selfexplained :) I just take every row with number 4356789 and get contact numbers next to it.

CREATE TABLE `contacts` (
  `contact_id` mediumint(8) unsigned NOT NULL default '0',
  `contacts` mediumint(8) unsigned NOT NULL default '0',
  `auth` tinyint(1) unsigned NOT NULL default '0',
  `ban` tinyint(1) unsigned NOT NULL default '0',
  KEY `Contacts ID` (`contact_id`)
) 
INSERT INTO `contacts` VALUES (4118394, 3333333, 1, 0);
INSERT INTO `contacts` VALUES (4118394, 1234567, 0, 1);

Its table for messenger data, ID, status of contact (offline,online,ect), description, chat archiwum,

CREATE TABLE `messenger` (
  `id_messenger` mediumint(8) unsigned NOT NULL default '0',
  `status_id` tinyint(3) unsigned NOT NULL default '0',
  `description` varchar(255) NOT NULL default '',
  `archiwum` mediumtext NOT NULL,
  PRIMARY KEY  (`id_messenger`)
)
INSERT INTO `messenger` VALUES (1234567, 0, '', '');
INSERT INTO `messenger` VALUES (3333333, 1, '', '');
INSERT INTO `messenger` VALUES (4118394, 2, '', '');

Status is enumeration of status states(off,on,brb ect).

CREATE TABLE `status` (
  `id_status` tinyint(3) unsigned NOT NULL default '0',
  `stat` varchar(15) default NULL,
  PRIMARY KEY  (`id_status`)
)
INSERT INTO `status` VALUES (0, 'offline');
INSERT INTO `status` VALUES (1, 'Online');
INSERT INTO `status` VALUES (2, 'brb');

What i want to get is contact list + additional info of specific user by its messenger id. Like:
id_messenger,contacts,auth,ban,stat

which is userID, contact ID, authorisation, ban, status

My query looks like this:

SELECT id_messenger,contacts,auth,ban,status_id
FROM account,messenger,contacts
WHERE account.login = 'User'
AND messenger.id_messenger = account.messenger_id
AND contacts.contact_id = messenger.id_messenger

And it shows in stat only status of user of which i retrieve contact list. Please help me, im tired of working on this, im sure it is trivial :(

thx in advance!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-07-20 : 06:42:01

what is the expected result ?

KH
[spoiler]Time is always against us[/spoiler]

Magur
Starting Member

4 Posts

Posted - 2007-07-20 : 07:00:03

userID, contact ID, authorisation, ban, status
Like from submited data:

id_messenger contacts auth ban status_id 
4118394      3333333   1    0        1
4118394      1234567   0    1        0

And i get:

id_messenger contacts auth ban status_id 
4118394      3333333   1    0        2
4118394      1234567   0    1        2

From status table 0-offline, 1-online, 2-brb
brb is the status of user that retrieves contact list (for ID 4118394)

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-07-23 : 00:40:44

Select contact_id, id_messenger,auth,ban,stat
From contacts a join messenger b
on a.contacts = b.id_messenger
join status c
on b.status_id = c.id_status

--------------------------------------------------
S.Ahamed

Magur
Starting Member

4 Posts

Posted - 2007-07-25 : 16:01:23

Thank you very much pbguy!

I must read more about those joints, its wicked :)

Thanks!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-25 : 16:06:24

Not if you work with MS SQL Server.
Which DBMS do you use?

E 12°55'05.76"
N 56°04'39.42"

Magur
Starting Member

4 Posts

Posted - 2007-07-25 : 18:46:27

MySQL on EasyPHP. I posted it here, coz site rox and community, and Q I think was sql general. Above query wouldnt work on MS SQL Server? I thought its only implementation of DBMS by other company... Never looked more into MS SQL coz it has too many numbers before $ in price tag :P

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources