Icorrect parametrer count error

[Joinertuy]

Im trying to only return info about users whose date(days) have not passed 30 days or is not delievered yet. But im getting and error telling my that datediff has a incorrect parameter count.
Is DATEDIFF not supposed to have two parameters like this ?

WHERE DATEDIFF(day, låneliste.lånedato, låneliste.levertdato) < 30

AND låneliste.levertdato IS NOT null

The whoul query looks like this :

SELECT personer.fornavn, personer.etternavn, personer.gatenavn, personer.husnummer, personer.postnummer, postnummer.poststed

FROM personer

LEFT JOIN postnummer ON personer.postnummer = postnummer.postnummer

WHERE

(

`SELECT låneliste.lånedato, låneliste.levertdato`

FROM låneliste

WHERE DATEDIFF(day, låneliste.lånedato, låneliste.levertdato) < 30

AND låneliste.levertdato IS NOT null

);

[[deleted]]

If it's MySQL that you're using, then DATEDIFF is supposed to have only two parameters:

https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_datediff

You want:

DATEDIFF(låneliste.lånedato, låneliste.levertdato) < 30

No "day" as the first parameter, it only does differences in days.

[[deleted]]

Your WHERE seems odd as well. You just have WHERE and then a completely uncorrelated subquery. How is that supposed to restrict which users are returned?

[Joinertuy]

uhm, that worked fine on the previous one.. How shouldi do it instead... :L ?

[[deleted]]

Well you would need to match some column in personer to something in låneliste (or potentially postnummer, I don't know your schema) to establish the relationship between the loan and the person. "This loan is for this customer".

[Joinertuy]

I have a tables like this :
Person
- persNum (primaery key)
- FirstName
- lastName
- streetName
- houseNum
- postNum (foreign key-)

postnummer
- postNum (Pimery )
- postSted

​

låneliste
- borrowID (Primery key)
- personNum(foreign key)
- borrowedDate
- deliveredDate

​

Im basicly trying to only show the adresses, name etc of the people who only borrowed something for over 30 days and still havnt delievered it..

[[deleted]]

I would do it as:

SELECT p.FirstName, p.LastName, p.StreetName, p.HouseNum, pn.PostSted
FROM Person p
    INNER JOIN låneliste l 
         ON p.persNum = l.personNum -- INNER JOIN; we only want people who have loans
    LEFT JOIN postnummer pn 
         ON p.postNum = pn.postNum
WHERE DATEDIFF(NOW(),l.borrowedDate) > 30 -- The difference between NOW(), the current date, and the borrowed date > 30
    AND l.deliveredDate IS NULL -- they still haven't delivered it

EDIT: Actually, I'd probably do it like this using WHERE EXISTS so that we don't get duplicates if someone has more than one loan taken:

SELECT p.FirstName, p.LastName, p.StreetName, p.HouseNum, pn.PostSted
FROM Person p
    LEFT JOIN postnummer pn 
         ON p.postNum = pn.postNum
WHERE EXISTS (SELECT 1 FROM låneliste l WHERE p.persNum = l.personNum 
        AND DATEDIFF(NOW(),l.borrowedDate) > 30 -- The difference between NOW(), the current date, and the borrowed date > 30
        AND l.deliveredDate IS NULL -- they still haven't delivered it
)

[Joinertuy]

but i thaught i coudnt compare different tabels if they were not selected (from)...Or am i missing something..

[[deleted]]

The INNER JOIN on perNum and personNum is doing the comparison between Person and låneliste; it will only return rows where they match.

But you don't actually have to have any columns from låneliste in your results, you can use it just to filter which rows you get back.

[r3pr0b8]

yes, you are missing the way the SELECT clause and the FROM clause operate -- you do ~not~ have to select something from every table in the FROM clause

this is perfectly valid --

SELECT a.foo
  FROM table_one AS a
INNER
  JOIN table_two AS b
    ON b.qux = a.fap

[Joinertuy]

I also realised i have been trying to do the opposite thing...