MySQL Query Help

5

5tharrow

Limp Gawd
Joined
Dec 9, 2005
Messages
193
My brain just aint working today. I need a query guru to help me out here if possible. I'm joining two tables (simple enough).

The first table is a list of bug requests (Table A), the second table contains custom field information (Table B).

If a bug request is entered, and the user fills-in only those custom fields that are applicable, the fields that are not filled-in simply don't exist in Table B (unless they default to some value and the user doesn't decide to erase this default value...don't ask). Each populated custom field adds a new row in Table B, and that row is identified by a code that represents the type of field being used.

With me so far?

Here's my problem...

Let's say one of the custom fields is a drop-down for a customer name. It defaults to 'NONE'; however, this field hasn't always existed, so there are many bugs that have been submitted without a customer name.

If I write a report query that is supposed to show both a Bug ID from Table A and a Customer Name from Table B (or nothing if there was no associated customer), the join only returns Bugs where a value for customer name actually exists in Table B. Like so...

0003 Acme Corp
0004 Electric Co.
0005 NONE
0006 Toys R Us

What I want to have happen is to show all bugs, regardless of whether or not there is an associated Company. Like so...

0001
0002
0003 Acme Corp
0004 Electric Co.
0005 NONE
0006 Toys R Us

I've tried different types of inner and outer joins to no avail. If the friggin field existed in Table B but was just empty, I'd be fine. This also causes a problem when I'm trying to figure out which bugs do NOT have an associated customer.
 
riot8ap said:
I think you'll want to look into an "Outer" Join.

http://en.wikipedia.org/wiki/Join_(SQL)#Outer_joins
Click to expand...

Yeah. I've tried that, but from what I understand this will only work if the cell where the data would normally be stored contains a NULL. In my case, the ROW doesn't exist.

I don't know why this DB is constructed this way. To me, it makes more sense to add a column to a custom table when a new custom field is created. For example:

id bug_id customer region
01 001 Acme South
02 002 NULL NULL

...then you add a new custom field "planet" and the table becomes:

id bug_id customer region planet
01 001 Acme South Earth
02 002 NULL NULL Mars

Instead, this DB has a code for each new custom field you create, and it adds a row for each value inserted. The resulting custom table ends-up looking like this:

id bug_id field_id value
01 001 0001 Acme
02 001 0002 South
03 001 0003 Earth
04 002 0003 Mars

In this example, bug 002 had no values filled-in for the custom fields "customer" (0001) and "region" (0002) , so the rows that would normally hold those values simply don't exist. There's nothing to join on.

I appreciate the help. Maybe outer joins can solve this and I'm just not seeing it.
 
you want to use either a left or a right join (depending how you join the tables).


Code: 
select * from tablea left join tableb
  on tablea.id = tableb.tableaid
 
KingKaeru said:
you want to use either a left or a right join (depending how you join the tables).


Code: 
select * from tablea left join tableb
  on tablea.id = tableb.tableaid
Click to expand...

Yep, I tried that and it doesn't work. I don't think what I'm trying to do is possible. If I was dealing with NULL values that would be one thing, but I'm essentially dealing with NULL rows.

Appreciate the input though. Thanks.
 
5tharrow said:
Yep, I tried that and it doesn't work. I don't think what I'm trying to do is possible. If I was dealing with NULL values that would be one thing, but I'm essentially dealing with NULL rows.

Appreciate the input though. Thanks.
Click to expand...

I think if people saw the query you were trying to run, someone would be able to provide an answer.

For an example using the AdventureWorks database, Production.Product contains Product IDs and Product Names. Production.ProductReview contains Product Reviews for Products, but it doesn't contain reviews for all of the Products.

The following query pulls out all of the Products and all of their Reviews. If the Product does not have a review, ProductReviewID will be null.

Code: 
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID

Taken from http://msdn.microsoft.com/en-us/library/ms187518.aspx
 
Jason Isom said:
I think if people saw the query you were trying to run, someone would be able to provide an answer.

For an example using the AdventureWorks database, Production.Product contains Product IDs and Product Names. Production.ProductReview contains Product Reviews for Products, but it doesn't contain reviews for all of the Products.

The following query pulls out all of the Products and all of their Reviews. If the Product does not have a review, ProductReviewID will be null.

Code: 
SELECT p.Name, pr.ProductReviewID
FROM Production.Product p
LEFT OUTER JOIN Production.ProductReview pr
ON p.ProductID = pr.ProductID

Taken from http://msdn.microsoft.com/en-us/library/ms187518.aspx
Click to expand...

I do need to provide a better example. I'll try to do that. I know what my problem is though.

The query works using a left outer join, but it returns rows for all custom fields. Since a new row is created every time a custom field (any one of about 10 different) is created, one bug could have 10 custom rows associated with it, and another could have 2, or 1, and so on. I end up getting back multiple rows for each bug when I only want one...NULL or not.

I have to provide a WHERE clause that only returns those rows from the custom table that relate to the customer name custom field type specifically. As soon as I do that it kills my query because it ignores everything else.

In my above example, this works but gives me too much data. I don't care about North, South, or Mars. Only Acme or NULL.

SELECT
b.bug_id,
c.value
FROM
bugs b LEFT OUTER JOIN custom c on b.bug_id = c.bug_id

It does return a NULL values if the bug hasn't had ANY custom fields filled-in, but it also shows 1 - 10 rows for each bug that HAS had a custom field filled-in. However, if I do the following so that I only see customer names, it obviously kills me because it gives me back only that which has a value.

SELECT
b.bug_id,
c.value
FROM
bugs b LEFT OUTER JOIN custom c on b.bug_id = c.bug_id
WHERE
c.field_id = '0001'

That query gives me all bugs that have associated customers, but not those who have not had the customer field filled-out. Adding "or c.field_id is NULL" to the end doesn't work either.

I'm not the best at explaining this. I'm relatively new to SQL. Thanks for your help so far though. I feel like I'm closer.
 
Does the following work?

Code: 
SELECT bug.bug_id, custom.value
FROM bug
    LEFT OUTER JOIN custom ON bug.bug_id = custom.bug_id 
        AND custom.field_id = '0001'
 
can you show us the columns in Table A and columns in Table B? Then the exact column names you want to join. I think we can figure this out better with that info.
 
Jason Isom said:
Does the following work?

Code: 
SELECT bug.bug_id, custom.value
FROM bug
    LEFT OUTER JOIN custom ON bug.bug_id = custom.bug_id 
        AND custom.field_id = '0001'
Click to expand...

Jason...I think this worked! I'll have to spend a few minutes going over it because it's over 5,000 rows returned, but after spot-checking it, it looks OK.

I've never written a query like that before. Care to explain what this is doing and why it works?
 
Note that LEFT OUTTER JOIN is the same as LEFT JOIN. Can anyone confirm?
 
KingKaeru said:
Note that LEFT OUTTER JOIN is the same as LEFT JOIN. Can anyone confirm?
Click to expand...

I think that's correct. As soon as you start using the terms LEFT or RIGHT, an OUTER JOIN is used regardless of whether or not you use the word OUTER.

If you don't use RIGHT or LEFT, then an INNER JOIN is assumed and is the default join type.

That's the way I understand it anyway.
 
You must log in or register to reply here.
Back
Top