wrong mysql output

Question

if I have these two tables :

Table1

AID1____________FID____________value1

1------------1----------12

7------------2-----------1

8------------1-----------1

Table2

AID2____________FID____________value2

7------------1----------3.3

When I execute the following query :

Select table1.value1, table2.value2, table1.AID1, table2.AID2

from table1,table2

where table1.FID = 1 or table2.FID = 1

I get :

value1_____________Value2_____AID1______AID2

12----------------- 3.3--------1--------7

1------------------3.3---------7--------7

1------------------3.3---------8---------7

But this is not the desired output, because some values should be NULL, but what I get is values doubled ! Can anyone help ?

This is my desired output : value1______Value2_AID1___AID2

12----------------- 3.3--------1--------7

1------------------NULL---------7--------NULL

1------------------NULL---------8---------NULL

The result set is not clear. Explain what result should be retrieved. — zerkms
– zerkms, Commented Dec 16, 2010 at 15:08
for example, why is 3.3 displayed three times, can't I have it just once ( in the first row only) , and in all the other rows set to NULL ? its because I only have it once in Table2 — SLA
– SLA, Commented Dec 16, 2010 at 15:10
Well what IS the output you expect? Do you want a JOIN, an OUTER JOIN, a UNION? — Konerak
– Konerak, Commented Dec 16, 2010 at 15:13
what im saying is that I dnt want entries t be repeated , could this be possible ? — SLA
– SLA, Commented Dec 16, 2010 at 15:17

Shakti Singh · Accepted Answer · 2010-12-16 15:00:00Z

1

Change your query to

Select table1.value1, table2.value2, table1.AID1, table2.AID2

from table1,table2

where table1.FID = 1 AND table2.FID = 1

answered Dec 16, 2010 at 15:00

Shakti Singh

86.7k21 gold badges142 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zerkms Over a year ago

This will give cartesian production, not what the OP expected.

SubniC · Accepted Answer · 2010-12-16 15:08:35Z

0

I've tried the scenario you describe and it is working as expected.

Table1:

AID1 -> int
FID -> int
value1 -> float

Table2:

AID2 -> int
FID -> int
value2 -> float

result:

value1, value2, AID1, AID2
12,3.3,1,7
1,3.3,7,7
1,3.3,8,7
NULL,3.3,NULL,7

How are you running it?

answered Dec 16, 2010 at 15:08

SubniC

10.4k4 gold badges30 silver badges33 bronze badges

Comments

bot403 · Accepted Answer · 2010-12-16 15:31:15Z

0

I think you want a union.

Select table1.value1, table1.AID1, from table1 where table1.FID = 1

union

Select table2.value2, table2.AID2 from table2 where table2.FID = 1

answered Dec 16, 2010 at 15:31

bot403

2,17115 silver badges14 bronze badges

Collectives™ on Stack Overflow

wrong mysql output

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related