sql join question.

Question

I have two tables, auctions and users.

I want to select the username table from auctions where category=x, and then select the fields a, b and c from the users table, where the username field in users matches th username fields from auctions.

I am trying this:

SELECT AUCTIONS.USERNAME, USERS.firstname, USERS.lastname, USERS.flaggedauctions
FROM AUCTIONS
INNER JOIN USERS
ON AUCTIONS.USERNAME=USERS.USERNAME

Which seems to work. However, USERNAME is not a primary key in either table, and in the auctions table there can be many records with the same username, as where there will be only one record per username in the users table.

The above query works, but the problem arises if I want to limit the result set to say 10, for pagination. This may result in 10 records returned, some of which are duplicates. Is there a way to run a query limiting to 1 record per surname inside of another query?

edit: in answer to Quassnoi's post

usernames are always unique

If I have

Auctions:
    username category blah
    -------------------------------------
    user1  category1 tshirt
    user2  category2 jeans
    user3  category3 shoes
    user2  category3 belt
    user3  category3 pants

Users:
    username firstname lastname
    -------------------------------------
    user1  john  smith
    user2  fred  black
    user3  alice  brady

Then given category 3 as the category, I would want to show:

    username firstname lastname
    -------------------------------------
    user2  fred  black
    user3  alice  brady

With username coming from the auctions table.

Instead, at the moment this will display:

    username firstname lastname
    -------------------------------------
    user2  fred  black
    user3  alice  brady
    user3  alice  brady

edit2:

I am using

SELECT username, firstname, lastname
FROM USERS
WHERE username
IN (

SELECT USERNAME
FROM AUCTIONS
WHERE category = 'fake'
)
LIMIT 0 , 30

Which returns 0 results. There are certainly many records in AUCTIONS with category set to fake.

Answer 1

SELECT DISTINCT ...

Answer 2

If I understood this right, using SELECT DISTINCT instead of SELECT will solve your problem

Answer 3

I would advice to use SELECT DISTINCT, but I rather ask you this question:

Given the tables:

AUCTION     USERNAME
--------    -------
Sotheby's   john
Christie's  john

USERNAME   FIRSTNAME LASTNAME
--------   --------  --------
john       John      Doe
john       John      Davis

, what do you want to be selected for Sotheby's and what for Christie's?

It's impossible to understand what do you mean by "a way to run a query limiting to 1 record per username" unless you answer this question.

Update:

SELECT  *
FROM    users
WHERE   username IN
        (
        SELECT  username
        FROM    auctions
        WHERE   category = 'category3'
        )

Update 2:

Does this query return something?

SELECT  u.username, u.firstname, u.lastname
FROM    auctions a, users u
WHERE   a.category = 'fake'
        AND u.username = a.username

Update 3:

SELECT  ao.username, u.firstname, u.lastname
FROM    (
        SELECT  DISTINCT username
        FROM    auctions a
        WHERE   category = 'fake'
        ) ao
LEFT JOIN
        users u
ON      u.username = a.username

Answer 4

English is not my native language but, if I understand correctly, you want to select all users (once) who has auction in given category x.

I am not sure if this syntax works for MySQL, but try something like:

SELECT
    AUCTIONS.USERNAME,
    USERS.firstname,
    USERS.lastname,
    USERS.flaggedauctions
FROM
(
    SELECT DISTINCT 
     AUCTIONS.USERNAME
    FROM
     AUCTIONS
    WHERE
     AUCTIONS.CATEGORY = x
) AS AUCTIONS
INNER JOIN 
    USERS
ON 
    USERS.USERNAME = AUCTIONS.USERNAME

Answer 5

You can simply use

SELECT DISTINCT
    a.username,
    u.firstname,
    u.lastname
FROM 
    Users u
RIGHT JOIN
    Auctions a
    ON
    u.username = a.username
WHERE
    a.category = 'category3'