CODEWITHSUNDEEP
sqlmysql
Joshxtothe4
Joshxtothe4

Reputation: 4193

sql join 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.

Upvotes: 0

Views: 208

Answers (5)

Stephen Denne
Stephen Denne

Reputation: 37007

Use SELECT DISTINCT instead of SELECT.

Upvotes: 2

Russ Cam
Russ Cam

Reputation: 125488

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'

Upvotes: 0

Quassnoi
Quassnoi

Reputation: 425291

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

Upvotes: 5

Grzegorz Gierlik
Grzegorz Gierlik

Reputation: 11232

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

Upvotes: 0

soulmerge
soulmerge

Reputation: 75704

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

Upvotes: 2

Related Questions