Migration of data from on CMS to another ($452) · Snippets

If you only want one row (see the n.nid = 28499 clause and all the INNER JOINs), then do it by hand.

Otherwise: what is the obstacle you actually see? All queries start at n.nid, so you can join all other tables to it

You should try that iteratively. So merge the first two queries to add the filename column:

SELECT n.type, r.title, r.body, r.teaser, r.uid, u.name, i.filename
  FROM vm_node n 
  INNER JOIN vm_users u ON u.uid = n.uid 
  INNER JOIN vm_node_revisions r ON r.vid = n.vid 
  INNER JOIN vm_node_images i ON n.nid = i.nid

I know that this is not exactly what you want, can you spot what it does and describe it?

that works well for the picture (i.filename), but not for the last select. the last select gives me 4 lines/results but I need that one concatenated to the primary select as additional rows

Now you need to understand that you can join tables multiple times. You just give them different names.

So you can do something like this:

SELECT *
  FROM vm_node n 
  INNER JOIN vm_term_node tn ON n.nid = tn.nid
  INNER JOIN vm_term_data td7 ON tn.tid = td7.tid AND td7.vid = 7
  INNER JOIN vm_term_data td8 ON tn.tid = td8.tid AND td8.vid = 8

Hmm, interesting and good to know. but maybe I have a knot in my brain because I either get non or only one:

SELECT td7.* 
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid
INNER JOIN vm_vocabulary v ON tn.vid = v.vid
INNER JOIN vm_term_data td7 ON tn.tid = td7.tid AND td7.vid IN (7,8,17,6)
WHERE n.nid = 28499

gives me four rows with tid,vid,name and they are correct for nid 28499. When I try one Inner join only on td7.vid = 7 this works - I get one row with one value. However, when I try to join the table a second time, I get not rows back, so with

SELECT td7.name AS Land
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid
INNER JOIN vm_vocabulary v ON tn.vid = v.vid
INNER JOIN vm_term_data td7 ON tn.tid = td7.tid AND td7.vid = 7
WHERE n.nid = 28499

I get one row, with

SELECT td7.name AS Land, td8.name AS Mischung
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid
INNER JOIN vm_vocabulary v ON tn.vid = v.vid
INNER JOIN vm_term_data td7 ON tn.tid = td7.tid AND td7.vid = 7
INNER JOIN vm_term_data td8 ON tn.tid = td8.tid AND td8.vid = 8
WHERE n.nid = 28499

I get 0 rows back. what am I missing there?

In the last example you changed vm_term_node to vm_term_data. I don't know your data model, so I'm just assuming what you say is correct.

INNER JOIN requires a row on both sides. So, if vm_term_data does not include a row with the appropriate values for tid and vid, the whole result is empty. You can check that that with:

SELECT td.*
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid
WHERE n.nid = 28499

If there is no result with any of the vid you want, use LEFT OUTER JOIN instead.

with

SELECT tn.*
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid
WHERE n.nid = 28499;

I get 4 rows:

tid	vid	name
19	6	rock pop
23	7	Deutschland
28	8	mixed
1769	17	Professional

which is what I expected. (select td.* didn't work, td was't defined).

even a

SELECT tn.*
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid AND tn.vid=6
WHERE n.nid = 28499;

gives the proper result:

tid	vid	name	description	weight
19	6	rock pop		0

but as soon as I add another Inner join, no rows can become selected:

SELECT tn.name, tn2.name
FROM  vm_term_node n
INNER JOIN vm_term_data tn ON n.tid = tn.tid AND tn.vid=6
INNER JOIN vm_term_data tn2 ON n.tid = tn2.tid AND tn2.vid=8
WHERE n.nid = 28499;

does the second inner join act as an AND-condition and not an OR-condition?

Interesting; mssql seems to do it right. now, we tried another approach:

MariaDB [] > select tn.name AS name1, tn2.name AS name2 FROM ( vm_term_node n INNER JOIN vm_term_data tn ON n.tid = tn.tid AND tn.vid = 6 ) INNER JOIN vm_term_data tn2 ON n.tid = tn2.tid AND tn2.vid = 8 WHERE n.nid = 28499; Empty set (0.001 sec)

MariaDB []> explain select tn.name AS name1, tn2.name AS name2 FROM ( vm_term_node n INNER JOIN vm_term_data tn ON n.tid = tn.tid AND tn.vid = 6 ) INNER JOIN vm_term_data tn2 ON n.tid = tn2.tid AND tn2.vid = 8 WHERE n.nid = 28499;

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
1	SIMPLE	tn2	ref	PRIMARY,vid	vid	4	const	3
1	SIMPLE	tn	eq_ref	PRIMARY,vid	PRIMARY	4	drupalaco.tn2.tid	1	Using where
1	SIMPLE	n	eq_ref	PRIMARY,nid,tid	PRIMARY	8	drupalaco.tn2.tid,const	1	Using index

3 rows in set (0.001 sec)