Left/righ join dont know anymore

[nitai_co]

Hi all,

I need to get all the records from one table but only if some values from
other tables are verified. Ok, so this should be normal like:

select * from
tableA, tableB, tableC
where tableB.hostid = 1
and tableC.groupid <> 1

This should actually give me all records from tableA if hostid from tableB is
"1" and groupid from tableC is not "1".

So actually this returns me nothing, because not all records from tableA are
in tableC. So I thought to use a left join but all I have tried does not work
at all.

Any kind soul please enlighten me? Thank you.

[CF_Oracle]

Put the code you tried with outer joints. What database do you query?

[paross1]

Also, you aren't relating the three tables to one another. How is table A
related to B and B to C (or A), etc.? I am surprised that you didn't get a
Cartesian join, where you return a product of the numer of rows in each table,
rather than nothing.

Phil

[CF_Oracle]

Paross you are right. That what I mean asking nitai_co to "put the code with
outer joints" here for our review and possible help. But it could be
interpreted as advise what to do. Since he/she wrote "I thought to use a left
join but all I have tried does not work at all" these 3 tables heve been
jointed somehow. Anyway we need to no foreign keys etc.

[nitai_co]

Ok, sorry if I did not write it out properly. But the code I wrote is actually
what I am doing and I get ALL the record, but I need only the records from
tableA who DONT have 1 in the tableC.groupid field.

So this code:

select * from
tableA, tableB, tableC
where tableB.hostid = 1
and tableC.groupid <> 1

shows me all records. Trying:

select * from
tableA, tableB, left join tableC on tablec.groupid <> 1
where tableB.hostid = 1

It was a long day and I dont know where to look anymore and what to do :-) (we
are on Oracle 10g).

PS: I am a he

[paross1]

That is what I meant by a Cartesian join (ALL records returned, actually all
three tables times each other). What you need to add is how these tables are
related. You only have where tableB.hostid = 1 and tableC.groupid <> 1, but how
is tableB related to tableA? Something like tableA.hostid=tableB.hostid and
also something like tableB.groupid = tableC.groupid, etc. (This is only an
example, because you really didn't specify how these tables are related.)
Otherwise, you just get the product of their rows times each other (a Cartesian
join ).

Phil

[nitai_co]

Originally posted by: mxstu

I'll let someone else post an optimization of that query ;-) ... but at the
very least ... specify the column names instead of using "SELECT *".


Yes, I always do, this was just for posting and making the code look "better"

[mxstu]

Okay, that's what I thought. It never hurts to make sure. Amazing the amount of people that actually use select * on a regular basis...

[nitai_co]

Really? hmhmhhm....those are the poeple who wonder why their application gets so slow when they hit a million records :-) Thank you for all suggestions.