INSERT INTO using multiple tables as sources

[proggR]

I'm working on an update to a database that will allow for a many-to-many relationship between entities.

 

I've added the associative table and have an insert statement that works on MY copy of the application but only because I only have 1 organization so I can hard code that value for the insert. The problem is that the application is currently in use and would usually have more than one organization so this won't work.

 

Essentially what I want is to be able to go:

foreach organization

  get org_id

      foreach eventtype

        get type_id

        insert into associative table values(type_id,org_id)

 

 

Its easy enough to write a PHP script to do this but where I work its common practice to have sql updates performed using only sql. Is there anyway to do this with only MySQL?

 

The insert I currently have that works when there is only one organization is:

INSERT INTO eventtype_organisation SELECT eventtype_id, 1 FROM events_lu_eventtypes;

 

I want to do something like

INSERT INTO eventtype_organisation SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o;

 

Also, I know organization is spelled wrong. That's not my doing and it would be more trouble than its worth to update all instances of it.

 

Thanks in advance.

[fugix]

read this

[mikosiko]

you need at least a JOIN here

SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o

[proggR]

read this

That's kind of the opposite of what I want to do. He wants to insert into two tables values that he already has in one query. I want to insert into one table values from two tables (just the ids since the table is an associative table used to represent a relationship).

 

I actually figured it out. It is possible if you join the tables on 1=1

 

INSERT INTO eventtype_organisation SELECT a.eventtype_id, b.organisation_id FROM events_lu_eventtypes AS a JOIN organisations AS b ON 1=1;

 

 

[proggR]

you need at least a JOIN here

SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o

 

I realized that and decided to try it joining them on 1=1 which ended up working.

Thanks for the reply. If it hadn't clicked it would have after I read yours lol.

[mikosiko]

I actually figured it out. It is possible if you join the tables on 1=1

 

which is totally incorrect  and will give you wrong results.  you should read a little about how JOIN's in general work.

 

[proggR]

I actually figured it out. It is possible if you join the tables on 1=1

 

which is totally incorrect  and will give you wrong results.  you should read a little about how JOIN's in general work.

 

 

I understand how they work and use them on a daily basis. I know usually you would join on like values but in this case there is no existing relationship between the entities, that's what I'm trying to make. Joining on 1=1 tells it to take every value from table one and pair it with every value from table two. It is correct and does work. I've already tested it and get the results I expect.

 

 

*edit* removed a needless remark

 

 

[mikosiko]

if try to help you means "be a dick".... then you really are having a bad day... if as you say .. you use JOINs  everyday then explain which is the difference between this sentences:

 

//your original one

SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o

 

// your second one
SELECT a.eventtype_id, b.organisation_id FROM events_lu_eventtypes AS a JOIN organisations AS b ON 1=1

 

// and this one
SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o
WHERE 1 = 1

 

if you "work everyday with JOIN's" as you said you probably should know as the three of them are EXACTLY THE SAME... so whom is the "dick'?

 

post/help closed in my side... hope your day improve.

[proggR]

if try to help you means "be a dick".... then you really are having a bad day... if as you say .. you use JOINs  everyday then explain which is the difference between this sentences:

 

//your original one

SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o

 

// your second one
SELECT a.eventtype_id, b.organisation_id FROM events_lu_eventtypes AS a JOIN organisations AS b ON 1=1

 

// and this one
SELECT e.eventtype_id, o.organisation_id FROM events_lu_eventtypes as e, organisations as o
WHERE 1 = 1

 

if you "work everyday with JOIN's" as you said you probably should know as the three of them are EXACTLY THE SAME... so whom is the "dick'?

 

post/help closed in my side... hope your day improve.

 

The difference is that when skimming code seing 1=1 very easily helps you tell that it is a cross join. Yes the others give the same results, the ON 1=1 just improves legibility.

And if you know they produce the same results, what was the purpose of:

which is totally incorrect  and will give you wrong results.  you should read a little about how JOIN's in general work.

 

Also, for future reference don't assume the other poster doesn't know anything. It comes off as rude.

[fugix]