-1

I have these tables:

News

  • newsId

Companies

  • companyId

Users

  • userId
  • company_id

NewsShare

  • news_id
  • user_id

I want to list all news from a company AND all news that a user from that company has shared. How do I do that? I have tried with this:

sql = "SELECT * FROM news,users,companies WHERE user_id = userId 
AND company_id = companyId AND ((companyId = " & Request("company") & ") 
OR (newsId IN (SELECT news_id FROM NewsShare,Users 
WHERE user_id = userId AND company_id = " & Request("company") & ")) 
ORDER BY createdate desc"

but it doesn't work. Any suggestions?

Tushar
  • 3,527
  • 9
  • 27
  • 49
Sanne Faurholt
  • 63
  • 7
  • 1
    Use Access query designer to get correct syntax. Use JOIN ON clause to link tables instead of WHERE. Need table prefix to field names when same field name is used in multiple tables. Need to specify each table name with a wildcard in the SELECT. Add sample data and desired output as formatted text tables. – June7 Mar 27 '23 at 18:47
  • 1
    "Doesn’t work" isn’t a question, it helps no one. Consider reading through and applying the suggestions in [ask]. – user692942 Mar 27 '23 at 22:32
  • Does this answer your question? [Breaking a String Across Multiple Lines](https://stackoverflow.com/a/37565683) – user692942 Apr 05 '23 at 08:17

1 Answers1

0

Your code does not show line continuation and concatenation.

Access is very picky about parentheses for JOIN ON clause. Best to use Access query designer to get correct syntax.

Consider simple JOIN of tables and applying filter to companyId field in Users table.

sql = "SELECT News.*, Users.*, Companies.* FROM News " & _
"INNER JOIN ((Companies INNER JOIN Users ON Companies.companyId = Users.company_id) " & _
"INNER JOIN NewsShare ON Users.userId = NewsShare.user_id) ON News.newsId = NewsShare.news_id " & _
"WHERE Users.company_id=" & Request("company") & " " & _
"ORDER BY createdate Desc"
June7
  • 19,874
  • 8
  • 24
  • 34