SQLite Forum

request on 5 tables

request on 5 tables

(1) By anonymous on 2020-09-21 12:07:11 [link] [source]

hi i m looking for a request about 5 tables. (table1. 2 3 4 5) All the tables have the same title on the columns. hours/ID/place1/place2 I must do a Inner join about the 5 tables but i have a error when i execute my request

can you help me? gratefully

(2) By Richard Hipp (drh) on 2020-09-21 13:11:47 in reply to 1 [link] [source]

No. We cannot help you because you have not given us any useful information about your problem.

  1. What is your schema?
  2. What is the specific query that you are trying to run?
  3. What is the error message?

(3) By anonymous on 2020-09-21 13:19:56 in reply to 2 [link] [source]

hi i use sqlite i try to know if hours/ID/place1 are on the 5 tables.

I have a godd result for 2 tables but not for 5. SELECT hours, ID, place1, FROM table2 INNER JOIN table 1 ON table2.id = table1.id

I m a beginner in sqlite and i use dbbrowser for sqlite

(4) By Gunter Hick (gunter_hick) on 2020-09-21 14:26:10 in reply to 1 [link] [source]

Having more than one table with identical fields and/or several fields with the same base name and serial numbers usually means that you need to go back to the database design phase.

Stating what exactly you are trying to accomplish, what your schema is, what statement exactly you are running and the verbatim error message are required to understand what the problem could be.

(5) By anonymous on 2020-09-21 15:08:16 in reply to 1 [link] [source]

i have 5 tables on each i have columns named hours1,ID1,place1, place2 hours2,ID2,place3,place4 hours3....... hours4....... hours5.......

i want to use innerjoin to know if i found ID of consumers on different place.

(6) By Gunter Hick (gunter_hick) on 2020-09-21 15:28:24 in reply to 5 [link] [source]

Why do you have 5 tables that look the same? What is the difference between the tables? Why do you have place1 and place2 in the same table? Why are you numbering fields that are unique to their table?

And still missing the answers to: What are you trying to do? What is the text of the query? What is the text of the error message?

(7) By anonymous on 2020-09-21 17:30:12 in reply to 6 [link] [source]

it s not exatly the same. We have 5 spots each customer have an ID with hisfidelity card. And i want to know which Id go to the 5 spots. the joint inner is not the best solution?

(8) By Warren Young (wyoung) on 2020-09-21 17:37:46 in reply to 7 [link] [source]

You're still not answering key questions. You can repeat your initial question as many times as you like, but until we get those answers, we can't help you.

(9) By Gunter Hick (gunter_hick) on 2020-09-22 06:15:24 in reply to 7 [link] [source]

I am just guessing that you are operating a retail business with several outlets and are issuing customer cards to identify repeat customers. You appear to be storing visits to each outlet in separate tables, one for each outlet. Your schema is definitely broken and joining 5 tables that should be only 1 table is not going to help.

(10) By anonymous on 2020-09-22 08:39:24 in reply to 9 [link] [source]

To me it looks like there might be a language issue here and repeating the questions will not redress the issues.

It is possible that

  • there are 5 tables one each for 5 locations which are not networked such that there are 5 databases.
  • the OP is collating the information from each location and re-organising it centrally in one table in yet another database.

If words are difficult, some clarity using examples may be enough to understand

  • whether the 5 tables need to be linked
  • whether the 5 tables can be processed sequentially
  • what happens when any client visits multiple locations/stores

(11) By anonymous on 2020-09-22 11:48:47 in reply to 10 [link] [source]

it s correct each location have a DB it snot networked I want to re organising this to have one table. In this tables on each location wa have old customers, new customers, and we have customers who go to each location. Wa want to find them for givng a bonus. It s difficult to explain but that s it.

for 2 tables it s not too difficult to have results SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.fk_id

i take results and begin a new operation with the table 3. After i take results and do new operation with the table 4 and i take results to do innerjoin with the table 5.

I m looking for a request to do all of this only with one request. Just because we have any new fidelity card each day and i want to be quick.

The 5 tables dont need to be linked, each shop have one on a secure computer not connected on internet. each table is independant.

what happens when any client visits multiple locations/stores? we have the id of the card one the DB when the fidelity card is read by a lasercode.

(12) By Richard Damon (RichardDamon) on 2020-09-22 13:58:38 in reply to 11 [source]

It is possible to stack JOINs into one SELECT statement. You do realize that by doing INNER JOINs you are asking only for recorda that exist in ALL the tables, so anyone that only exists in 4 or less out of the 5 tables won't get returned?

(13) By anonymous on 2020-09-22 14:27:45 in reply to 12 [link] [source]

i know they must exist in all the tables. to know how many times the ID is in the 5 tables i can use

SELECT COUNT() AS nbr_doublon, champ1, champ2, champ3 FROM table GROUP BY champ1, champ2, champ3 HAVING COUNT() > 1

so but i lose a lot of data. i prefer use inner join but it s difficult for me:-(

(14) By Gunter Hick (gunter_hick) on 2020-09-22 16:12:59 in reply to 11 [link] [source]

You have two distinct problems.

Problem one: Merging the content of the 5 Stores' data.
Problem two: Finding customers that visit more than one store.

For problem one:

You need a schema that is common to all the stores and the central location. And you need a method to record the changes made at each store to replicate them to central and all other stores (for customer identification).

In the table recording the visits, add a column storeID and assign astoreID to each store. That way you can copy the changes from all the stores into a single table.

For problem two:

SELECT customerID, count() as stores_visited from (SELECT DISTINCT customerID, storeID FROM visits) GROUP BY customerID HAVING stores_visited = 5;

(15) By anonymous on 2020-09-23 06:53:16 in reply to 14 [link] [source]

thx a lot. it s a method i dont think about it.

I have another one, tell me if it s wrong. i have data un CSV, i can merge 4 tables in one and use inner join with the 5th table no?

(16) By Gunter Hick (gunter_hick) on 2020-09-23 07:53:06 in reply to 15 [link] [source]


Merging two CSV files destroys the information about which store they came from. You need that information stored in each record, not in a table name.

Table names are not data. Stop pretending that they are.

(17) By anonymous on 2020-09-24 06:35:59 in reply to 15 [link] [source]

Where is inner join coming from? That is, why is it relevant?

You have 5 lots of data in 5 tables coming from 5 stores - there is NO link among them.

  1. attach each of your databases with an alias e.g. attach database 'store1.db' as store1; ... attach database 'store5.db' as store5;

  2. put all your data in one table

create temp table allData as

select 'store1' as source,hours,ID,place1,place2 from store1.yourTable

union all

select 'store2' as source,hours,ID,place1,place2 from store2.yourTable

/* repeat for every table */

union all

select 'store5' as source hours,ID,place1,place2 from store5.yourTable

  • now you can use the allData table to group by ID or by ID and source and you will use this to update the table that you hold centrally for your 'bonus' logic.

Your business logic is flawed (i.e. has lots of aspects that are unclear). For instance, how will you avoid double counting? Would you empty each store's table as soon as you've taken a copy? If you do not, you will count this weeks records twice when you repeat your consolidation next week etc. etc. Or you need a marker column to indicate which records are subject to consolidation.