CODEWITHSUNDEEP
mysqlsql
Shane Larson
Shane Larson

Reputation: 597

Doubles in MySQL Query

I'm trying to make it so I can find people with a certain zipcode but list the results by their full name in ASC order under the users table...

  SELECT profiles.id 
    FROM `profiles`,`users` 
   WHERE profiles.zipcode = '$ZIPCODE' 
ORDER BY users.full_name ASC

It like shows doubles.

Upvotes: 0

Views: 112

Answers (3)

Yuck
Yuck

Reputation: 50835

In your query you're doing this:

FROM `profiles`,`users`

and not providing a JOIN or WHERE condition relative to the relationship of those tables. This is resulting in a cartesian product. You're lucky you're only getting doubles; the result could easily be larger.

Upvotes: 0

user554546
user554546

Reputation:

If you only want to show distinct IDs, then this will work (depending on your dialect of SQL, you may also be able to select distinct profiles.id and skip the group by statement).

   SELECT profiles.id 
   FROM (select * from `profiles`,`users` 
         WHERE profiles.zipcode = '$ZIPCODE' 
         ORDER BY users.full_name
         )subquery
   GROUP BY profiles.id;

Upvotes: 0

Kelend
Kelend

Reputation: 1427

You need a join I think, try something like

SELECT Users.full_name
FROM Users
INNER JOIN profiles
ON profiles.user_id = users.id
WHERE profiles.zipcode = '$ZIPCODE' 
ORDER BY users.full_name ASC

I assume there is a foreign key for users in the profiles table? The answer assumes a foreign key of user_id in the profiles table

EDIT:

A foreign key is a key belonging to another table. So if you have a user and a profile table, you would most likely have a column in the profile table, that for each profile references a unique users (assuming users only have 1 profile each)

Upvotes: 2

Related Questions