Inner Join, Union, Alias Help Please

Results 1 to 2 of 2

Thread: Inner Join, Union, Alias Help Please

  1. #1
    Join Date
    Dec 1969

    Default Inner Join, Union, Alias Help Please

    I am attempting to pull the Top 6 rows from a join of 3 tables ordered by the column DateAdded in each table. I want to pull <BR><BR>the 6 most recent entries using the DateAdded columns from the combined data of these 3 tables with no preference to how <BR><BR>many, if any, are selected from each table. The tables and columns I wish to pull data from are as follows:<BR><BR>tblPdf [table]<BR> FileName [varchar]<BR> DisplayName [varchar]<BR> DateAdded [datetime]<BR><BR>tblLinks [table]<BR> LinksID [int primary key]<BR> Title [varchar]<BR> DateAdded [datetime]<BR><BR>tblArticles [table]<BR> ArticleID [int primary key]<BR> Title [varchar]<BR> DateAdded [datetime]<BR><BR>I&#039;ve attempted using an inner join but can&#039;t get it to ORDER BY tblPdf.DateAdded, tblLinks.DateAdded, tblArticles.DateAdded. <BR><BR>I receive the &#039;Incorrect syntax near the keyword &#039;BY&#039;.&#039; error. Here&#039;s the content of this sql statment <BR>{ SELECT Top 6 tblPdf.FileName, tblPdf.DisplayName, tblPdf.DateAdded, tblLinks.LinksID, tblLinks.Title, <BR><BR>tblLinks.DateAdded, tblArticles.ArticleID, tblArticles.Title, tblArticles.DateAdded <BR><BR>FROM tblPdf, tblLinks, tblArticles <BR>ORDER BY tblPdf.DateAdded, tblLinks.DateAdded, tblArticles.DateAdded DESC<BR>}<BR><BR>I&#039;ve also attempted to use a UNION and alias setting FileName, LinksID, and ArticleID AS ID but because FileName is [varchar] <BR><BR>and ArticleID and LinksID are [int] it doesn&#039;t work. Here&#039;s the content of this sql statement <BR>{<BR>SELECT tblPdf.FileName AS ID, tblPdf.DisplayName AS Title, tblPdf.DateAdded AS AddDate FROM tblPdf <BR>UNION SELECT tblLinks.LinksID AS ID, tblLinks.Title AS Title, tblLinks.DateAdded AS AddDate FROM tblLinks <BR>UNION SELECT tblArticles.ArticleID AS ID, tblArticles.Title AS Title, tblArticles.DateAdded AS AddDate FROM tblArticles <BR>ORDER BY AddDate DESC<BR>} and to capture the top 6 I either SELECT TOP 2 from each table or set a counter in the recordset loop.<BR><BR>Thanks for your help!<BR>

  2. #2
    Join Date
    Dec 1969

    Default ORDER BY 3 DESC

    3 being the column number in your select statement. I believe DateAdded is the third column.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts