A simple join question.. 1

[travs69]

table a
number in_use

table b
source destination


I'm stuck on trying to join these with out returning multiple rows

select b.source,b.destination where (a.number = b.source and a.in_use = '0') or (a.number = b.destination and a.in_use = '0') limit 1;

but that of course returns two rows. I'm trying to return a single row where neither source or destination is set to '1'

Thanks in advance

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[r937]

but that of course returns two rows

of course


say, could you perhaps take a moment and explain what each table is for? and how they are joined? and how you could possibly have a row in table a that joins to a row in table b if the columns in table b are all 0s and 1s?

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[travs69]

Sorry.. Number is a phone number as is source and destination. in_use is a boolean (tiny int)

Number/source/destinaion could be
1 222 333 4444
1 222 333 4445
1 222 333 4446
1 222 333 4447
etc

this sentence is incorrect
I'm trying to return a single row where neither source or destination is set to '1'

and should be
I'm trying to return a single row where neither source joined to number in_use or destination joined to number in_use is set to '1'


I actually think I figured this out with a couple of inner joins.. but I'm still testing.





~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[r937]

SELECT b.source
     , b.destination
  FROM tableb AS b
INNER
  JOIN tablea AS a1
    ON a1.number = b.source
   AND NOT a1.in_use = 1
INNER
  JOIN tablea AS a2
    ON a2.number = b.destination
   AND NOT a2.in_use = 1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[travs69]

I had the almost the exact same code execpt instead of the and not's I had it as a where.. any diffence? I've never seen it as and not

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[r937]

there is really no difference for inner joins, but i prefer putting the search conditions into the ON clauses because this way of thinking is important when you use outer joins

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[travs69]

Thank you for your help.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[travs69]

what if (always a what if )

either
The number does not exist in table a and if it doesn't i want it to return as if it did and in_use = 0

or
I can put that number in table A but want to ignore the in_use only for that number?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Travis - Those who say it cannot be done are usually interrupted by someone else doing it; Give the wrong symptoms, get the wrong solutions;

 

[r937]

in that case you need to switch to outer joins

in outer joins, you must put what you're searching for into the ON clause

SELECT b.source
     , COALESCE(a1.in_use,0) as source_in_use
     , b.destination
     , COALESCE(a2.in_use,0) as dest_in_use
  FROM tableb AS b
LEFT OUTER
  JOIN tablea AS a1
    ON a1.number = b.source
   AND a1.in_use = 1
LEFT OUTER
  JOIN tablea AS a2
    ON a2.number = b.destination
   AND a2.in_use = 1
 WHERE COALESCE(a1.in_use,0) = 0
    OR COALESCE(a2.in_use,0) = 0

now the query returns the b row whenever either the source or dest in_use is not 1

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon