Take data related to more than one table separately?

Asked

Viewed 169 times

-1

There’s no way I’m doing this. It seems to be with Join, but when I try, returns me zero records.

I have the tables: TB1, TB2, TB3, TB4, TB...etc All with PK (ID)

I tried that code:

SELECT ID
FROM TB1
inner join TB2 on (TB1.ID = TB2.ID)
inner join TB3 on (TB1.ID = TB3.ID)
inner join TB4 on (TB1.ID = TB4.ID)

Here it will only display the ID that is common in the four TB’s. I want it to show if it appears in any of them.

php mysql query pdo crud
  • 1

    Romulo, I don’t understand the relationship between the tables. You relate ID to ID, being PK, theoretically it is wrong if there is no relation always 1 = 1, in all tables. You would have to have a field on a main table, with FK for the other tables. Or else detail your scenario better because it doesn’t make sense. If you have any questions about how to improve your question, take a look: How to create a Minimum, Complete and Verifiable example

  • I want to buy a main table with other various tables. Every time a record of the main table is found in any of the other tables, not at all, it will appear in select.

  • I understand, but you have to have relationship between them. Example: TB1.campoX will be FK of TB2.ID. And so each with his FK with the "main table"

  • I can’t compare the main PK to index fields in the others? I have to have FK’s?

  • There is no logic to this. You always have the same ID for several other tables as relationship. Think of a register of "people" and the other tables that are "state", "municipality", "telephones" (which may have more than 1), etc how would have connection? This is still only 1 hypothesis, there are several others.

  • These data inserted in the other tables were created in the PHP code I am working on. Whenever a record was created in the main one, the code adds in the other tables, in a field of the same name, the same value. This same value is not PK or FK. It is defined as 'KEY' in the other tables. I am working on a code that has been ready for years. In case, I would then have to change this 'KEY' field to FK and make the relationship normally with Ner Join?

  • Just to be clear is this: I have a table of orders, it’s the main one. These orders can come from 10 different places. Every time something wrong happens with this request, it is created in another table, a record with the number of this request (the one of the request is PK in the main table) to indicate that something is wrong. She keeps orders with problems, so you can’t be FK. Not everyone goes there, and they’re unpredictable. There are 10 different tables, each representing a place of origin of the application, which are filled.

  • So... Don’t have a column that tells you where it comes from? It would be nice to post a real example, it makes it easier to understand to help you...

Show 3 more comments

1 answer

0

Table requests (tb_requests):

id_pedido (PK) | person_id | descrição   | preço
------------------------------------
1              | 1         | iphone 5    | 100
2              | 1         | iphone 6    | 200
3              | 1         | samsung Tv  | 300
4              | 2         | samsung APT | 110
5              | 3         | Phillips TV | 250
6              | 3         | Phillips ph | 260

Table Apple_missing:

id (PK) | id_pedido | description | data
------------------------------------
1       | 1         | Perda       | 24/07/2017
2       | 2         | Roubo       | 08/07/2015

Table Samsung_lost:

id (PK) | id_pedido | description | data
------------------------------------
3       | 5         | Indefinido  | 16/06/2014

Table Phillips_missing:

id (PK) | id_pedido | description | data
------------------------------------
N       | N         | N           | N

That’s basically it. The main order table. And the others are filled when the order has some delivery problem. Every time something happens the php code inserts the information in the other tables, of its respective mark. This only occurs when a problem is reported by the courier, and the responsible employee enters the system with this data. The table is already created for years and has more than 200 thousand records.

So what am I doing: On another screen, I need to display the records that had problems. I need to compare the 'id_request' of the main table with those of the lost tables. Whenever the id_request is found in any of the lost tables, it goes to the list and is displayed.

The way I’m trying to do it, it’s not working:

SELECT id_pedido
FROM tb_pedidos as p
INNER JOIN Apple_Extravio ae ON (p.id_pedido = ae.id_pedido)
INNER JOIN Samsung_Extravio se ON (p.id_pedido = se.id_pedido)
INNER JOIN Phillips_Extravio ON (p.id_pedido = pe.id_pedido)

This shows me 0 results.

Browser other questions tagged php mysql query pdo crud

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