August 1, 2007 at 11:51 pm
I have 4 tables
Product - ProdID, ProdName
Location - LocID, LocName
ProdLoc- PLID, ProdID, LocID (connecting Product and Location)
Test - TID, PLID, Other Misc data
In ProdLoc, my ProdID and LocID might be blank or with '-'
If I am to grab all Test Record, how do I call my query and displaying all Test Record data, ProdName and LocName? My Test table only have the PLID but I wish to get the ProdName and LocName.
My current query only extracts all data where ProdID/LocID in ProdLoc is not equals to '-'
August 2, 2007 at 6:30 am
Can you post a sample ddl with some data?
August 2, 2007 at 11:18 am
Also, please be careful to include examples with '-', so that we know whether that is the full field, or the '-' is part of the field's value. (Hence the request for both DDL and sample data)
August 2, 2007 at 7:04 pm
My same data and query are as follows:
ProdLoc Table
---------
ProdLocID, ProductID, LocationID
1, -, 23
2,112, 24
3, 115, 24
Product Table
------------
ProductID, ProductName
112, ABC
115, DEF
Location Table
-------------
LocationID, LocationName
22, HumanResource
23, Engineering
24, Marketing
Test Table
---------
TestID, ProdLocID
31, 1
32, 2
33, 3
SELECT test.TestID, location.LocationName, product.ProductName
FROM Test as test
INNER JOIN ProdLoc AS prodLoc ON test.ProdLocID = prodLoc.ProdLocID
INNER JOIN Product AS product ON prodstat.ProductID = product.ProductID
INNER JOIN Location AS location ON ProdLoc.LocationID = location.LocationID
This query returns me:
TestID, LocationName, ProductName
2, Marketing, ABC
3, Marketing, DEF
SELECT test.testID, product.ProductName, location.LocationnName from Test test, ProdLoc prodloc, Product product, Location location WHERE
test.ProdLocID = prodloc.ProdLocID AND prodloc.ProductID = "-" and prodloc.LocationID = location.LocationID
This query returns me:
TestID LocationName ProductName
31, HumanResource, ABC
31, HumanResource, DEF
My problem lies where I wish to extract all information from Test table and if the query detects that there is a '-' in the ProductID entry in ProdLoc table, it will returns me the followings:
TestID, LocationName, ProductName
32, Marketing, ABC
33, Marketing, DEF
31, HumanResource, -
August 3, 2007 at 4:27 am
You need to do left joins into the Prod and Loc tables:
SELECT test.TestID, ISNULL(location.LocationName, '-'), INULL(product.ProductName, '-')
FROM Test as test
INNER JOIN ProdLoc AS prodLoc ON test.ProdLocID = prodLoc.ProdLocID
LEFT JOIN Product AS product ON prodstat.ProductID = product.ProductID
LEFT JOIN Location AS location ON ProdLoc.LocationID = location.LocationID
should do what you're looking for (hopefully). Have to figure out the HTML codes to make it look pretty. This assumes that every row in test has a corresponding row in ProdLoc (or you'll need to left join on that was well).
August 6, 2007 at 2:41 am
Thanks a lot. I got this correct.
SELECT test.TestID, IFNULL(location.LocationName, '-'), IFNULL(product.ProductName, '-')
FROM Test as test
INNER JOIN ProdLoc AS ProdLoc ON test.ProdLocID = prodLoc.ProdLocID
LEFT JOIN Product AS product ON ProdLoc.ProductID = product.ProductID
LEFT JOIN Location AS location ON ProdLoc.LocationID = location.LocationID
I am using Borland c++ to do my query and display it on the form. However, I cannot manage to display the productname/stationname in the form. It gives me an empty field. The query is correct if I run it in my console. Anyone knows how to solve this? I am displaying the table using TDBGrid's columns and prefix these columns according to the table attributes I want.
August 6, 2007 at 2:41 am
Thanks a lot. I got this correct.
SELECT test.TestID, IFNULL(location.LocationName, '-'), IFNULL(product.ProductName, '-')
FROM Test as test
INNER JOIN ProdLoc AS ProdLoc ON test.ProdLocID = prodLoc.ProdLocID
LEFT JOIN Product AS product ON ProdLoc.ProductID = product.ProductID
LEFT JOIN Location AS location ON ProdLoc.LocationID = location.LocationID
I am using Borland c++ to do my query and display it on the form. However, I cannot manage to display the productname/stationname in the form. It gives me an empty field. The query is correct if I run it in my console. Anyone knows how to solve this? I am displaying the table using TDBGrid's columns and prefix these columns according to the table attributes I want.
August 6, 2007 at 2:54 am
SELECT test.TestID, IFNULL(location.LocationName, '-') AS LocationName, IFNULL(product.ProductName, '-') AS ProductName
FROM Test as test
August 8, 2007 at 1:29 am
Thanks. This also works if I use in my SQL query browser.
I am using Borland Database library to display these data and I am using DBGrid. However, I get (MEMO) instead of the data I want.
August 8, 2007 at 10:51 am
Your basic problem is that your data is corrupt. The ProdLoc table is an intersection (or "glue") table which implements a many-to-many relationship between the Product and Location tables (any one product may be located at (or shipped from) many locations and any one location may contain (or supply) many products). So ProdLoc(ProductID) should be a foreign key reference to Product(ProductID) and ProdLoc(LocationID) should be a foreign key reference to Location(LocationID). So the row containing the "-" is invalid. That it even exists shows you have not defined the relationships properly.
Get all the relationships properly defined and, amazingly, these little pesky problems will disappear.
Plus, I really don't understand the role the test table plays in all this. It would seem to be nothing more than a duplicate of the ProdLoc table. In your example you are even using it in place of the ProdLoc table. Does it have a purpose the ProdLoc table cannot perform?
Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
August 8, 2007 at 7:15 pm
Yes, you are correct. My ProcLoc table is used to overcome the M to M relationship between Product and Location. My Test table however, is used to hold test datas, test results, test durations and other relevant information regarding each test for products/location.
Certain TEST can be implemented on product AND location but certain TEST are only for certain location/product. Therefore, the ProductID or LocationID inside ProcLoc can be '-'.
My query is correct. Just that in Borland c++, the data displayed is in (MEMO) instead of the ProductName I want.
August 8, 2007 at 8:59 pm
NVM guys. I've solved my problems. It's Borland C++ DBGrid's problem. Not my query problem.
Thanks for all the help! ^_^
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply