menu

Questions & Answers

View only showing some records

I have created a view and confirmed that there is no limit when showing the table. However it is only showing 32 records instead of showing all. This means it is restricting what I export from the view.

This is my SQL text for the view I have. Any hel; is appreciated in either getting all records showing, OR in creating another table to show all fields from other tables and allows me to link

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `EasyBadge-UserCivi`@`%` 
    SQL SECURITY DEFINER
VIEW `EasyBadge` AS
    SELECT 
        `C`.`id` AS `AD_ID`,
        `A`.`id` AS `Civi_ID`,
        `A`.`external_identifier` AS `EXTERNAL_IDENTIFIER`,
        `A`.`first_name` AS `FIRST_NAME`,
        `A`.`last_name` AS `LAST_NAME`,
        `A`.`birth_date` AS `BIRTH_DATE`,
        `B`.`state_province_id` AS `STATE_PROVINCE_ID`,
        `C`.`assistance_dog_name_8` AS `ASSISTANCE_DOG_NAME_8`,
        `C`.`date_of_birth_9` AS `DATE_OF_BIRTH_9`,
        `C`.`colour_11` AS `COLOUR_11`,
        `C`.`breed_10` AS `BREED_10`,
        `C`.`microchip_number_15` AS `MICROCHIP_NUMBER_15`,
        `D`.`invisible_disabilities_142` AS `INVISIBLE_DISABILITIES_142`,
        `D`.`non_verbal_148` AS `NON_VERBAL_148`,
        `D`.`hearing_impairment_143` AS `HEARING_IMPAIRMENT_143`,
        `D`.`sight_loss_144` AS `SIGHT_LOSS_144`,
        `D`.`bladder_bowel_weakness_illness_145` AS `BLADDER_BOWEL_WEAKNESS_ILLNESS`,
        `D`.`mobility_146` AS `MOBILITY_146`
    FROM
        (((`civicrm_contact` `A`
        JOIN `civicrm_address` `B`)
        JOIN `civicrm_value_assistance_do_2` `C`)
        JOIN `civicrm_value_medical_condi_3` `D`)
    WHERE
        `A`.`id` = `B`.`id`
            AND `A`.`id` = `C`.`entity_id`
            AND `A`.`id` = `D`.`entity_id`

I have tried making sure there is no limit on the showing of records, and checked my SQL text to ensure nothing would stop all fields being shown.

Ideally i would like to create a table to get the same information from other tables, meaning I can edit the table to update the records if I need to and it allows me to link the table to other applications.

Comments:
2023-01-17 00:48:40
Based on code we can only determine if there is some kind of a syntax error in the code. We cannot see your data. If an sql query does not show all expected rows, then it is usually caused by the inner join, where, and having clauses. Since we do not know anything about your data, we cannot comment on this side.
2023-01-17 00:48:40
there is no limit when showing the table. However it is only showing 32 records instead of showing all This can be an effect of the client software which has some setting in its options for to limit the amount of retrieved rows.
2023-01-17 00:48:40
That's a lot of joins with no ON conditions. (where maybe on definately)
2023-01-17 00:48:40
The trouble I have is that this is the first thing i have ever written in SQL, I would be stupid if i didnt mention, I don't know what I am doing and i am learning as I go along. I have never had to use SQL before now. I run the IT for a charity and I am the only person that knows anything about IT so it is a massive learning curve for me. Thats also the reason i have mentioned what my ideal solution would be, but I just dont know how to implement it. The software I am using to query the database for this purpose is Workbench
Answers(0) :