How to write correct sql with left join on some tables?

Question

Good day.

STRUCTURE TABLES AND ERROR WHEN EXECUTE QUERY ON SQLFIDDLE

I have some sql queries:

First query:

SELECT 
            n.Type AS Type,
            n.UserIdn AS UserIdn,
            u.Username AS Username,
            n.NewsIdn AS NewsIdn,
            n.Header AS Header,
            n.Text AS Text,
            n.Tags AS Tags,
            n.ImageLink AS ImageLink,
            n.VideoLink AS VideoLink,
            n.DateCreate AS DateCreate
        FROM News n 
        LEFT JOIN Users u ON n.UserIdn = u.UserIdn 

SECOND QUERY:

SELECT 
    IFNULL(SUM(Type = 'up'),0) AS Uplikes, 
    IFNULL(SUM(Type = 'down'),0) AS Downlikes,
    (IFNULL(SUM(Type = 'up'),0) - IFNULL(SUM(Type = 'down'),0)) AS SumLikes
FROM JOIN Likes
WHERE NewsIdn=NewsIdn  //only for example- in main sql NewsIdn = value NewsIdn from row table News
ORDER BY UpLikes DESC

AND TREE QUERY

SELECT 
count(*) as Favorit
Form Favorites
WHERE NewsIdn=NewsIdn  //only for example- in main sql NewsIdn = value NewsIdn from row table News

I would like to combine both queries, display all rows from the table News, as well as the number of Uplikes, DownLikes and number of Favorit for each value NewsIdn from the table of News (i.e. number of Uplikes, DownLikes and number of Favorit for each row of News) and make order by Uplikes Desc.

Tell me please how to make it?

P.S.: in result i would like next values

TYPE  USERIDN USERNAME NEWSIDN HEADER TEXT                      TAGS     IMAGELINK       VIDEOLINK DATECREATE                      UPLIKES DOWNLIKES SUMLIKES FAVORIT
image 346412  test     260806  test                                      1388152519.jpg            December, 27 2013 08:55:27+0000 2       0          2       2
image 108546  test2    905554  test2  1231231111111111111111111 123. 123 1388153493.jpg            December, 27 2013 09:11:41+0000 1       0          1       0
text  108546  test2    270085  test3                            123 .123                           December, 27 2013 09:13:30+0000 1       0          1       0
image 108546  test2    764955  test4                                     1388192300.jpg            December. 27 2013 19:58:22+0000 0       1         -1       0

Answer 1

First, your table structures with all the "Idn" of varchar(30). It appears those would actually be ID keys to the other tables and should be integers for better indexing and joining performance.

Second, this type of process, especially web-based is a perfect example of DENORMALIZING the values for likes, dislikes, and favorites by actually having those columns as counters directly on the record (ex: News table). When a person likes, dislikes or makes as a favorite, stamp it right away and be done with it. If a first time through you do a bulk sql-update do so, but also have triggers on the table to automatically handle updating the counts appropriately. This way, you just query the table directly and order by that which you need and you are not required to query all likes +/- records joined to all news and see which is best. Having an index on the news table will be your best bet.

Now, that said, and with your existing table constructs, you can do via pre-aggregate queries and joining them as aliases in the sql FROM clause... something like

SELECT
      N.Type,
      N.UserIdn,
      U.UserName,
      N.NewsIdn,
      N.Header,
      N.Text,
      N.Tags,
      N.ImageLink,
      N.VideoLink,
      N.DateCreate,
      COALESCE( SumL.UpLikes, 0 ) as Uplikes,
      COALESCE( SumL.DownLikes, 0 ) as DownLikes,
      COALESCE( SumL.NetLikes, 0 ) as NetLikes,
      COALESCE( Fav.FavCount, 0 ) as FavCount
   from
      News N
         JOIN Users U
            ON N.UserIdn = U.UserIdn
         LEFT JOIN ( select 
                           L.NewsIdn,
                           SUM( L.Type = 'up' ) as UpLikes,
                           SUM( L.Type = 'down' ) as DownLikes,
                           SUM( ( L.Type = 'up' ) - ( L.Type = 'down' )) as NetLikes
                        from
                           Likes L
                        group by
                           L.NewsIdn ) SumL
            ON N.NewsIdn = SumL.NewsIdn
         LEFT JOIN ( select 
                           F.NewsIdn,
                           COUNT(*) as FavCount
                        from
                           Favorites F
                        group by
                           F.NewsIdn ) Fav
            ON N.NewsIdn = Fav.NewsIdn
   order by
      SumL.UpLikes DESC

Again, I do not understand why you would have an auto-increment numeric ID column for the news table, then ANOTHER value for it as NewsIdn as a varchar. I would just have this and your other tables reference the News.ID column directly... why have two columns representing the same component. And obviously, each table you are doing aggregates (likes, favorites), should have indexes on any such criteria you would join or aggregate on (hence NewsIdn) column, UserIdn, etc.

And final reminder, this type of query is ALWAYS running aggregates against your ENTIRE TABLE of likes, favorites EVERY TIME and suggest going with denormalized columns to hold the counts when someone so selects them. You can always go back to the raw tables if you ever want to show or update for a particular person to change their like/dislike/favorite status.

You'll have to look into reading on triggers as each database has its own syntax for handling.

As for table structures, this is a SIMPLIFIED version of what I would have (removed many other columns from you SQLFiddle sample)

CREATE TABLE IF NOT EXISTS `News` (
 id int(11) NOT NULL AUTO_INCREMENT,
 UserID integer NOT NULL,
 ... other fields
 `DateCreate` datetime NOT NULL,
 PRIMARY KEY ( id ),
 KEY ( UserID )  
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

extra key on the User ID in case you wanted all news activity created by a specific user.

CREATE TABLE IF NOT EXISTS `Users` (
  id int(11) NOT NULL AUTO_INCREMENT,
  other fields...
  PRIMARY KEY ( id ),
  KEY ( LastName, Name )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ;

additional key in case you want to do a search by a user's name

CREATE TABLE IF NOT EXISTS `Likes` (
 id int(11) NOT NULL AUTO_INCREMENT,
 UserId integer NOT NULL,
 NewsID integer NOT NULL,
 `Type` enum('up','down') NOT NULL,
 `IsFavorite` enum('yes','no') NOT NULL,
 `DateCreate` datetime NOT NULL,
 PRIMARY KEY (`id`),
 KEY ( UserID ),
 KEY ( NewsID, IsFavorite )
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

additional keys here for joining and/or aggregates. I've also added a flag column for being a favorite too. This could prevent the need of a favorites table since they hold the same basic content of the LIKES. So someone could just LIKE/DISLIKE, against a given news item, but ALSO LIKE/DISLIKE it as a FAVORITE the end-user wants to quickly be able to reference.

Now, how do these table structures get simplified for querying? Each table has its own "id" column, but any OTHER table is uses the tableNameID (UserID, NewsID, LikesID or whatever) and that is the join.

select ...
   from 
      News N
         Join Users U
            on N.UserID = U.ID
         Join Likes L
            on N.ID = L.NewsID

Integer columns are easier and more commonly identifiable by others when writing queries... Does this make a little more sense?