-3

This code created the error.

Error Code: 1052. Column 'ID' in where clause is ambiguous

      SELECT h1.name, h1.gender,h2.gender
        FROM hampster h1, hampster h2,partner p
        INNER JOIN partner on h1.ID = partner.ID1
        INNER JOIN children on h2.ID = children.ID1
WHERE h1.id = p.id1
  AND h2.id = p.id2
  AND h1.gender = h2.gender
  AND EXISTS (SELECT c.id1
            FROM children c
            WHERE c.id1 = p.id1
             OR  c.id1 = p.id2)
ORDER BY h1.gender, h1.name;

This is created when I add hampsters h2 into my query. My goal is to use inner join to combine two instances of the hamspter table and still use 'h1' and 'h2' to refer to them.

This is the expected output from the query

h1.name    h2.name gender
Alex        King    0
Grant       Alex    0
Jack        Grant   0
Cathy       Amy     1
Trish       Amy     1
Dharman
  • 30,962
  • 25
  • 85
  • 135
Caesar Krit
  • 204
  • 4
  • 19
  • do you want just to change to inner join? can you share your structure table or definition.. i am still can't get what you want to achieve.. – dwir182 Nov 01 '18 at 03:22
  • Again: [mcve] giving cut & paste & runnable code with clear input & query & output or error message for each problem query. Your "code format" sentence is not clear. What you want your query to do is not clear. It's not clear what code "this code here" is. What is the code where "this code created the error"? Etc. Use enough words to say what you mean & group related things together. This is a jumble. Google re using sqfiddle.com for your stack overflow posts. PS Clarify via post edits to the best presentation possible, not via comments. – philipxy Nov 01 '18 at 03:28
  • 2
    Do not mix `implicit join` and `explicit join` and partner, hampster h1 and h2 will become cross join.. i am asking again.. what you want to achieve? please show ddl table and your expected result and query if error show show the error.. – dwir182 Nov 01 '18 at 03:32
  • Possible duplicate of [MySQL unknown column in ON clause](https://stackoverflow.com/q/4065985/3404097) – philipxy Nov 01 '18 at 03:34
  • i would like my code to be able to use inner join While making two(2) instances of the hampster table. – Caesar Krit Nov 01 '18 at 03:37
  • I am not negative, I am accurately describing your post. Would you prefer not to find out that a post is a jumble & not clear by not being told? I tried to comment after edits. I am clearly assuming you can be organized & clear or I wouldn't bother to tell you. PS Clarify via edits not comments. See the comment & link re your problem of mixing comma with JOIN. Athough your problem is more that comma *means* cross join (with lower priority) & you probably want INNER JOINs *instead*. Google for "self-join" answers. PS Still no [mcve], just fragments of one. – philipxy Nov 01 '18 at 03:41
  • You just finished an edit, my last comment still applies. – philipxy Nov 01 '18 at 03:49
  • @CaesarKrit You can see this as your reference.. This is good question and clear.. And you can see people vote it.. [SO Question](https://stackoverflow.com/questions/53094862/how-should-i-write-an-sql-statement-with-conditional-multiplication) – dwir182 Nov 01 '18 at 04:07
  • I can't help you now or other people too maybe.. But if you modify and satisfied.. We would gladly help you.. :) – dwir182 Nov 01 '18 at 04:09
  • Yet again: 1. Your query is parsed `... , ... , (... join...)` & the `(... join ...)` is evaluated first so it doesn't know about the `... , ... ,`. 2. You don't want the `,`s, they mean cross join. 3. No [mcve] to cut & paste & run. Show a program that calculates what you expect it to as it goes through (sub)expressions--including that arguments passed to each function/operator meet its requirements--by saying what that is & showing that it actually does it via incremental output. Add code giving a problem. Ask about the (small) difference between the examples. (Basic debugging.) – philipxy Nov 01 '18 at 20:19
  • Does this answer your question? [Column 'id' in where clause is ambiguous](https://stackoverflow.com/questions/22632441/column-id-in-where-clause-is-ambiguous) – Dharman Apr 28 '21 at 15:22
  • @Dharman There's no undotted id, your suggested duplicate applies to the error message but not the code, the given error message does not arise from the given code, see my last comment re a code problem now, the post has been edited a lot, with varying error messages & code, but there's still no MRE. – philipxy Apr 29 '21 at 06:17

1 Answers1

-2

You missing a table alias in where condition, server didn't know which ID column your using.

Try this one :

where h1.ID = 101

Crystal Lu
  • 1