Fetching information across tables

[SorenIX]

Hello everyone,

I've been looking around to solve my problem. I even went and recovered my old class note about SQL but haven't found anything. And yet I know it's kinda simple... but I just don't remember clearly.

In the scheme below, I want to fetch all the info in Table3 for all the Tiers that has the AppID in Table2. There could be more than 1 Tier using the AppID. I don't know if I'm clear though.

Table1            Table2             Table3
 
[COLOR=blue]AppID (PK)[/COLOR] _    AutoNb (PK)      AutoNB
Name          \   [COLOR=red]Tier (FK)[/COLOR] ---_    Name
                   - [COLOR=blue]AppID (FK)[/COLOR]    \_ [COLOR=red]Tier (PK)[/COLOR]

Here's my failing attempt...

SELECT Table3.*
FROM Table3
WHERE Table3.Tier = (SELECT AppID
                     FROM Table1
                     WHERE Table1.AppID = 2002);

Thanks in advance.

 

[SorenIX]

I thought giving some values would be of better help...


Table1

1 | AppA
2 | AppB
3 | AppC

Table 2

1 | TierA | AppA
2 | TierB | AppB
3 | TierC | AppC
4 | TierB | AppA
5 | TierC | AppB

Table3

1 | Smith | TierA
2 | Bob | TierB
3 | John | TierC


So if I run a query that search for every person that has AppA related to them, the query should return :

1 | Smith | TierA
2 | Bob | TierB

 

[Mrs.Smith]

hmm... you wrote:

I want to fetch all the info in Table3 for all the Tiers that has the AppID in Table2

... then you showed the code:

SELECT Table3.*
FROM Table3
WHERE Table3.Tier = (SELECT AppID
FROM Table1
WHERE Table1.AppID = 2002);

...
But there is a table number discrepancy - try changing the table1 to table2 first, then post back if that's not the fix.

 

[SorenIX]

This query is not working because it would return a record, while the WHERE clause is only looking for a value to fetch many (possibly) records.

Get it?

 

[SorenIX]

I'm still needing a solution!!!

I was busy with other work but now that's the only thing left...

 

[Mrs.Smith]

your tables are not set up right. i rebuilt your basic platform a different way and got the results:

here's what you need:
1. table for Apps - fields: AppID, AppName - (appA, appB, appC)
2. table for contacts - fields: ContactID, Name - (Smith, Bob, John)
3. table for tiers - fields: TierID, TierName - (tierA, tierb, tierc)
4. table for 'events' or whatever this database is tracking.

Events table should have the following fields:
EventID, Name (drawn from contacts table), Tier (drawn from tiers table), App (drawn from app table)

Then you build a simple query off the 'events' table, add all the fields, and set the criteria for your App field as ="AppA" or whatever your critera is.. you will get the results you want if the data is entered correctly.

I named my tables differently, but the sql for the query is this:
SELECT [t_Event Query].ID, [t_Event Query].Name, [t_Event Query].Tier, [t_Event Query].App
FROM [t_Event Query]
WHERE ((([t_Event Query].App)="AppA"));

Or, doing it your way:
SELECT [t_Event Query].*
FROM [t_Event Query]
WHERE ((([t_Event Query].App)="AppA"));

 

[Mrs.Smith]

I'm assuming that each record will always have a name, a tier and an app associated with it...

 

[Mrs.Smith]

If you want a prompt with the option to search for other app names, use this sql with the set up i described above.

SELECT [t_Event Query].*
FROM [t_Event Query]
WHERE ((([t_Event Query].App)=[Enter App Name]));

 

[SorenIX]

Thanks for your answers!

One thing though, the client is actualy the tier. So on the table you call events, there's only EventID, Tier, AppID...

In my sketch, Table1 is App Table, Table2 is Events Table and Table3 is Tier Table(client at the same time, they're the same thing in this context)