CODEWITHSUNDEEP
mysqlsyntax-error
Keith
Keith

Reputation: 73

Error in MySql query

I have three tables. I am attempting to pull data from two tables and insert into the third if a set of circumstances exists. When I run my query I am getting

"#1054 - Unknown column 'rvp_to_registrants_to_events.rvp_to_registrants_id' in 'where clause' "

Here is my query:

INSERT INTO  rvp_to_registrants_to_events (rtr.rvp_to_registrants_id, rte.events_id)



SELECT
    rtr.rvp_to_registrants_id, rte.events_id

FROM
    registrants_to_events rte,rvp_to_registrants rtr


WHERE (
    SELECT
        rte.registrants_id, rte.events_id, 
        rtr.rvp_to_registrants_id, rtr.registrants_id


    FROM
        registrants_to_events rte,rvp_to_registrants rtr


    WHERE
        rte.registrants_id = rtr.registrants_id

        AND (rtr.rvp_to_registrants_id != rvp_to_registrants_to_events.rvp_to_registrants_id

        AND rte.events_id != rvp_to_registrants_to_events.events_id)

    )

I can't figure out why I am getting this error. I am sure it is something bonehead that I am overlooking, but any help appreciated.

Thanks!

Per Michael's input I have updated my code as follows. Returning zero results now, so am working on that:

   INSERT INTO  rvp_to_registrants_to_events (rvp_to_registrants_id, events_id)

SELECT
    rtr.rvp_to_registrants_id, rte.events_id

FROM
  registrants_to_events rte 
  JOIN rvp_to_registrants rtr ON rte.registrants_id = rtr.registrants_id
  JOIN rvp_to_registrants_to_events rtrte ON rtr.rvp_to_registrants_id = rtrte.rvp_to_registrants_id

    WHERE  

        rte.registrants_id = rtr.registrants_id
    AND 
        (rtr.rvp_to_registrants_id !=  rtrte.rvp_to_registrants_id
    AND 
        rte.events_id != rtrte.events_id)

Upvotes: 0

Views: 62

Answers (3)

Michael Berkowski
Michael Berkowski

Reputation: 270607

The error looks to be because you have referred to rvp_to_registrants_to_events in your WHERE clause, but not included it in your FROM:

FROM
    registrants_to_events rte,rvp_to_registrants rtr, rvp_to_registrants_to_events
WHERE
    rte.registrants_id = rtr.registrants_id
    AND (rtr.rvp_to_registrants_id != rvp_to_registrants_to_events.rvp_to_registrants_id
    AND rte.events_id != rvp_to_registrants_to_events.events_id)

I would recommend redefining this with a proper JOIN syntax:

FROM
  registrants_to_events rte 
  JOIN rvp_to_registrants rtr ON rte.registrants_id = rtr.registrants_id
  JOIN rvp_to_registrants_to_events ON rtr.registrants_id = rvp_to_registrants_to_events.registrants_id
  -- --------------------supply the correct ON clause relationship ^^^^^^^^^^^^^^^^^^^
WHERE
  -- actual selection criteria

You will need to supply the actual column relationship to the ON clause in place of ON rtr.registrants_id = rvp_to_registrants_to_events.registrants_id. The JOIN assumes there is a common column between rvp_to_registrants_to_events and either rte or rtr but that isn't clear to me.

Upvotes: 1

JM4
JM4

Reputation: 6788

To start, learn to use "JOIN" statements and table aliases in your code. It not only makes it easier to use but also understand. Essentially, your sql is doing that anyway but in its own interpretation.

Upvotes: 0

Nettogrof
Nettogrof

Reputation: 2136

in your from cause you have : registrants_to_events rte,rvp_to_registrants rtr

in your where cause you use the table : rvp_to_registrants_to_events

This table should be in your from cause

Upvotes: 0

Related Questions