#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0

    Unhappy Count rows by condition WHERE


    SELECT COUNT( persons ) AS new, instans.id_inst, instans.time, instans.email, instans.logo, instans.adres, instans.phone,
    instans.name AS `name_instan` ,
    photos_instans.image, instans.name
    FROM photos_instans
    LEFT JOIN instans ON instans.id_inst = photos_instans.id_inst
    LEFT JOIN rezervation ON rezervation.id_inst = instans.id_inst
    WHERE rezervation.status =0
    AND instans.type =3
    GROUP BY instans.name
    ORDER BY instans.id_inst DESC
    LIMIT 0 , 30

    I want to count rows at table Rezervation WHERE rezervation.status =0, but on this condition if does not request rezervation.status =0, then all query returns nothing. If remove rezervation.status =0 then all work correctly, but does not work condition (it is must be)
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    31
    Rep Power
    2
    Originally Posted by oliwin
    SELECT COUNT( persons ) AS new, instans.id_inst, instans.time, instans.email, instans.logo, instans.adres, instans.phone,
    instans.name AS `name_instan` ,
    photos_instans.image, instans.name
    FROM photos_instans
    LEFT JOIN instans ON instans.id_inst = photos_instans.id_inst
    LEFT JOIN rezervation ON rezervation.id_inst = instans.id_inst
    WHERE rezervation.status =0
    AND instans.type =3
    GROUP BY instans.name
    ORDER BY instans.id_inst DESC
    LIMIT 0 , 30

    I want to count rows at table Rezervation WHERE rezervation.status =0, but on this condition if does not request rezervation.status =0, then all query returns nothing. If remove rezervation.status =0 then all work correctly, but does not work condition (it is must be)



    you could try something like...
    create procedure dbo.SP_THis_testProc
    (
    @request as string
    )

    SELECT COUNT( persons ) AS new, instans.id_inst, instans.time, instans.email, instans.logo, instans.adres, instans.phone,
    instans.name AS `name_instan` ,
    photos_instans.image, instans.name
    FROM photos_instans
    LEFT JOIN instans ON instans.id_inst = photos_instans.id_inst
    LEFT JOIN rezervation ON rezervation.id_inst = instans.id_inst
    WHERE rezervation.status =@request
    AND instans.type =3
    GROUP BY instans.name
    ORDER BY instans.id_inst DESC
    LIMIT 0 , 30


    if i understand your question correctly, you are asking how to make your query adjust as requested?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0
    I work at Phpmyadmin, how can i apply your procedure with variable @request?
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0
    I wanted to say that my SQL query must
    execute the first LEFT JOIN despite on the next JOIN'S, because condition "ON" for LEFT JOIN is correctrly and there are notes at table for this cond. But query returns empty rows.
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,148
    Rep Power
    4274
    change this --
    Code:
    FROM photos_instans 
    LEFT JOIN instans ON instans.id_inst = photos_instans.id_inst 
    LEFT JOIN rezervation ON rezervation.id_inst = instans.id_inst 
    WHERE rezervation.status =0 
    AND instans.type =3
    to this --
    Code:
      FROM photos_instans 
    LEFT OUTER
      JOIN instans 
        ON instans.id_inst = photos_instans.id_inst 
       AND instans.type =3
    LEFT OUTER
      JOIN rezervation 
        ON rezervation.id_inst = instans.id_inst 
       AND rezervation.status = 0
    see the difference?

    also, i've moved the thread to the mysql forum for you, because ms sql server doesn't support LIMIT
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0
    Okay, Thank you. Now i have last type of query:

    Code:
    SELECT DISTINCT(instans.id_inst), count(persons) as new, instans.time, instans.email,instans.logo, instans.adres,instans.phone, instans.name as `name_instan`, photos_instans.image, instans.name FROM photos_instans LEFT OUTER JOIN instans ON instans.id_inst = photos_instans.id_inst AND instans.type= 2 LEFT OUTER JOIN rezervation ON rezervation.id_inst = instans.id_inst AND rezervation.status=0 GROUP BY instans.name ORDER BY instans.id_inst DESC
    It returns null rows from table instans (: Problem was not resolved
  12. #7
  13. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,148
    Rep Power
    4274
    Originally Posted by oliwin
    It returns null rows from table instans (:
    your query looks legit

    so it must be the data that's the problem

    alternatively, your query could be making assumtions that are not correct for your data

    p.s. if you ever post again, please make sure your sql is formatted

    the entire query on one line with horizontal scroll is unacceptable

    Code:
    SELECT DISTINCT(instans.id_inst)
         , count(persons) as new
         , instans.time
         , instans.email
         , instans.logo
         , instans.adres
         , instans.phone
         , instans.name as `name_instan`
         , photos_instans.image
         , instans.name 
      FROM photos_instans 
    LEFT OUTER 
      JOIN instans 
        ON instans.id_inst = photos_instans.id_inst 
       AND instans.type= 2 
    LEFT OUTER 
      JOIN rezervation 
        ON rezervation.id_inst = instans.id_inst 
       AND rezervation.status=0 
    GROUP 
        BY instans.name 
    ORDER 
        BY instans.id_inst DESC
    p.s. DISTINCT is not a function, remove it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  14. #8
  15. #9
  16. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2013
    Posts
    14
    Rep Power
    0

    Unknown column 'jobs.name' in 'where clause'


    SELECT `jobs`.`id_job`, count(*) as new, `instans`.`name`, `instans_images`.`image`, `jobs`.`name`, `jobs`.`id_inst` FROM (`jobs`)

    LEFT OUTER JOIN
    `instans` ON `jobs`.`id_inst` = `instans`.`id_inst`

    LEFT OUTER JOIN `instans_images`
    ON `instans`.`id_inst` = `instans_images`.`id_inst`

    LEFT OUTER JOIN `cvs`
    ON `jobs`.`id_job` = `cvs`.`id_job` AND `cvs`.`statusf` = 0 WHERE `jobs.name` LIKE '%\%D0\%B7%'
    GROUP BY `instans`.`name` ORDER BY `name` ASC LIMIT 1, 20

    Why it swears on line name field `jobs.name` ?
  17. #10
  18. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    It's
    Code:
    `jobs`.`name`
    not
    Code:
    `jobs.name`
    "jobs" and "name" are two separate identifiers. What you're doing is create a single identifier which happens to include a dot: "jobs.name". And there is no column with that name.

    Things like this is why those backticks are not always a good idea. They accept any nonsense as a valid identifier, so you can easily make mistakes or use names you shouldn't use.

IMN logo majestic logo threadwatch logo seochat tools logo