CODEWITHSUNDEEP
sqljoin
chuy08
chuy08

Reputation: 139

SQL select - one to many to many

I have three tables like follows:

m(id INT, name TEXT)
tn(id INT, name TEXT, valueid  INT)
tv(valueid INT, name TEXT)

In the m table there are only unique entries. The 'tn' table will contain at least one value that matches the m.id field, usually there are many. Finally the 'tv' table has many values that are associated back to the tn table using the valueid field.

The outcome would be to be able to pick a value from the m table find it's corresponding value in the 'tn' table like so m.id = tn.id. Lastly I need to get out all the tv values that associate back to the tn.valueid field.

Output would be something like so

foo host bar
foo host foobar
bar host1 foo

Where column 1 is from m.name, column 2 is from tn.name and column 3 from tv.names. For each entriy in tv.names will result in a new line of output for my query.

Update:

So far I have been able to construct the first query which allows me to pick out the unique name in m and it's associated values in tn.name:

select m.name, tn.name from m, tn where m.id = tn.id and m.id = 128;

I need to extend this to include the third table and limit the choice by the valueid field in other two tables.

Thanks.

Upvotes: 0

Views: 293

Answers (1)

ruakh
ruakh

Reputation: 183602

Keeping your current notation, you can add the third table using the same approach:

select m.name, tn.name, tv.name
  from m, tn, tv
 where m.id = tn.id
   and tn.valueid = tv.valueid
   and m.id = 128
;

However, I think most people nowadays consider it better to use the ANSI join notation, which is more explicit:

SELECT m.name, tn.name, tv.name
  FROM m
  JOIN tn ON tn.id = m.id
  JOIN tv ON tv.valueid = tn.valueid
 WHERE m.id = 128
;

(It's more explicit in that some of the conditions go in specific ON clauses, clarifying the nature of the join, rather than putting everything in a big WHERE clause.)

Upvotes: 1

Related Questions