Inner Join ligando 9 tabelas

[Vagner Bucioli Scala 0]

Pessoal! preciso fazer um inner join com 8 tabelas, em ASP + ACCESS!! Alguém tem uma sugestão de uma maneira fácil e abreviada de como fazer?? agradeço!

[Ted k' 126]

mais qual seria a finalidade disso cara?

[Claudio Neto 3]

Ter como fazer tem sim. Mas acho que existe uma maneira resumida de se fazer.

 

Acho que só ligando tabela por tabela mesmo, talvez o que possa fazer para encurtar é utilizar alias, mas mesma assim fiará grande...

 

Boa sorte...

[Vagner Bucioli Scala 0]

A finalidade é o seguinte..num site de RH, a mulher tem 8 tipos de pesquisas diferentes, Pesquisa CPF, Pesquisa RG, Pesquisa Veículos >> Pesquisa Empregos anteriores..totalizando em 8 tipos de pesquisas diferentes.

 

No caso montei o sistema com 8 tabelinhas, uma para cada tipo de pesquisa. Ocorre que no fechamento do mês ela quer listar todas as pesquisas feitas por um determinado cliente. Daí preciso mandar vasculhar em todas as 8 tabelas o Código deste cliente!

 

obrigado pela ajuda!

[jrcardozo 4]

dependendo do caso use o EXISTS para ter uma consulta muito mais rapida

[Vagner Bucioli Scala 0]

oi Jonatha! já ouvi falar deste tal de "Exists"!! você tem um exemplo bem simples pra postar? obrigado!

[filipescoob 1]

Segue ai quentinho do HELP do SQL

 

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
GO


ou 

USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
GO

ou até

 

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)
GO

-- Or, using = ANY

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
   (SELECT city
   FROM publishers)
GO

ou a documentação do Exists no sql

 

EXISTS
Specifies a subquery to test for the existence of rows. 

Syntax
EXISTS subquery

Arguments
subquery

Is a restricted SELECT statement (the COMPUTE clause, and the INTO keyword are not allowed). For more information, see the discussion of subqueries in SELECT. 

Result Types
Boolean

Result Values
Returns TRUE if a subquery contains any rows.

Examples
A. Use NULL in subquery to still return a result set
This example returns a result set with NULL specified in the subquery and still evaluates to TRUE by using EXISTS.

USE Northwind
GO
SELECT CategoryName
FROM Categories
WHERE EXISTS (SELECT NULL)
ORDER BY CategoryName ASC
GO

B. Compare queries using EXISTS and IN
This example compares two queries that are semantically equivalent. The first query uses EXISTS and the second query uses IN. Note that both queries return the same information.

USE pubs
GO
SELECT DISTINCT pub_name
FROM publishers
WHERE EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
GO

-- Or, using the IN clause:

USE pubs
GO
SELECT distinct pub_name
FROM publishers
WHERE pub_id IN
   (SELECT pub_id
   FROM titles
   WHERE type = 'business')
GO

Here is the result set for either query:

pub_name								 
---------------------------------------- 
Algodata Infosystems					 
New Moon Books						   

(2 row(s) affected)

C. Compare queries using EXISTS and = ANY
This example shows two queries to find authors who live in the same city as a publisher. The first query uses = ANY and the second uses EXISTS. Note that both queries return the same information.

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE exists
   (SELECT *
   FROM publishers
   WHERE authors.city = publishers.city)
GO

-- Or, using = ANY

USE pubs
GO
SELECT au_lname, au_fname
FROM authors
WHERE city = ANY
   (SELECT city
   FROM publishers)
GO

Here is the result set for either query:

au_lname								 au_fname			 
---------------------------------------- -------------------- 
Carson								   Cheryl			   
Bennet								   Abraham			  

(2 row(s) affected)

D. Compare queries using EXISTS and IN
This example shows queries to find titles of books published by any publisher located in a city that begins with the letter B.

USE pubs
GO
SELECT title
FROM titles
WHERE EXISTS
   (SELECT *
   FROM publishers
   WHERE pub_id = titles.pub_id
   AND city LIKE 'B%')
GO

-- Or, using IN:

USE pubs
GO
SELECT title
FROM titles
WHERE pub_id IN
   (SELECT pub_id
   FROM publishers
   WHERE city LIKE 'B%')
GO

Here is the result set for either query:

title																			
------------------------------------------------------------------------ 
The Busy Executive's Database Guide											  
Cooking with Computers: Surreptitious Balance Sheets							 
You Can Combat Computer Stress!												  
Straight Talk About Computers													
But Is It User Friendly?														 
Secrets of Silicon Valley														
Net Etiquette																	
Is Anger the Enemy?															  
Life Without Fear																
Prolonged Data Deprivation: Four Case Studies									
Emotional Security: A New Algorithm											  

(11 row(s) affected)

E. Use NOT EXISTS
NOT EXISTS works the opposite as EXISTS. The WHERE clause in NOT EXISTS is satisfied if no rows are returned by the subquery. This example finds the names of publishers who do not publish business books.

USE pubs
GO
SELECT pub_name
FROM publishers
WHERE NOT EXISTS
   (SELECT *
   FROM titles
   WHERE pub_id = publishers.pub_id
   AND type = 'business')
ORDER BY pub_name
GO

Here is the result set:

pub_name								 
---------------------------------------- 
Binnet & Hardley						 
Five Lakes Publishing					
GGG&G									
Lucerne Publishing					   
Ramona Publishers						
Scootney Books						   

(6 row(s) affected)

[Vagner Bucioli Scala 0]

valeu pessoal! o ruim que estou usando o ACESS e no access este tal de Exists não rola! mas obrigado mesmo assim!

[filipescoob 1]

Use o IN!!!!

 

Tem exemplos do uso do IN acima que se aplicam a access tambem