Select statement producing multiple rows unexpectedly 1

[Kalisto]

I have a situation where part of my WHERE clause needs to include ((table1.col1 = table2.col1) OR (table1.col1 = 0))

When I execute my query, wherever there is a match (i.e. table1 refers to a row in table 2) then I get a single line of data.

Wherever there is a 0 in table 1 (i.e. no link to table 2) then I get duplicate rows of the data in table1, 1 for each possible record in table2. So how do I structure my syntax?

Or is there a better way?

(My 2 tables - 1 shows reported bugs, and the other shows details on who has been assigned to correct it, if anyone. - As bugs can be reported by anyone, and it needs the P.M to assign a fixer, I cant really change my underlying data, and nor can I have 2 seperate queries, as the manglement wish to have a master view of all bugs, (assigned and not)

Any help greatfully received

K

 

[PHV]

Have you tried SELECT DISTINCT ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

use a LEFT OUTER JOIN

select columns
from table1
left outer
join table2
on table1.col1 = table2.col1

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Kalisto]

Thanks but neither of those works.

I still get all the unique rows, but wherever table1 has a value of 0, I get whole set of duplicate rows, each one with a different id at the end, not even a 0

The whole query is a bit of a monster, pulling from about 6 different tables (not my design), so I cant figure out whats happening.

I'll post it here, just in case you can see anything obvious.

select distinct [b].[assigned_to] as b_assigned_to, [b].[bug_id] as b_bug_id, [b].[bug_name] as b_bug_name, [b].[priority_id] as b_priority_id, [b].[project_id] as b_project_id, [b].[status_id] as b_status_id, [b].[test_id] as b_test_id, [p].[project_id] as p_project_id, [p].[project_name] as p_project_name, [p1].[priority_id] as p1_priority_id, [p1].[priority_desc] as p1_priority_desc, [e].[employee_id] as e_employee_id, [e].[employee_name] as e_employee_name, [s].[status_id] as s_status_id, [s].[status] as s_status, [tst].[pk] as tst_ID  from [bugs] b, [projects] p, [priorities] p1, [employees] e, [statuses] s, [TestCases] tst where [p].[project_id]=b.[project_id] and [p1].[priority_id]=b.[priority_id] and [e].[employee_id]=b.[assigned_to] and [s].[status_id]=b.[status_id] and (([tst].[pk]=b.[test_id]) or (b.[test_id]=0))

 

[r937]

i don't see a LEFT OUTER JOIN in your query

how can you say my solution doesn't work?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Kalisto]

I tried it and it didnt. so I reverted back to what I knew worked before!

(But, SQL not being my strong suit, was I right in assuming that I needed it before the problem table, i.e.

select .....
AND LEFT OUTER JOIN tst ON [tst].[pk]=b.[test_id] or b.[test_id]=0 ?

 

[Golom]

The obvious cause of your problem is

and (([tst].[pk]=b.[test_id]) or (b.[test_id]=0))

which translates to "... if b.test_id is zero then use all the records from the 'tst' table ...". If it isn't zero then use only the record where the test_id and pk fields match.

It looks like the table [TestCases] tst doesn't contribute much to the discussion. Either it matches b.test_id and you already have that value or b.test_id is zero and the values from tst.pk are not ones that you want to see.

What's the "business" meaning of b.test_id being zero? Does that mean that no tests are defined?

 

[r937]

select b.assigned_to    as b_assigned_to
     , b.bug_id         as b_bug_id
     , b.bug_name       as b_bug_name
     , b.priority_id    as b_priority_id
     , b.project_id     as b_project_id
     , b.status_id      as b_status_id
     , b.test_id        as b_test_id
     , p.project_id     as p_project_id
     , p.project_name   as p_project_name
     , p1.priority_id   as p1_priority_id
     , p1.priority_desc as p1_priority_desc
     , e.employee_id    as e_employee_id
     , e.employee_name  as e_employee_name
     , s.status_id      as s_status_id
     , s.status         as s_status
     , tst.pk           as tst_ID  
  from bugs b
inner
  join projects p
    on b.project_id = p.project_id 
inner
  join priorities p1
    on b.priority_id = p1.priority_id 
inner
  join employees e
    on b.assigned_to = e.employee_id
inner
  join statuses s
    on b.status_id = s.status_id 
[b]left outer
  join TestCases tst 
    on b.test_id = tst.pk[/b]

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Kalisto]

Rudy thanks, I get a missing operator error when I execute the query, but I'll poke about to see what it is, Im sure I can figure it out!

Golom at the moment it doesnt add a lot, Im not an SQL person, so I just wanted initially to retrieve a parameter from the new table to make sure that it worked before I went any further. (Eventually I want to add a link in there so that I can display the test case on a new page).

And yes, the business meaning of 0 menas that there isnt yet a test case defined for the particular bug

Thanks for your help

K

 

[r937]

missing operator? is this by any change microsoft access?

add some parentheses

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[Kalisto]

Aye, the final database isnt, but its easier to see whats going on in access!

I guess I need (inner join...(inner join...()))

Ah well, I'll find out in a moment

Ta
K

 

[PHV]

Kalisto, be aware that access (Jet SQL) is far away from ANSI SQL ...

 

[Kalisto]

Ah, I beg you pardon. I am forced to use access at present, but will be moving to a larger platform in the future, so I *hoped* that access would work with ansi sql, just have its own tweaks on top.

I'll go and haunt the access forum for a while until I can walk then!

K

 

[r937]

but the only difference in this particular instance is those parentheses



rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)