0

This is my sql query:

SELECT DISTINCT 
   k.*, m.title as city, w.title as state 
FROM 
   konta__kategoria kat, konta k 
INNER JOIN 
   miejscowosci as m ON k.miejscowosc = m.id 
INNER JOIN 
   wojewodztwa as w ON k.wojewodztwo = w.id 
LEFT JOIN 
   kategorie as cat ON cat.id = kat.kategoria 
WHERE 
   kat.kategoria IN (1610,1609,1608,1607,1606,1605,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,6) 
   AND kat.parent_id = k.id 
   AND k.dsc LIKE "%%" 
   AND deleted = 0 
ORDER BY 
   k.nazwa

I'm getting error:

#1054 - Unknown column 'kat.kategoria' in 'on clause'

It's working when I remove

LEFT JOIN kategorie as cat ON cat.id = kat.kategoria

But I know that kat.kategoria exist (it's working in where), also there exist table cat.id, so what is the problem?

Improve this question
5
  • 5
    Why?! You're using pre and post ANSI 92 JOIN syntax in the exact same query. Why would you do that? Commented Jan 2, 2014 at 21:20
  • Can you make a sqlfiddle.com page with the schema and sample data? Commented Jan 2, 2014 at 21:21
  • do you get anything from SELECT DISTINCT kat.kategoria FROM konta__kategoria kat ? Commented Jan 2, 2014 at 21:21
  • Zane-> so how to fix it> Paul H -> yes Commented Jan 2, 2014 at 21:24
  • Besides the error (which has been solved by the 2 answers), why are you joining kategorie as cat? It seems you don't use that table at all. Commented Jan 2, 2014 at 21:34

2 Answers 2

Reset to default
5

So this is how that query would be written with the current JOIN syntax put forth by ANSI 92. Here is a Link to a brilliant article as to why you should not use the old method for doing this.

 SELECT DISTINCT 
           k.*, m.title as city, w.title as state 
        FROM 
           konta__kategoria kat
        INNER JOIN konta k 
            ON kat.parent_id = k.id 
        INNER JOIN miejscowosci as m 
            ON k.miejscowosc = m.id 
        INNER JOIN  wojewodztwa as w 
            ON k.wojewodztwo = w.id 
        LEFT JOIN  kategorie as cat 
            ON cat.id = kat.kategoria 
        WHERE 
           kat.kategoria IN (1610,1609,1608,1607,1606,1605,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,6) 
           AND k.dsc LIKE "%%" 
           AND deleted = 0 
        ORDER BY 
           k.nazwa
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

I've copied your answer and it also works, I don't know why (I've tryed inner join), maybe I made a syntax error, thank you
One other question @user2606353 why use and k.dsc like '%%'? Those are just two wild cards with no filter at all.
Yes, k.dsc LIKE '%%' is equivalent to k.dsc IS NOT NULL - and if k.dsc is not nullable, you can remove the condition.
3

This is because you are using a pre-ansi join. Here is what's going on: the list of JOINs are "connected" with the last table from the list, namely, the konta k. The konta__kategoria kat part of the non-ANSI join is not in scope in any of the ANSI joins, triggering the error that you see.

You can fix it by using ANSI joins everywhere, like this:

SELECT DISTINCT 
   k.*, m.title as city, w.title as state 
FROM 
   konta__kategoria kat
CROSS JOIN konta k 
INNER JOIN 
   miejscowosci as m ON k.miejscowosc = m.id 
INNER JOIN 
   wojewodztwa as w ON k.wojewodztwo = w.id 
LEFT JOIN 
   kategorie as cat ON cat.id = kat.kategoria 
WHERE 
   kat.kategoria IN (1610,1609,1608,1607,1606,1605,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,6) 
   AND kat.parent_id = k.id 
   AND k.dsc LIKE "%%" 
   AND deleted = 0 
ORDER BY 
   k.nazwa

Now both k and kat aliases are in scope for all the joins, so your query should work.

UPDATE : Assuming that id and parent_id are non-nullable, you do not need a cross join:

SELECT DISTINCT 
   k.*, m.title as city, w.title as state 
FROM 
   konta__kategoria kat
INNER JOIN
    konta k ON kat.parent_id = k.id
INNER JOIN 
   miejscowosci as m ON k.miejscowosc = m.id 
INNER JOIN 
   wojewodztwa as w ON k.wojewodztwo = w.id 
LEFT JOIN 
   kategorie as cat ON cat.id = kat.kategoria 
WHERE 
   kat.kategoria IN (1610,1609,1608,1607,1606,1605,1611,1612,1613,1614,1615,1616,1617,1618,1619,1620,1621,1622,1623,1624,1625,1626,1627,1628,1629,1630,1631,1632,1633,1634,1635,1636,1637,1638,1639,1640,1641,1642,1643,1644,1645,1646,1647,1648,1649,1650,1651,1652,1653,1654,1655,1656,1657,1658,1659,1660,1661,1662,1663,1664,1665,1666,1667,1668,1669,1670,1671,1672,1673,6) 
   AND k.dsc LIKE "%%" 
   AND deleted = 0 
ORDER BY 
   k.nazwa
Improve this answer

5 Comments

I don't think you need the CROSS JOIN since the WHERE clause has the join condition kat.parent_id = k.id. Just make it an INNER JOIN
yeah ... try it with INNER JOIN ON kat.parent_id = k.id (and remove that from the WHERE clause) instead of CROSS JOIN
@user2606353 then you were not doing it correctly... Did you have the syntax right.
@user2606353 how did you try it? you'll need to change the syntax according to how i suggested it
@Zane I've copied your answer and it also works, I don't know why (I've tryed inner join), maybe I made a syntax error, thank you

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.