Jump to content

MySQL Query from more tables


superaktieboy

Recommended Posts

hi
i got this MySQL query
but i don't get results back, while i am sure there are results

[code]SELECT
   m.*,
   c.id,
   dd.id,
   dr.id,
   fr.id,
   nr.id,
   p.id,
   ss.id,
   sr.id,
   tt.id,
   tr.id,
   f.*,
   o.*,

   c.userid,
   dd.dl_userid,
   dr.userid,
   fr.reply_poster,
   nr.news_rp_userid,
   p.userid,
   ss.userid,
   sr.userid,
   tt.userid,
   tr.userid
FROM
   members AS m,
   calender AS c,
   dl_downloads AS dd,
   dl_reacties AS dr,
   forum_replys AS fr,
   news_replies AS nr,
   online AS o,
   pictures AS p,
   scripts AS ss,
   sc_reacties AS sr,
   tutorials AS tt,
   tut_reacties AS tr,
   friends AS f
WHERE
   m.id > 0
AND
   c.userid = m.id
AND
   dd.dl_userid = m.id
AND
   dr.userid = m.id
AND
   fr.reply_poster = m.id
AND
   nr.news_rp_userid = m.id
AND
   p.userid = m.id
AND
   ss.userid = m.id
AND
   sr.userid = m.id
AND
   tt.userid = m.id
AND
   tr.userid = m.id
AND
   f.userid = m.id
AND
   o.userid = m.id [/code]

these are my tables
[code]
--
-- Table structure for table `calender`
--

