The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Missing Database Entries

Hello,

I have a database that has a few tables, product - holding product informtion, productsection - link table storing product codes and section id's - section - holding section information

When I run my JOIN query, it seems to not pick up some of the product codes.

SELECT product.code, section.section FROM product RIGHT JOIN productsection ON product.code = productsection.code LEFT JOIN section ON section.id = productsection.id WHERE section.id = 10;

All the NULL returned items do have entries in the product table.

Does anyone know why the database isn't picking these up?

Can't work it out, if you could point me in the right direction

Thanks
Talib Kweli
Sunday, July 23, 2006
 
 
Can you give us a small example of the relevant data in each of these three tables, as well as an example of the output that the example should produce?

I.e.:

ProductCode
1
2
3
4

Section  ProductCode
1        1
1        2
2        3

Desired output:

ProductCode  Section
1            1
2            1
3            2
4            (null)

or whatever?
Karl Perry Send private email
Sunday, July 23, 2006
 
 
Hey, sorry some test data would be good haha :)

Product table
-------------
Code
M100CEL-BLK
M100CER-BLK
M100FEL-BLK

Productsection table
--------------------
ID  - Code
1  - M100CEL-BLK
1  - M100CER-BLK
1  - M100FEL-BLK

Section table
-------------
ID - Name
1  - Mirrors

So now when I run my JOIN query it seems to find some products and not others!

Query: SELECT product.code, section.section, productsection.code FROM product RIGHT JOIN productsection ON product.code = productsection.code LEFT JOIN section ON section.id = productsection.id WHERE section.id = 1;

This would return:
Code  -  Section  - Code
NULL  -  Mirrors  - M100CEL-BLK
M100CER-BLK - Mirrors - M100CER-BLK
NULL  -  Mirrors  - M100FEL-BLK

So for the first and third rows in that table it has been unable the code in the product table. But they are in there and the code is correct in the productsection table. I'm totally baffled.
Talib Kweli
Monday, July 24, 2006
 
 
Talib -

I don't think there is any problem with the tables and the query. I guess you can check if there's a typo in the enteries. May be they aren't matching. The spelling error or some other simple error should be with the enteries in the Product table.

You may have checked it already. But I thought about it because there's a small error in your post. If you run the query given above you should get an error as there's no column by name 'Section' in the table "Section". I think you meant it to be 'Name'.
Senthilnathan N.S. Send private email
Monday, July 24, 2006
 
 
Yeah, sorry that was my mistake, in my DB section has ID and Section.

Yes I can't explain why the query won't work but I'm going to have to fine comb through them all. The product codes are in the database exactly as they are in the productsection table hmmm

Thanks for the reply :-) At least i'm along the right lines!
Talib Kweli
Monday, July 24, 2006
 
 
When go through them all check for extra spaces also. As it may not be clearly visible when you look at the data in the Enterprise Manager they are usual culprits. You can run a query and fetch the length of all the entries and compare them.
Senthilnathan N.S. Send private email
Monday, July 24, 2006
 
 
Talib,

This may sound strange, but explain exactly in one sentence what your query is supposed to return.
D in PHX Send private email
Monday, July 24, 2006
 
 
Talib,

It depends on the output you want. If you want to list only the products that have matches in the sections table, this will work:

select pr.code, prs.id, sec.section
from products pr
inner join productsection prs on pr.code = prs.code
inner join section sec on sec.id = prs.id

If you want all rows with or without matching data in the section table, you can use this:

select pr.code, prs.id, sec.section
from products pr
left outer join productsection prs on pr.code = prs.code
left outer join section sec on sec.id = prs.id

Ken
Ken
Monday, July 24, 2006
 
 
This is really subtle, but it has bitten me so many times before. It might be what's affecting you, or it might not.

Let's say I have a column that's of type char(4) in one table.  And a column of type vchar(4) in another table. Sometimes rows will match, and sometimes they will not match.

Why? Because if I put the letters ABC into char(4), the database expands the string to "ABC " (notice the space) since the field is four characters long.

vchar(4) in contrast leaves the string alone and reports it as "ABC" since the database allows for it to grow and shrink with a maximum of four characters.

Consequently, "ABCD" matches "ABCD" but "ABC " does not match "ABC".

Check to make sure that the data types of the columns that you're joining on, are the same.
TheDavid
Monday, July 24, 2006
 
 
Thanks for the replies :)

TheDavid, yeah I am going to have a look at what you suggested its smees quite likely!

My query is supposed to return products and there sections! The problem is that some product codes aren't matching! I don't want to see just the matches but I want to see the NON matches to and correct the issue :)

Anyway I'm going to see if I can fix this now, again thanks for all your replies, I've been trying to solve this for a few days now and your input is appreicated

Cheers
Talib Kweli
Tuesday, July 25, 2006
 
 

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
 
Powered by FogBugz