SELECT data with primary keys from another table?

Question

I have a 'users' table, an 'offers' table, and a junction '*users_offers*' with userIDs and offerIDs.

How to select every offer from offers that belongs to a particular user?

I could achieve a solution (below) that actually starts select data from the junction table, then simply joins the offer columns, but the result is containing the junction columns as well.

SELECT * 
FROM users_offers INNER JOIN offers ON users_offers.offerID = offers.ID 
WHERE userID = 1

Is there any solution that starts selection with offers?

Instead of selecting *, select just the columns you need. – Dan Bracuk Apr 20 '13 at 22:51 — Dan Bracuk, Apr 20 '13 at 22:51

CodeZombie · Accepted Answer · 2013-04-20T22:56:55.097

2

Use SQL Alias:

SELECT o.* 
FROM users_offers AS uo
INNER JOIN offers AS o ON uo.offerID = o.ID 
WHERE uo.userID = 1

Explanation:

FROM users_offers AS uo sets a new alias called "uo" pointing to the [users_offers] table. The same happens for offers AS o. These alias can be used in the other parts of your SQL statement.

edited Apr 20 '13 at 22:56

answered Apr 20 '13 at 22:48

CodeZombie

5,367
3
30
37

Whoa, thanks. Actually I could omit users_offers alias, only 'o' seems enough. – Geri Borbás Apr 20 '13 at 22:52
Yes, you can, but I usually set alias for all tables used to make future changes easier. But that's a matter of taste. – CodeZombie Apr 20 '13 at 22:55
I'm just in the early phase of complexity, so considerations like this feels overheads to me for now. Thanks again, spared me hours of Googling. :D – Geri Borbás Apr 20 '13 at 22:57

score 0 · Answer 2 · answered Apr 20 '13 at 22:54

0

SELECT offers_alias.*
FROM users_offers
INNER JOIN offers AS offers_alias ON users_offers.offerID = offers_alias.ID 
WHERE users_offers.userID = 1

answered Apr 20 '13 at 22:54

Geri Borbás

15,810
18
109
172

SELECT data with primary keys from another table?

2 Answers2