CODEWITHSUNDEEP
mysql
angry kiwi
angry kiwi

Reputation: 11495

Select one row from this table and all related rows from other table

table user

id | name | address

3 | Jacko | 33A Herewini

table user_photo

id | userid | thumb | full

1 | 3 | 3k1j_thumb.jpg| 3k1j.jpg

1 | 3 | 3k1j_thumb.jpg| 3k1j.jpg

2 | 14 | 44r_thumb.jpg| 44r.jpg

2 | 14 | 55t_thumb.jpg| 55t.jpg

2 | 14 | 12f_thumb.jpg| 12f.jpg

I got the user id, I want to select his name and address and all his photos

PS: what tool/software you use to draw the table line (the +---+) ?

Edit: then how would you put the name in a div and all the photos in a ul

my html look like this

<div class='name'></div>

...600 elements...

<ul class='photos'></ul>

Upvotes: 1

Views: 4648

Answers (3)

Ondra Žižka
Ondra Žižka

Reputation: 46904

Learn about LEFT JOIN, see e.g. http://www.postgresql.org/docs/8.2/static/sql-select.html 

SELECT * FROM user AS u LEFT JOIN user_photo AS p ON p.userid = u.id

Adjust as needed.

Upvotes: 1

Eric Petroelje
Eric Petroelje

Reputation: 60549

To get the address, you can just query that table:

   SELECT * FROM user WHERE id = 3

To get the photos, you can query the user photo table:

   SELECT * FROM user_photo WHERE userid = 3

If you want to get everything at once, you can join the two tables together on the user id:

   SELECT * FROM user u
   LEFT JOIN user_photo up ON up.userid = u.id
   WHERE u.id = 3

Note though that the address info will of course be duplicated on every row

Upvotes: 1

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58491

To combine the results of two tables linked by one column (id in this case), you would use a JOIN.

You can read up on JOINs in the MySQL Reference Manual

SELECT *
FROM   user u
       LEFT OUTER JOIN user_photo up ON up.userid = u.id

Upvotes: 0

Related Questions