join two tables that have no foriegn keys in each other via a third table

Question

Let's say that I have three tables: A,B and C.

I want to join A and C but there is no foriegn key from A to C or from C to A, but B has foriegn keys from both of them !

I want to update a line in A but in Condition that depends on the values on a record in C !! I hope i'm making this clear enough, sorry for my bad english . Thank you in advance !!

You might want to clarify your problem with examples, though personally I do not believe you have researched this enough yourself. Look into how JOINS are used in SQL as this is quite a basic problem that a quick search would solve. — talegna, May 21 '14 at 10:40
Ok, so B is implementing a many-to-many relationship between A and C. That is clear. But what you want to do is not... please explain what you want... what you tried... what errors you are getting. — Frazz, May 21 '14 at 10:40
This is a very common situation actually.Please provide the table structures of A, B , C and the relation(foreign keys) between them — A Nice Guy, May 21 '14 at 10:40
`but in Condition that can only be viewed in C` what does that mean — Artur Udod, May 21 '14 at 10:40
A(titre(30), nature(20), responsable(20)) C(nom(30), couleur(10), volume(double), ratelier(int), camion(int)) B(titre(30), utilisateur(20), accessoire(30)) the "titre" in B is a fk of "titre" in A and "accessoire" in B is a fk of "nom" in C so i want to edit the "responsable" column in A if he is using an accessoire that has a volume > 0.3 so here is it — Hamzaoui, May 21 '14 at 10:48
You can join the three table as described in the various answers below. However - foreign key relationships between tables are not random! Each encodes one very specific bit of information about your business. Be very, very certain that your table "B" has a meaning suitable for your current purpose. Otherwise your results will be senseless junk. — Michael Green, May 21 '14 at 10:52
@MichaelGreen the result was i exactly what i wanted. so that makes anything but senseless junk. — Hamzaoui, May 21 '14 at 10:57
Good. I'm genuinely glad you got your question answered. Be aware it only works because table B encodes the meaning "A *is using* Accessoire C" and that is what you are looking for. If it had encoded the meaning "A *manufactures* Accessoire C" or "A *sells* Accessoire C" it would have been useless for your purpose. — Michael Green, May 21 '14 at 11:07

score 1 · Accepted Answer · edited May 23 '17 at 12:23

1

I don't think your question is particularly clear, but if I understand correctly you want to update table A depending on constraints in table C. Something along the lines of the following would work:

UPDATE
    a
SET
    columnName = 'newValue'
FROM
    a
    INNER JOIN b ON a.columnA = b.columnA
    INNER JOIN c ON c.columnC = b.columnC
WHERE
    c.columnName = 'condition'

You might also find the following question useful:

How do I UPDATE from a SELECT in SQL Server?

edited May 23 '17 at 12:23

Community

1
1

answered May 21 '14 at 10:43

talegna

2,407
2
19
22

OMG it worked . thank you very much, you're genius enough to undestand why i was trying to say . thanks a lot – Hamzaoui May 21 '14 at 10:51
You might want to flag an answer then which will help others identify that you've already solved your problem :-) – talegna May 21 '14 at 10:57

join two tables that have no foriegn keys in each other via a third table

1 Answers1