MySQL Query from more tables

[superaktieboy]

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

[Barand]

if just one of the tables does not have a record with a matching id then no rows will be returned

[superaktieboy]

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

[Barand]

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.

[fenway]

Also, use proper JOIN syntax, so that you can see your WHERE clause from your JOIN conditions.

[superaktieboy]

[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

[fenway]

[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.

[superaktieboy]

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

[Barand]

Try changing INNER JOIN to LEFT JOIN

[superaktieboy]

Well i tried that with LEFT JOIN, and i get arround 7000 results.. and all with the same name, from a table with arround 6 rows :S

[Barand]

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.

[superaktieboy]

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

[Barand]

Should be at least 1 thing different in each row

[superaktieboy]

Could but how am i gonna check that? there are to much results :S

[Barand]

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.

[superaktieboy]

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 ..

[Barand]

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

[fenway]

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.

[superaktieboy]

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?

[Barand]

Remove the comma between o.* and FROM

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

[superaktieboy]

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]

[Barand]

Did you add the "ON" clauses to specify the joins? If so, what does the query look like now?

[superaktieboy]

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]

[fenway]

FYI, the distinct is probably unnecessary here, since you're getting back UIDs, which will be unique a.k.a. distinct anyway.

[superaktieboy]

oh ok.. i did that