| Cartesian join on tables with no relationship [message #22174] |
Thu, 26 September 2002 21:49  |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
Hi people. I'm just having a problem here with the database that I have created. Basically, I'm working on a database which contains information on assets audit. I was given an excel spreadsheet which contains old audit information. I have to compare my new audit with this old one. So I imported this excel sheet into the database as a table. I also created another table which is currently being populated by the new audit information. Unfortunately, I cann not join these 2 tables because there is no similarity. I need to make a query to list the assets that have matching serial numbers in both table( Basically I'm trying to produce a result of how many assets that we audited are in the old excel sheet) This is my query;
SELECT New_audit.id,New_audit.equipment,New_audit.[[serial number]]
FROM New_audit, Old_audit
WHERE New_audit.[[serial number]] = Old_audit.[[serial number]];
This, of course, produces the unwanted Cartesian join. How do I get rid of the Cartesian join since I can't seem to link the 2 tables up. I can't join them on Serial number neither because most of the Serial number field in the Old table are empty. Please Help. I have read somewhere that if you can't join the tables up then u have to create a 3rd table or another query as a bridge between the two, but how do I do that? please help
|
|
|
|
| Re: Cartesian join on tables with no relationship [message #22175 is a reply to message #22174] |
Thu, 26 September 2002 22:18  |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
SELECT New_audit.ID, New_audit.Equipment, New_audit.[[Serial Number]]
FROM New_audit INNER JOIN Old_audit ON New_audit.[[Serial Number]] = Old_audit.[[Serial Number]]
;
I tried that as well but it still gives me Cartesian join because (unfortunately) some equipments have the same Serial Number !!!!! Please help guys.
|
|
|
|
| Re: Cartesian join on tables with no relationship [message #22177 is a reply to message #22174] |
Thu, 26 September 2002 23:40 |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
Hey, Kumar. I use matching Serial number as an indication that an audit is in both old and new. The problem is most of the entry in Old table doesn't have S/N. And some equipment of different types happen to have same S/N. Would u have any idea.
This is sample entry from 2 table. As u can see, 2 table have different fields since one is created from scratch and one has been done with minor difference in the available fields. As u can see, both entries in Old table doesn't have S/N. I dunno how can I join the table up in this scenario. Is there another way around it.
NEW_Audit Table
ID Frame ID Equipment Make Model Number Serial Number Serial Number-2 Site Location User Name Cost Centre Condition Comments Audited By 1 0001 Laptop IBM G51 66K8925 Sussex st S-L4-4073 VAT N/A GOOD Username Not on Desk Sakthi
2 0002 KEYBOARD IBM KB-7953 0413152 Sussex st S-L4-4074 N/A N/A GOOD N/A Sakthi
3 0003 MONITOR IBM G74 6657264 Sussex st S-L4-4075 N/A N/A GOOD N/A Sakthi
OLD_AUDIT TABLE
ID Cost Object Project Name Purch Order # Cost Centre Asset Label model Type subtype Description Serial number User Lease End
1 4208 DESKTOP TOOLS & S 5500007828 HEWLETT - array FACTORY INTEFRATED 30/10/2004
2 4208 DESKTOP TOOLS & S 5500007828 HEWLETT - array FACTORY RACKED AUTORAID ARRAY UST3501036 30/10/2004
|
|
|
|
|
|
| Re: Cartesian join on tables with no relationship [message #22199 is a reply to message #22174] |
Sat, 28 September 2002 14:05 |
Tom
Messages: 67
Registered: June 1998
|
Member |
|
|
Hmm, I would go by Serial number, and then if there are more than 2 entries with matching serial number then I will have a look at other fields to match them up. In an ideal database, this would work, eg.
***************
WHERE table1.serial = table2.serial AND table1.model = table2.model
***************
The addition of maching model number criteria would help to break down several equipments that happen to have same serial number. But the thing is, the old database that I was given is really bad. A lot of entires have missing model number and serial number, so the above criteria would still give me either a cartesian join or a non matching in the case where there are several matching serial number and blank entries in the model number field.
EG, if there're such entries;
MY TABLE:
1: S/N=2323 M/N=qwerty
2: S/N=2323 M/N=
GIVEN TABLE:
1: S/N=2323 M/N=
2: S/N=2323 M/N=
where entry1 in my table is entry1 in given table and entry2 in my table is entry2 in given table respectively. Some equipment just happen to not have a model number.
Using the above WHERE clause, if I want to query out entry1 in my table, the query would return NULL, wherea if i want to query out entry2 in my table, the entry would produce unwanted cartesian join.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