CREATE TABLE `calender` (
 `id` int(11) NOT NULL auto_increment,
 `userid` int(11) NOT NULL,
 `date` varchar(10) collate latin1_general_ci NOT NULL default '',
 `subject` varchar(255) collate latin1_general_ci NOT NULL,
 `description` text collate latin1_general_ci NOT NULL,
 `day` int(2) NOT NULL,
 `month` int(2) NOT NULL,
 `year` int(4) NOT NULL,
 UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci PACK_KEYS=0 AUTO_INCREMENT=7 ;

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

--
-- Table structure for table `dl_downloads`
--

CREATE TABLE `dl_downloads` (
 `id` int(11) NOT NULL auto_increment,
 `dl_dloads` int(11) NOT NULL,
 `dl_subcat_id` int(11) NOT NULL,
 `dl_userid` int(11) NOT NULL,
 `dl_naam` varchar(255) collate latin1_general_ci NOT NULL,
 `dl_uitleg` text collate latin1_general_ci NOT NULL,
 `dl_url` varchar(255) collate latin1_general_ci NOT NULL,
 `dl_datum` int(10) NOT NULL,
 `dl_status` int(1) NOT NULL,
 `dl_views` int(11) NOT NULL,
 `dl_short_uitleg` varchar(255) collate latin1_general_ci NOT NULL,
 `dl_screenshot` varchar(255) collate latin1_general_ci NOT NULL,
 `dl_download` varchar(255) collate latin1_general_ci NOT NULL,
 `dl_autheur` varchar(255) collate latin1_general_ci NOT NULL default '',
 `dl_version` varchar(50) collate latin1_general_ci NOT NULL,
 `file_size` int(255) NOT NULL,
 `dl_last_download` int(10) NOT NULL,
 `last_post_date` int(10) NOT NULL,
 `dl_icon` int(2) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=88 ;

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

--
-- Table structure for table `dl_reacties`
--

CREATE TABLE `dl_reacties` (
 `id` int(11) NOT NULL auto_increment,
 `userid` int(11) NOT NULL default '0',
 `dl_id` int(11) NOT NULL default '0',
 `bericht` text collate latin1_general_ci NOT NULL,
 `datum` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;

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

--
-- Table structure for table `forum_replys`
--

CREATE TABLE `forum_replys` (
 `id` int(11) NOT NULL auto_increment,
 `topic_id` int(11) NOT NULL,
 `is_topic` int(1) NOT NULL,
 `reply_poster` int(11) NOT NULL,
 `reply_date` int(10) NOT NULL,
 `reply_message` text collate latin1_general_ci NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=27 ;

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

--
-- Table structure for table `forum_topics`
--

CREATE TABLE `forum_topics` (
 `id` int(11) NOT NULL auto_increment,
 `forum_id` int(11) NOT NULL,
 `topic_poster` int(11) NOT NULL,
 `topic_type` int(1) NOT NULL,
 `topic_naam` varchar(40) collate latin1_general_ci NOT NULL,
 `topic_date` int(10) NOT NULL,
 `topic_locked` int(11) NOT NULL default '0',
 `topic_views` int(11) NOT NULL,
 `last_post_date` int(10) NOT NULL,
 `topic_desc` varchar(255) collate latin1_general_ci NOT NULL,
 `poll_added` int(1) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=13 ;

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

--
-- Table structure for table `friends`
--

CREATE TABLE `friends` (
 `userid` int(11) NOT NULL,
 `userid2` int(11) NOT NULL,
 `type` int(1) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

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

--
-- Table structure for table `members`
--

CREATE TABLE `members` (
 `id` int(11) NOT NULL auto_increment,
 `gebruikersnaam` varchar(25) collate latin1_general_ci NOT NULL,
 `wachtwoord` varchar(32) collate latin1_general_ci NOT NULL,
 `email` varchar(255) collate latin1_general_ci NOT NULL,
 `realname` varchar(255) collate latin1_general_ci NOT NULL,
 `gender` int(1) NOT NULL,
 `birthday` int(10) NOT NULL,
 `activated` varchar(20) collate latin1_general_ci NOT NULL,
 `website` varchar(255) collate latin1_general_ci NOT NULL,
 `status` varchar(100) collate latin1_general_ci NOT NULL default 'Lid',
 `avatar` varchar(255) collate latin1_general_ci NOT NULL,
 `liddatum` int(10) NOT NULL,
 `ondertitel` text collate latin1_general_ci NOT NULL,
 `ip` varchar(50) collate latin1_general_ci NOT NULL,
 `proxy` varchar(50) collate latin1_general_ci NOT NULL,
 `host` varchar(50) collate latin1_general_ci NOT NULL,
 `warnings` int(1) NOT NULL default '0',
 `posts` int(11) NOT NULL default '0',
 `tuts` int(11) NOT NULL default '0',
 `dloads` int(11) NOT NULL default '0',
 `scripts` int(11) NOT NULL default '0',
 `lastvisit` int(11) NOT NULL,
 `locatie` varchar(255) collate latin1_general_ci NOT NULL,
 `occupation` varchar(255) collate latin1_general_ci NOT NULL,
 `icq` varchar(15) collate latin1_general_ci NOT NULL,
 `aim` varchar(255) collate latin1_general_ci NOT NULL,
 `yim` varchar(255) collate latin1_general_ci NOT NULL,
 `msnm` varchar(255) collate latin1_general_ci NOT NULL,
 `interests` varchar(255) collate latin1_general_ci NOT NULL,
 `tel_home` int(20) NOT NULL,
 `fax_home` int(20) NOT NULL,
 `tel_occ` int(20) NOT NULL,
 `fax_occ` int(20) NOT NULL,
 `mobiel` int(20) NOT NULL,
 `user_scripts_per_page` int(11) NOT NULL,
 `user_language` varchar(40) collate latin1_general_ci NOT NULL default 'dutch',
 `user_users_per_page` int(11) NOT NULL,
 `user_view_online` int(1) NOT NULL,
 `user_view_profile` int(1) NOT NULL,
 `user_newsletter` int(1) NOT NULL,
 `user_view_email` int(1) NOT NULL,
 `user_replys_per_page` int(11) NOT NULL,
 `user_notify_forum` int(1) NOT NULL,
 `user_notify_dloads` int(1) NOT NULL,
 `user_notify_scripts` int(1) NOT NULL,
 `user_notify_tutorials` int(1) NOT NULL,
 `user_notify_privmsg` int(1) NOT NULL,
 `user_popup_by_privmsg` int(1) NOT NULL,
 `user_allow_pm` int(1) NOT NULL,
 `user_mail_pm` int(1) NOT NULL,
 `user_view_signature` int(1) NOT NULL,
 `user_view_avatar` int(1) NOT NULL,
 `optie_view_web` int(1) NOT NULL,
 `optie_view_msg` int(1) NOT NULL,
 `optie_view_personal` int(1) NOT NULL,
 `user_view_friends` int(1) NOT NULL,
 `user_view_blocked` int(1) NOT NULL,
 `user_view_friend_of` int(1) NOT NULL,
 `user_view_blocked_by` int(1) NOT NULL,
 `user_privmsg_p_page` int(5) NOT NULL,
 `user_view_quick_reply` int(1) NOT NULL,
 PRIMARY KEY  (`id`),
 FULLTEXT KEY `gebruikersnaam` (`gebruikersnaam`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=12 ;

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

--
-- Table structure for table `news_replies`
--

CREATE TABLE `news_replies` (
 `id` int(10) NOT NULL auto_increment,
 `news_rp_id` int(10) NOT NULL default '0',
 `news_rp_userid` varchar(20) collate latin1_general_ci NOT NULL default '',
 `news_rp_bericht` text collate latin1_general_ci NOT NULL,
 `news_rp_datum` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

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

--
-- Table structure for table `online`
--

CREATE TABLE `online` (
 `id` int(11) NOT NULL auto_increment,
 `userid` int(11) NOT NULL default '0',
 `ip` varchar(15) collate latin1_general_ci NOT NULL default '',
 `datum` int(10) NOT NULL,
 `datum_start` int(10) NOT NULL,
 `page` varchar(255) collate latin1_general_ci NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=966 ;

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

--
-- Table structure for table `pictures`
--

CREATE TABLE `pictures` (
 `id` int(11) NOT NULL auto_increment,
 `sid` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `subject` varchar(255) collate latin1_general_ci NOT NULL,
 `description` text collate latin1_general_ci NOT NULL,
 `location` varchar(1000) collate latin1_general_ci NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `sc_reacties`
--

CREATE TABLE `sc_reacties` (
 `id` int(11) NOT NULL auto_increment,
 `userid` int(11) NOT NULL default '0',
 `script_id` int(11) NOT NULL default '0',
 `bericht` text collate latin1_general_ci NOT NULL,
 `datum` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=1 ;

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

--
-- Table structure for table `scripts`
--

CREATE TABLE `scripts` (
 `id` int(11) NOT NULL auto_increment,
 `subcat_id` int(11) NOT NULL,
 `title` varchar(255) collate latin1_general_ci NOT NULL,
 `explain` text collate latin1_general_ci NOT NULL,
 `views` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `desc` varchar(300) collate latin1_general_ci NOT NULL,
 `source` text collate latin1_general_ci NOT NULL,
 `demo_url` varchar(255) collate latin1_general_ci NOT NULL,
 `date` int(10) NOT NULL,
 `version` varchar(10) collate latin1_general_ci NOT NULL,
 `last_post_date` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

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

--
-- Table structure for table `tut_reacties`
--

CREATE TABLE `tut_reacties` (
 `id` int(11) NOT NULL auto_increment,
 `userid` int(11) NOT NULL,
 `tut_id` int(11) NOT NULL,
 `bericht` text collate latin1_general_ci NOT NULL,
 `datum` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=3 ;

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

--
-- Table structure for table `tutorials`
--

CREATE TABLE `tutorials` (
 `id` int(11) NOT NULL auto_increment,
 `subcat_id` int(11) NOT NULL,
 `userid` int(11) NOT NULL,
 `title` varchar(255) collate latin1_general_ci NOT NULL,
 `inleiding` varchar(300) collate latin1_general_ci NOT NULL,
 `text` text collate latin1_general_ci NOT NULL,
 `views` int(11) NOT NULL,
 `datum` int(10) NOT NULL,
 `last_post_date` int(10) NOT NULL,
 PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=2 ;
[/code]

what is the problem with the query?

greetzz
Link to comment
Share on other sites

The only tables you are getting data from are
members
friends
online

All the other selected fields in your query are the id fields, and as you are matching on these they will all have the same value as member.id. So not much point in putting them in the select.

So the question are -
Why are the other tables in the query?
Is it so that you only return records in member that have a match in those tables?
Do you  want members listed with no friends records?
Do you want members listed with no online records?

So yes, I can help with the query but only if I know what the query should do.
Link to comment
Share on other sites

[b]Why are the other tables in the query?[/b]
because (as you probably knew) i use it for a memberslist, there a user can say how they want to order, and what they want to select.. i want them to select like on total online, total new replies etc.
[b]Is it so that you only return records in member that have a match in those tables?[/b]
well i got the users at (there are 4 users to test) and not all of them have posted, so its not only those who have posted, but every user.
[b]Do you  want members listed with no friends records?[/b]
i want the user to be able to select that himself with a form
[b]Do you want members listed with no online records?[/b]
same as above

@fenway
srry im not really good in MySQL so if you can explain what you mean, i'd appreciate it

btw i forgot to say even when i try only "WHERE m.id > 0" it still doesn't give records.

greetzz
Link to comment
Share on other sites

[quote author=superaktieboy link=topic=112860.msg459129#msg459129 date=1162053526]
@fenway
srry im not really good in MySQL so if you can explain what you mean, i'd appreciate it
[/quote]

I mean like this:
[code]
SELECT
    m.*,
    c.id,
    dd.id,
    dr.id,
    fr.id,
    nr.id,
    p.id,
    ss.id,
    sr.id,
    tt.id,
    tr.id,
    f.*,
    o.*,

    c.userid,
    dd.dl_userid,
    dr.userid,
    fr.reply_poster,
    nr.news_rp_userid,
    p.userid,
    ss.userid,
    sr.userid,
    tt.userid,
    tr.userid
FROM members AS m
    INNER JOIN calender AS c ON c.userid = m.id
    INNER JOIN dl_downloads AS dd ON dd.dl_userid = m.id
    INNER JOIN dl_reacties AS dr ON dr.userid = m.id
    INNER JOIN forum_replys AS fr ON fr.reply_poster = m.id
    INNER JOIN news_replies AS nr ON    nr.news_rp_userid = m.id
    INNER JOIN online AS o ON    o.userid = m.id
    INNER JOIN pictures AS p ON    p.userid = m.id
    INNER JOIN scripts AS ss ON    ss.userid = m.id
    INNER JOIN sc_reacties AS sr ON    sr.userid = m.id
    INNER JOIN tutorials AS tt ON    tt.userid = m.id
    INNER JOIN tut_reacties AS tr ON    tr.userid = m.id
    INNER JOIN friends AS f ON    f.userid = m.id
WHERE
    m.id > 0
[/code]

But as barand mentioned, if anything doesn't match, you'll get back nothing.
Link to comment
Share on other sites

That's a thing abour joins

Table1 has 2 records and you have 3 matching records in table2, so the cols from each of the 2 records are joined to the cols of each of the matching 3 from Table2 giving 6 rows..

Now join to Table3 with 5 matching rows and you now have 30 rows.
Now join to Table4 with 10 matching rows and you now have 300 rows.

etc etc etc

Try putting DISTINCT immediately after SELECT so only unique combinations of field value are returned.
Link to comment
Share on other sites

so you mean like this:
[code]
SELECT DISTINCT
    m.*,
    c.id,
    dd.id,
    dr.id,
    fr.id,
    nr.id,
    p.id,
    ss.id,
    sr.id,
    tt.id,
    tr.id,
    f.*,
    o.*,

    c.userid,
    dd.dl_userid,
    dr.userid,
    fr.reply_poster,
    nr.news_rp_userid,
    p.userid,
    ss.userid,
    sr.userid,
    tt.userid,
    tr.userid
FROM members AS m
    LEFT JOIN calender AS c ON c.userid = m.id
    LEFT JOIN dl_downloads AS dd ON dd.dl_userid = m.id
    LEFT JOIN dl_reacties AS dr ON dr.userid = m.id
    LEFT JOIN forum_replys AS fr ON fr.reply_poster = m.id
    LEFT JOIN news_replies AS nr ON    nr.news_rp_userid = m.id
    LEFT JOIN online AS o ON    o.userid = m.id
    LEFT JOIN pictures AS p ON    p.userid = m.id
    LEFT JOIN scripts AS ss ON    ss.userid = m.id
    LEFT JOIN sc_reacties AS sr ON    sr.userid = m.id
    LEFT JOIN tutorials AS tt ON    tt.userid = m.id
    LEFT JOIN tut_reacties AS tr ON    tr.userid = m.id
    LEFT JOIN friends AS f ON    f.userid = m.id
WHERE
    m.id > 0
[/code]

i still get lot of the same rows
Link to comment
Share on other sites

Now you've seen for yourself the results of joining all the tables, you might want to consider a different approach.

For example, to get the number of replies you only need query the forum_replys table. If you include all the others you are going to get counts like 7000+ every time.

If you want to count those online, use online table (maybe joined with member table if want their names)

Just because you can join them all doesn't mean that you should.
Link to comment
Share on other sites

ok i tried this one:
[code]SELECT DISTINCT
    m.*,
    f.*,
    o.*,
FROM members AS m
    LEFT JOIN online AS o
    LEFT JOIN friends AS f
WHERE
    m.id > 0[/code]

but i get this error from MySQL

[quote]
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FROM members AS m LEFT JOIN online AS o LEFT JOIN friends AS f WHERE ' at line 5
[/quote]

what does it mean?
Link to comment
Share on other sites

Thx .. it worked, it was without the ON bit .. i added it and it worked :) thx
here is the query for other people
[code]
SELECT DISTINCT
    m.*,
    f.*,
    o.*
FROM members AS m
    LEFT JOIN online AS o ON o.userid = m.id
    LEFT JOIN friends AS f ON f.userid = m.id
WHERE
    m.id > 0[/code]
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.