View original (French)

SQL query to display data

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

7 answers

  2.
    jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831
     
    Hello

    Take an interest in joins.

    --
    .
    Best regards,
    Jordane
    0
    1. okbabb Posted messages 59 Status Member
       
      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
      1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831 > okbabb Posted messages 59 Status Member
         
        Look, for example, at how the left join works.
        0
      2. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831 > jordane45 Posted messages 30427 Registration date   Status Moderator 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
      3. okbabb Posted messages 59 Status Member > jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention  
         
        Thank you, but sorry, I don't quite understand the problem. Can you please correct the request?
        0
  3. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831
     
    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
    1. okbabb Posted messages 59 Status Member
       
      MySQL DBMS and project on PHP,
      I did some research on joins but it is more complicated than my simple task.
      0
      2. okbabb Posted messages 59 Status Member > jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention  
         
        Thank you, I read all of that, but I can't find my example.
        0
      3. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831 > okbabb Posted messages 59 Status Member
         
        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
  4. okbabb Posted messages 59 Status Member
     
    I did what you told me and it doesn't work
    0
    1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831
       
      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
  6. okbabb Posted messages 59 Status Member
     
    Before I add the aliases, I tested this code and it shows me a query error.
    0
    1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831
       
      And what is the error exactly? (because... giving us this information would greatly help in resolving it..)
      0
      1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831 > jordane45 Posted messages 30427 Registration date   Status Moderator 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
  7. okbabb Posted messages 59 Status Member
     
    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
    1. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831
       
      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
    2. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831 > jordane45 Posted messages 30427 Registration date   Status Moderator 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
    3. okbabb Posted messages 59 Status Member > jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention  
       
      error too
      0
    4. okbabb Posted messages 59 Status Member > jordane45 Posted messages 30427 Registration date   Status Moderator 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
    5. jordane45 Posted messages 30427 Registration date   Status Moderator Last intervention   4 831 > okbabb Posted messages 59 Status Member
       
      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
  8. okbabb Posted messages 59 Status Member
     
    Thank you very much my friend, it works great :D
    0