By William

2019-03-14 20:10:48 8 Comments

I have two tables (users and posts) and I want to write out all posts (among other things) by one user. I'm thinking I should use a JOIN and WHERE but I get an error for using WHERE.

This is my code:

SELECT username, post, joinDate, title, info FROM users 
    WHERE userId='18' 
    JOIN posts ON users.userId=posts.userId 
    ORDER BY date DESC

I'm new to this and perhaps there is a better way but I can't figure it out atm. Thankful for all answers!


@Naveed Ramzan 2019-03-14 20:28:00

SELECT username, post, joinDate, title, info
FROM users 
JOIN posts ON users.userId=posts.userId and users.userId='18'

if userId is in users table then YES you can use where userId='18'.

If userId is in posts table then it should be userId='18' be in join part.

@Dharman 2019-03-14 20:29:59

You missed the point and you answer is creating more confusion for beginners like OP.

@Thermos 2019-03-14 20:12:23

The JOIN clause comes before the WHERE clause, after the FROM clause. First you join together all the tables you need, then you do your filtering with WHERE. Like this:

SELECT username, post, joinDate, title, info
FROM users 
JOIN posts ON users.userId=posts.userId 
WHERE users.userId='18'

@William 2019-03-14 20:20:01

I get "#1052 - Column 'userId' in where clause is ambiguous" when I do that, what might be causing this?

@Dharman 2019-03-14 20:20:39

You can try with JOIN posts USING(userId)

@Thermos 2019-03-14 20:21:42

You need to specify your table alias before any column names in the WHERE clause that are contained in more than one of the tables that you have joined together. WHERE users.userID = '18'

@The Impaler 2019-03-14 20:22:31

Change to WHERE users.userId='18' to avoid ambiguity. `

@William 2019-03-14 20:24:43

That did it! Thank you!

@Zaynul Abadin Tuhin 2019-03-14 20:22:06

try like below

    SELECT u.*,p.*
    FROM users u JOIN posts p ON u.userId=p.userId
    WHERE u.userId=18      
    ORDER BY date DESC

where will be after join and 18 is int datatype value so not need single quote for this and use alias for avoiding ambigous column name

@Dharman 2019-03-14 20:24:31

Why? Selecting * is always a bad idea, do not recommend it. Why do you think that aliasing with single letters is going to help?

@Zaynul Abadin Tuhin 2019-03-14 20:25:08

@Dharman i used alias name of table that is not like * selection so du you think table alias name .* and * is same ?

@Zaynul Abadin Tuhin 2019-03-14 20:27:12

@Dharman so before downvoting you have to clear about your idea

@Dharman 2019-03-14 20:28:11

I made 2 points. First is that you should avoid SELECT * always. Second is that the single letter alias is not needed and does not help with anything; it makes it more difficult to read.

@Zaynul Abadin Tuhin 2019-03-14 20:33:54

@Dharman here in SO the user who provide answer mostly for each question of answer related to sql always use signle letter alias name in his query do you know why? because here most people submit subset or sample of their real life problem so it is mandatory to inform OP's what he need to solve this problem not all in all hope you got my point

Related Questions

Sponsored Content

42 Answered Questions

[SOLVED] How to import an SQL file using the command line in MySQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

24 Answered Questions

[SOLVED] What is the difference between "INNER JOIN" and "OUTER JOIN"?

27 Answered Questions

[SOLVED] Finding duplicate values in a SQL table

  • 2010-04-07 18:17:29
  • Alex
  • 2393120 View
  • 1630 Score
  • 27 Answer
  • Tags:   sql duplicates

30 Answered Questions

[SOLVED] Convert HTML + CSS to PDF with PHP?

33 Answered Questions

[SOLVED] How do I UPDATE from a SELECT in SQL Server?

3 Answered Questions

15 Answered Questions

[SOLVED] How to Delete using INNER JOIN with SQL Server?

37 Answered Questions

10 Answered Questions


Sponsored Content