'Unknown column event_id in field list

I'm getting the error:

Unknown column event_id in field list

yet I have this column in both tables

SELECT 
event_id.events,
tournament.events, 
team1.events,
team2.events,event_date.events,
venue.events, picks.pick,tournament.picks
FROM  events 
INNER JOIN picks 
ON  `event_id`.events = `event_id`.picks
 WHERE tournament = 'SUPER15'

I have checked and tweaked and tried but I just can't figure out what is wrong...

TABLE EVENTS

enter image description here

TABLE PICKS

enter image description here

mysqlsql


Solution 1:[1]

First should be table name or alias than column name as below

 SELECT
    events.event_id,
    events.tournament, 
    events.team1, 
    events.team2,
    events.event_date,
    events.venue, 
    picks.pick,
    picks.tournament
FROM  events 
    INNER JOIN picks ON  
           events.`event_id` = picks.`event_id`
    WHERE 
        events.tournament = 'SUPER15'

You have to add column name for condition WHERE tournament = 'SUPER15' too. I changed it to WHERE events.tournament = 'SUPER15'. I also recommend you to use aliases as below

  SELECT 
       E.event_id,
       E.tournament, 
       E.team1, 
       E.team2,
       E.event_date,
       E.venue, 
       P.pick,
       P.tournament
 FROM  events E
    INNER JOIN picks P ON  
              E.`event_id` = P.`event_id`
    WHERE 
        E.tournament = 'SUPER15'

It's more readable, isn't?

Solution 2:[2]

You're specifying your selections in reverse order. It should be table.column:

SELECT events.event_id,events.tournament, events.team, events.team2,
       events.event_date, events.venue, pick.picks,picks.tournament
FROM  events 
INNER JOIN picks ON  `event_id`.events = `event_id`.picks
WHERE tournament = 'SUPER15'

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Sachu
Solution 2 Ori Lentz

Related Questions