Jump to content

Archived

This topic is now archived and is closed to further replies.

superaktieboy

MySQL Query from more tables

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

Share this post


Link to post
Share on other sites
if just one of the tables does not have a record with a matching id then no rows will be returned

Share this post


Link to post
Share on other sites
ooh thx .. so can anyone help me with selecting from these tables for a members list page?
i need it in one query coz i want the user to be able to choose what to select and what not and how to order.

greetzz

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
Also, use proper JOIN syntax, so that you can see your WHERE clause from your JOIN conditions.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
oh ok.. it still doesn't work ??? anyway isn't there anyway that this allows this to return without even having any results, coz the others tables could be empty

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
ok.. so how can i then select only from friends, members and online.. again those tables could be empty (maybe not online but friends could be)
then im making extra rows in members for these things ..

Share this post


Link to post
Share on other sites
Just include those tables you need for the query.

if you want only members with friend(s) use

members INNER JOIN friends

if you want all members with friends but also want to show those with 0 friends, use

members LEFT JOIN friends

Share this post


Link to post
Share on other sites
Actually, if you're JOINing, and your JOIN conditions are correct, you should never get any "extra" rows at all -- it will just add more fields to each record returned.

Share this post


Link to post
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?

Share this post


Link to post
Share on other sites
Remove the comma between o.* and FROM

Also you haven't specified the join conditions to say which columns should match

Share this post


Link to post
Share on other sites
oh thx didn't see that one  :P
but now i get another error:
[code]
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 'WHERE m.id > 0' at line 8
[/code]

Share this post


Link to post
Share on other sites
Did you add the "ON" clauses to specify the joins? If so, what does the query look like now?

Share this post


Link to post
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]

Share this post


Link to post
Share on other sites
FYI, the distinct is probably unnecessary here, since you're getting back UIDs, which will be unique a.k.a. distinct anyway.

Share this post


Link to post
Share on other sites

×

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.