Using a subquery to determine the "WHERE" of a main query.

[esender]

Hey Everyone,

I got another hopefully simple question..

Using the GROUP_CONCAT, I'd like to do a big OR statement in my query:

 

SELECT		GROUP_CONCAT(node.title separator '||| |||')   AS titles
FROM			node
WHERE		node.nid =	(	SELECT		
					DISTINCT    GROUP_CONCAT(ranked_nid separator ' OR node.nid = ')
					FROM		bc_bests
					WHERE       ranked_rank = 1
					AND		(best_nid = 8286 OR best_nid = 8289)
						)

So in the sub-query, the output is something like:

+------------------------------------------------------+
| GROUP_CONCAT(ranked_nid separator ' OR node.nid = ') |
+------------------------------------------------------+
| 7966 OR node.nid = 7964                              |
+------------------------------------------------------+

So it is in shape for a big OR statement.

 

How do I make sure the query actually reads this.

 

The output of it now is something completely wrong. It gives me a row that its not even in the "ranked_nid = 1" category!

 

8286 and 8289 are temporary numbers that I will give later from outside the SQL.

 

Instead of "=" I have tried "IN" before the subquery.

 

Thanks a lot!

[esender]

Actually I was able to get around the "OR ... " subquery by suggestion of another friend. However, now a new question arrises (the answer to my previous question lies pretty obviously in the following queries)

 

When I use the GROUP_CONCAT, it seems to only return 1 single row.

 

Here is what I got:

 

SELECT			
DISTINCT		node.title		AS title
FROM			node
LEFT JOIN		bc_bests
ON			node.nid = bc_bests.ranked_nid
WHERE			bc_bests.ranked_nid IN 	(	SELECT      bc_bests.ranked_nid
						FROM	bc_bests
						WHERE	   (bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
						AND			bc_bests.ranked_rank = 1
					);

 

This returns multiple rows. Good..

+-------------------------------------------+
| title                                     |
+-------------------------------------------+
| Panasonic TH-42PX80U 42" Plasma TV        |
| Panasonic Viera TH-58PZ800U 58" Plasma TV |
| Panasonic TH-50PZ800U 50" Plasma TV       |
| Samsung LN52A650 52" LCD TV               |
+-------------------------------------------+

 

However, when I add the GROUP_CONCAT, it only returns one row..

SELECT			
DISTINCT		node.title										AS title,
		GROUP_CONCAT(bc_bests.best_nid separator '+')   AS bids
FROM			node
LEFT JOIN		bc_bests
ON			node.nid = bc_bests.ranked_nid
WHERE			bc_bests.ranked_nid IN 	(	SELECT      bc_bests.ranked_nid
						FROM	bc_bests
						WHERE	   (bc_bests.best_nid = 8286 OR bc_bests.best_nid = 8289)
						AND			bc_bests.ranked_rank = 1
					);

 

and..

+-------------------------------------------+------------------------------------------------------------------------------------------------------------+
| title                                     | bids                                                                                                       |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+
| Panasonic Viera TH-58PZ800U 58" Plasma TV | 8287+8541+8287+8289+8290+8284+8285+8286+8288+8289+8290+8291+8538+8538+8538+8539+8540+8542+8542+10848+10952 |
+-------------------------------------------+------------------------------------------------------------------------------------------------------------+

 

So, now it is only returning one row with ALL the "bids" in just one cell.

 

My hope is that it can return all 4 rows with the "bids" column intact for all of them.

 

Really appreciate any help

[aschk]

Before I go any further, i'd like to ask that you provide a schema layout for the tables you are using. And a description and sample set of the data you want returning.

[esender]

Before I go any further, i'd like to ask that you provide a schema layout for the tables you are using. And a description and sample set of the data you want returning.

 

Sure:

 

mysql> desc bc_bests;
+-------------+------------------+------+-----+---------+-------+
| Field       | Type             | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+-------+
| best_nid    | int(10) unsigned | NO   | MUL | 0       |       |
| ranked_nid  | int(10) unsigned | NO   | MUL | 0       |       |
| ranked_rank | int(10) unsigned | NO   |     | 0       |       |
| why_best    | mediumtext       | NO   |     | NULL    |       |
+-------------+------------------+------+-----+---------+-------+

mysql> desc node;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| nid       | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
| vid       | int(10) unsigned | NO   | UNI | 0       |                |
| type      | varchar(32)      | NO   | MUL |         |                |
| language  | varchar(12)      | NO   |     |         |                |
| title     | varchar(255)     | NO   | MUL |         |                |
| uid       | int(11)          | NO   | MUL | 0       |                |
| status    | int(11)          | NO   | MUL | 1       |                |
| created   | int(11)          | NO   | MUL | 0       |                |
| changed   | int(11)          | NO   | MUL | 0       |                |
| comment   | int(11)          | NO   |     | 0       |                |
| promote   | int(11)          | NO   | MUL | 0       |                |
| moderate  | int(11)          | NO   | MUL | 0       |                |
| sticky    | int(11)          | NO   |     | 0       |                |
| tnid      | int(10) unsigned | NO   | MUL | 0       |                |
| translate | int(11)          | NO   | MUL | 0       |                |
+-----------+------------------+------+-----+---------+----------------+

 

The site I am doing work on has two types of nodes (This is drupal) Item nodes and "Best" nodes. Item nodes are actual items but best nodes are lists. So an item can be #1 on multiple lists.

 

Hope this suffices in answering you clarification question.

[aschk]

And a sample dataset and simple explanation?

Drupal eh? I've been working with it for the past couple of months so hopefully it'll become obvious what you're after when I get these last pieces of the puzzle.

[aschk]

So you have sets of nodes?

i.e.

Best List 1 contains Nodes, 1,2,3?

Best List 2 contains Nodes, 1,3,5?