CODEWITHSUNDEEP
sql
Jesse McBride
Jesse McBride

Reputation: 91

Select a user by their username and then select data from another table using their UID

Sorry if that title is a bit convoluted... I'm spoiled by an ORM usually and my raw SQL skills are really poor, apparently.

I'm writing an application that links to a vBulletin forum. Users authenticate with their forum username, and the query for that is simple (selecting by username from the users table). The next half of it is more complex. There's also a subscriptions table that has a timestamp in it, but the primary key for these is a user id, not a username.

This is what I've worked out so far:

SELECT
    forum.user.userid,
    forum.user.usergroupid,
    forum.user.password,
    forum.user.salt,
    forum.user.pmunread,
    forum.subscriptionlog.expirydate
FROM
    forum.user
    JOIN forum.subscriptionlog
WHERE
    forum.user.username LIKE 'SomeUSER'

Unfortunately this returns the entirety of the subscriptionlog table, which makes sense because there's no username field in it. Is it possible to grab the subscriptionlog row using the userid I get from forum.user.userid, or does this need to be split into two queries?

Thanks!

Upvotes: 2

Views: 2716

Answers (3)

N Kumar
N Kumar

Reputation: 1312

try this 

SELECT
forum.user.userid,
forum.user.usergroupid,
forum.user.password,
forum.user.salt,
forum.user.pmunread,
forum.subscriptionlog.expirydate
FROM
forum.user
INNER JOIN forum.subscriptionlog
ON forum.subscriptionlog.userid = forum.user.userid
WHERE
forum.user.username LIKE 'SomeUSER'

Upvotes: 1

gbjbaanb
gbjbaanb

Reputation: 52659

select * from user u JOIN subscriptions s ON u.id = s.id where u.username = 'someuser'

The bit in bold is what you want to add, it combines the 2 tables into one that you return results from.

Upvotes: 1

dursk
dursk

Reputation: 4445

The issue is that you are blindly joining the two tables. You need to specify what column they are related by.

I think you want something like:

SELECT * FROM user u
   INNER JOIN subscriptionlog sl ON u.id = sl.userid
WHERE u.username LIKE 'SomeUSER'

Upvotes: 2

Related Questions