View original (French)

SQL query to display data

Solved
okbabb Posted messages 59 Status Membre -  
okbabb Posted messages 59 Status Membre -
Hello,

7 réponses

Réponse 1 / 7
yg_be Posted messages 23437 Registration date   Status Contributeur Last intervention   Ambassadeur 1 587
 
Hello,
https://www.commentcamarche.net/infos/25899-demander-de-l-aide-pour-vos-exercices-sur-ccm/
0
okbabb Posted messages 59 Status Membre
 
It's not an exercise, it's a problem that I want solved.
0
Réponse 2 / 7
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830
 
Hello

Take an interest in joins.

--
.
Best regards,
Jordane
0
okbabb Posted messages 59 Status Membre
 
This query does not work:

SELECT * FROM persons, structures WHERE workplace = structure_id AND assignment = structure_id

How can I solve the problem please?
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > okbabb Posted messages 59 Status Membre
 
Look, for example, at how the left join works.
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
You will also need to pay attention to the aliases... because you have to join your structures table twice (once for each link with your persons table).
0
okbabb Posted messages 59 Status Membre > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
Thank you, but sorry, I don't quite understand the problem. Can you please correct the request?
0
Réponse 3 / 7
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830
 
First, you need to tell us which DBMS you are using...
MySQL? Access? Oracle? SqlServer? PostgreSQL? Other ??

Then, I told you to look into LEFT JOINs and ALIASES... have you done that?
0
okbabb Posted messages 59 Status Membre
 
MySQL DBMS and project on PHP,
I did some research on joins but it is more complicated than my simple task.
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > okbabb Posted messages 59 Status Membre
 
Complicated ??
Yet, it seems pretty simple to understand to me.. https://sql.sh/cours/jointures/left-join
And a course on joins.. and aliases: https://joaomarcuraa.medium.com/sql-joins-and-aliases-1965fd2423ca
0
okbabb Posted messages 59 Status Membre > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
Thank you, I read all of that, but I can't find my example.
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > okbabb Posted messages 59 Status Membre
 
You won't find an example that perfectly matches your needs... it's up to you to understand how it works and apply it...

Start with an initial join between your two tables, see what it gives you...
then, add a second join between your two tables... and there you go... job done...
In short 
 SELECT * FROM table1 T1 LEFT JOIN table2 T2 ON T2.xx = T1.zz LEFT JOIN table2 T3 on T3.ww = T1.yy
0
Réponse 4 / 7
okbabb Posted messages 59 Status Membre
 
I did what you told me and it doesn't work
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830
 
1 - Do not post images... but the code directly on the forum using the code tags!
2 - An SQL query should be tested FIRST directly in the database (using phpMyAdmin for example)
3 - You will need to add ALIASES as well in the "SELECT" part of your query to identify the fields (since you have the same ones multiple times..)

NB: Explanations regarding the use of code tags to read completely before returning:
https://codes-sources.commentcamarche.net/faq/11288-les-balises-de-code

.
0
Réponse 5 / 7
okbabb Posted messages 59 Status Membre
 
Before I add the aliases, I tested this code and it shows me a query error.
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830
 
And what is the error exactly? (because... giving us this information would greatly help in resolving it..)
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
Isn't it a question of ambiguous ID in the order by ??
It should be prefixed by the alias of the table that concerns this field .. T1.id for example.
0
Réponse 6 / 7
okbabb Posted messages 59 Status Membre
 
No, I removed the order by and the problem still exists
The error is:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in D:\Serveur\xampp\htdocs\projet\tableau.php on line 103

And I don't know how to use aliases so that the output displays the names of the structures (lieu_travail and affectation) of the targeted columns.
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830
 
Oh, but you're talking about a PHP error message... who cares about that!
First of all, because your question concerns the construction of an SQL query, which is posted in the Database forum... and moreover, I told you to test your queries DIRECTLY in your database before wanting to use them in code.

Not to mention that your PHP code is outdated... but I'll talk to you about that later when you've finally made the query for which you asked us for help...
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
For the aliases .. you clearly still haven't taken the time to read the links I gave you.
Everything is explained there ...
For example 
 SELECT T1.id as ID1, T2.id as ID2 ...
0
okbabb Posted messages 59 Status Membre > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
error too
0
okbabb Posted messages 59 Status Membre > jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention  
 
The thing is very simple, but I don't know how to do it. Here is another example,

a table "users" that contains 4 columns: id, name, country_of_origin, country_of_residence

id name country_of_origin country_of_residence
1 michel 1 2
2 celine 3 4


and another table "countries" that contains 2 columns: country_id, country_name

country_id country_name
1 france
2 belgium
3 spain
4 switzerland

I want a final table like this: (output)

name country_of_origin country_of_residence
michel france belgium
celine spain switzerland


what is the SQL query? Thank you in advance.
0
jordane45 Posted messages 30426 Registration date   Status Modérateur Last intervention   4 830 > okbabb Posted messages 59 Status Membre
 
You have an error because you did not use the table alias in the SELECT part... 
 T1.location....

And not 
 persons.location....


Unless you did not write the column name correctly? (but not knowing the exact structure of your database, it's hard for me to guess from a distance)
0
Réponse 7 / 7
okbabb Posted messages 59 Status Membre
 
Thank you very much my friend, it works great :D
0