CODEWITHSUNDEEP
mysql
hawarden_
hawarden_

Reputation: 2160

MySQL : Get row of table on same column of three tables

I have three tables, the first one is called "File" :

JobId    FilenameId    FileId
5        2             1  
7        3             2

And the second one is called "Filename" 

Filename    FilenameId
File1         2    
File2         3

And the third one is called "Client" :

ClientId    JobId
1           5 
2           7

Now I want to get the ClientId of File1, how can I do it? I'm new to SQL.

Thanks.

Edit : this is what I tried but it's not working

Select c.ClientId 
From `File` f, Filename fn, Client c
Where f.FilenameId = fn.FilenameId and f.JobId = c.JobId and fn.Filename = "File1";

Upvotes: 0

Views: 50

Answers (2)

DRapp
DRapp

Reputation: 48139

First, I hate the negative banter that sometimes goes on, but yes, you need to get yourself more educated in SQL during your learning. Look here at real-life scenarios and how people offer different solutions to the same.

Now to YOUR question. First, get rid of old style sql where you put all the join criteria in your where clause. Get started knowing the proper relationships between the tables. Second, your WHERE clause should be the basis of your specific criteria -- such as you want File 1. From that, get to the other tables. My personal standard of SQL coding shows first the what criteria do I want and from what table. Ensure indexes are available for optimizing the query. THEN join to the other tables to get the other elements needed to complete the row of data. (Good use of table "aliases", and keep with it).

First, your main criteria. Simple enough.

select
      fn.FileNameID,
      fn.FileName
   from
      FileName fn
   where
      fn.FileName = 'File1'

From there, do your joins to get the next pieces of information from file to client relationships 

select
      fn.FileNameID,
      fn.FileName,
      c.clientID
   from
      FileName fn
         JOIN File f
            on fn.FileNameID = f.FileNameID
            JOIN Client c
               on f.JobID = c.JobID
   where
      fn.FileName = 'File1'

Notice the hierarchical indentation from file name to the file, then from file to the client... you can visually see how the tables are related. Then, just grab your other columns as you need and add to your field list with proper aliases.

Upvotes: 2

user4222503
user4222503

Reputation:

Try this:

select ClientId from Client where JobId in (select JobId from File where FilenameId in (select FilenameId from Filename where Filename="File1"));

Upvotes: 0

Related Questions