Join that shows multiple data from one id

Asked

Viewed 74 times

0

I have 3 tables: "Third parties", "Address third", "Contacts third party". The goal was: a third party would be inserted, it would be listed, it would be assigned an address and a contact, however I would like it to be possible to assign several addresses to a single third party. I first used INNER JOIN in the 3 tables and it was not possible, I switched to LEFT JOIN and when more than one address or contact to a third party was listed the same information more than once, I ended up using RIGHT JOIN and FULL JOIN and nothing. `

//Tabela Terceiros 


	$sql="SELECT Terceiros.*, Email, Telefone, Telemovel, TipoC, Morada, Localidade, CodPostal FROM ((Terceiros INNER JOIN  Contactos_Terceiro on  Terceiros.Numero = Contactos_Terceiro.Numero ) INNER JOIN Morada_Terceiro on Terceiros.Numero = Morada_Terceiro.Numero )";
	if(isset($pesq))
		$sql.=" where Nome like '$pesq' ";
	    $sql.=" limit $ini, $tp";
	    $res=$lig->query($sql); 

		
	//Tabela Contactos_Terceiro
  
  $sql="SELECT  Contactos_Terceiro.*, Nome FROM  Contactos_Terceiro INNER JOIN Terceiros on (Terceiros.Numero =  Contactos_Terceiro.Numero)";
	if(isset($pesq))
	$sql.=" where Nome like '$pesq'";
	$sql.=" limit $ini, $tp";
	$res=$lig->query($sql);

	//Tabela Morada_Terceiro
  
  $sql="SELECT Morada_Terceiro.*, Nome FROM Morada_Terceiro INNER JOIN Terceiros on (Terceiros.Numero = Morada_Terceiro.Numero)";
	if(isset($pesq))
		$sql.=" where Nome like '$pesq'";
	$sql.=" limit $ini, $tp";
	$res=$lig->query($sql);

inserir a descrição da imagem aqui`

sql

1 answer

0

When using the inner, the rows of the table Third party will be repeated for each occurrence in the other tables.

Example:

If you make a inner join of Third party* with **Dwelling, if there are two records of the same Third in the Address_third, the query will return two lines: in both lines the data of the Third will be of the same record.

An alternative is on this page to query ONLY in the table Third party, and query the other tables when the "eye" button is clicked.

  • The problem is that I need the query in the remaining tables because I will fetch the table name field "Third party"

  • But in the others you can use the join without worrying about duplicating records.

  • So how would you do it? Leave it as it is in the Third Party table and the other 2?

  • On this Third Party Listing page select only from the Third Party table; then when you click the button, you pass the IdTerceiro as parameter and makes a second query searching the data you need regarding this IdTerceiro

  • <?php $sql= "SELECT Third Party., Email, Telephone, Telemovel, Tipoc, Address, Locality, Codpostal from Terceiros, Contactos_terceiro, Address_terceiro "; $sql.=" WHERE Contactos_terceiro.Codcon = Terceiros.Codcon and Address_terceiro.Numero = Terceiros.Numero "; $res=$lig->query($sql); while ($Lin=$res->fetch_array()){ ?>* I tried to do so to the button, but gives error

  • What error? And you need to pass the Id of the selected item.

Show 1 more comment

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.