1

I'm joinning two SELECTS. The first one retrieves:

t1
id  value
1   149
2   149
3   149
4   149

The second one:

t2 
id  value
149 2
149 13
149 145
149 149

So, I'm joining t1/t2 on t1.value = t2.id. But the outcome follows:

1   149 2
1   149 13
1   149 145
1   149 149
2   149 2
2   149 13
2   149 145
2   149 149
3   149 2
3   149 13
3   149 145
3   149 149
4   149 2
4   149 13
4   149 145
4   149 149

When the desired result should look like this:

1   149   2
2   149   13
3   149   145
4   149   149

I think that the problem appears because this are SELECT and not tables. After googling a lot I couldn't find any solution.

EDIT: MySQL query:

SELECT t1.id_sequence,t2.id_category, t2.tree
FROM
(
    SELECT * FROM (SELECT 1 AS id_sequence UNION SELECT 2 AS id_sequence UNION SELECT 3 AS id_sequence UNION SELECT 4 AS id_sequence ) as tbl1
    CROSS JOIN (SELECT DISTINCT id_catalog_category AS 'id_category' from catalog_category where id_catalog_category = 149) as tbl2
) as t1
LEFT JOIN 
(
    SELECT child.id_catalog_category AS id_category, ancestor.id_catalog_category AS tree
    FROM catalog_category AS child
    JOIN catalog_category AS ancestor
    ON (child.lft BETWEEN ancestor.lft AND ancestor.rgt)
    WHERE child.id_catalog_category = 149 AND ancestor.id_catalog_category != 1 
) as t2
ON t1.id_category = t2.id_category

Select retrieved table1 and table2 respectively.

Taz
  • 3,718
  • 2
  • 37
  • 59
Cristobal
  • 23
  • 3

2 Answers2

0

Mysql joins ROWS based on VALUES, which is different than joining on VALUES only.

In your dataset, there should be 4*4 rows returned, which is exactly what you get, so there's no problem with your query. Your expectations are wrong.

For example:

first row of t1: 1 149 you can see yourself that your join matches all rows of t2, so there are 4 rows returned. This is the same for all next rows of t1, which in total returns 16 of them.

EDIT: check this:

SELECT @rownum:=@rownum+1 as `id`, t2.* 
FROM (
    SELECT child.id_catalog_category AS id_category, ancestor.id_catalog_category AS tree
    FROM catalog_category AS child
    JOIN catalog_category AS ancestor
    ON (child.lft BETWEEN ancestor.lft AND ancestor.rgt)
    WHERE child.id_catalog_category = 149 AND ancestor.id_catalog_category != 1 
) as t2
Sebas
  • 21,192
  • 9
  • 55
  • 109
  • You are totally right @Sebas. A Silly mistake. Well, my question continues ¿How can I get the desired table? – Cristobal Jul 03 '12 at 18:01
  • I'm not sure of your usage of "table". Do you want to modify your structure or to change your query to actually get the desired result? – Sebas Jul 03 '12 at 18:07
  • I built a index sequence from the first select, then comes a product ID, and finally some product values. I want a query to obtain the final table on the question. As you well said, my query is performing a 1-* query, and what I need is a 1-1 result – Cristobal Jul 03 '12 at 18:12
  • there's a misunderstanding problem here. Your first query is just adding up sequence to the category table. Let me propose something. EDIT: got it now – Sebas Jul 03 '12 at 18:20
  • Almost there @Sebas, I was trying the same a minute ago, when appears a second issue. The tree column may has 3 or 4 values depending on the id_catalog_category value. So, I need that value to restart each time id_catalog_category (I need that value to traspose the results matrix). If a select just one value on the WHERE condition it works just fine. But after removing the condition it doesn't work as expected – Cristobal Jul 03 '12 at 18:43
  • I don't get it, your description doesn't match your "export" of t2 in your example – Sebas Jul 03 '12 at 18:47
0

I think your current result is correct. As an example, Assume that id in t1 uniquely identifies a man & value is the Walmart store # he is shopping at. Assume that id in t2 is Walmart store #, value is an item number list of everything in every Walmart store.

Hence, every man can buy everything from Walmart store # 149. Conclusion - What are you really trying to do?

Phil
  • 1