0

apologies as i am fairly new to SQL but im having trouble finding a solution to a problem.

I have two tables outline bellow:

Comment table

Columns: Name, Comment, rating a, rating b, rating c, venue_id

Venue table

Columns: id, venue_name, description, address

I would like to get the Name from the venue table where the id in the same row matches that of the venue id in the comment table, is this possible? (The venue_id and id match)

For example if i were to print this i would have

venue_name       (from the venue table)
rating a         (from the comment table)
rating b         (from the comment table)
rating c         (from the comment table)

(the ratings linked to the venue_name through the venue id in the comment table and the id in the venue table)

apologies if this doesn't make sense but would appreciate some help if possible. Thanks

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794
  • possible duplicate of [How can an SQL query return data from multiple tables](http://stackoverflow.com/questions/12475850/how-can-an-sql-query-return-data-from-multiple-tables) – Clockwork-Muse Apr 20 '14 at 07:53

4 Answers4

1

Use Simple Join query. SELECT Venue.venue_name, Comment.rating_a, Comment.rating_b, Comment.rating_c FROM Venue INNER JOIN Comment ON Venue.id=Comment.venue_id;

This will give you:

  • venue_name (from the venue table)
  • rating a (from the comment table)
  • rating b (from the comment table)
  • rating c (from the comment table)
A H K
  • 1,758
  • 17
  • 29
  • Perfect, works exactly how i wanted it to. Thank you for your help! –  Apr 20 '14 at 14:55
0
select Venue.venue_name, 
rating_a, rating_b, 
rating_c from Venue, 
Comment 
where Comment.venue_id = Venue.id;

Explanation:

Venue.venue_name and the three rating_ identifiers all tell SQL which columns you want to output (note: you may have to prepend Comment. in front of the three rating columns, but I think it will work without that since those columns are uniquely named).

The from statement must contain each table you want to draw data out of (whether for display or for a where clause).

Finally, the where clause is how the rows in Venue get related to the rows in Comment (they share a common venue_id). You'll need to make sure that venue_id in Venue is a primary key (and ideally, set it to auto_increment)

Erran Morad
  • 4,563
  • 10
  • 43
  • 72
Greg
  • 192
  • 7
0

You might need to set up reference key for id in the venue table. Just make venue_id as a primary key and reference that to the id in the Venue table.

So the sql query would be - 

SELECT venue.venue_name, 
rating_a.comment, 
rating_b.comment
FROM venue, comment
INNER JOIN venue
ON Comment.venue_id = Venue.id;

or just use where

select Venue.venue_name, 
rating_a, 
rating_b
from Venue, Comment
where Comment.venue_id = Venue.id;

First thing to note is that when joining these two tables you need to set up the primary and foreign key... for Venue table set up a primary key and then set up a foreign key. You can make the primary key for Venue table as auto increment and then reference the foreign key as -id- to the venue_id from Comment table.

Erran Morad
  • 4,563
  • 10
  • 43
  • 72
SnowmanOnFire
  • 63
  • 3
  • 12
0

You can use SQL JOIN

Try

SELECT s.*, AS venue_name 
FROM venuetable s 
LEFT JOIN commenttable  
AS r 
ON r.venue_id
rhill45
  • 559
  • 10
  • 35