Query with previously selected Id

[friiks]

Hey!

 

Wont go on with senseless yapping and just explain my problem.

 

I'm currently working on an app that has to select multiple database rows from different tables. They have one value that stays the same for all, everything's fine and working well until I filter some of the queries.

 

The problem is that the filter selects data by specific field and selects the row which matches the filter.

 

 

This image should give the idea how the table looks.

 

So, when the filter is called I select data from this table by choosing to select rows who only have 2 as val3. Everything goes fine until the moment I output selected data because mysql has only selected the row with 2  as val3.

I tried to use the filter like this

SELECT ... WHERE (ThisTable.val3 = $filter OR ThisTable.Id = OtherTable.Id) ..

 

Which, as far as I know, should work fine as it gets the filtered values OR the other tables Id (it is called earlier in the query) thus showing all the rows I want to see.

 

This doesn't work like that sadly as it gets all of the rows in ThisTable

 

Are there any suggestions on how to achieve what I'm trying to do ? :S

 

 

Thanks,

Matt

 

P.S.

Here's an example of how my query looks.

 

SELECT
Orders.OrdersId,
`ClientsId`,
`BranchesId`,
`OrdersStatus`,
`OrdersCreatedTime`,
`OrdersOrderedTime`,
`OrdersStartedTime`,
`OrdersAssemblingTime`,
`OrdersFinishedTime`,
`OrdersShipmentType`,
`OrdersPhone`,
`OrdersEmail`,
`OrdersAddress`,
`OrdersAdminComment`,
OrdersProductsMap.LensesPower,
OrdersProductsMap.LensesBaseCurve,
OrdersProductsMap.LensesCylinder,
OrdersProductsMap.LensesAxis,
OrdersProductsMap.Quantity,
OrdersProductsMap.ProductsId FROM `Orders`
LEFT JOIN `OrdersProductsMap`
ON OrdersProductsMap.OrdersId = Orders.OrdersId
LEFT JOIN `APcomments`
ON APcomments.OrdersId = Orders.OrdersId
LEFT JOIN `ProductsProductsCategoriesMap`
ON ProductsProductsCategoriesMap.ProductsId = OrdersProductsMap.ProductsId
WHERE (OrdersProductsMap.ProductsId = 79 OR OrdersProductsMap.OrdersId = Orders.OrdersId) AND `OrdersName` = 'Ducky' AND `OrdersSurname`='Duck' AND (`OrdersStatus`='finished' OR `OrdersStatus`='assembling') AND Orders.OrdersId = (SELECT MAX(Orders.OrdersId) FROM `Orders` WHERE `OrdersName` = 'Ducky' AND `OrdersSurname`='Duck' AND (`OrdersStatus`='finished' OR `OrdersStatus`='assembling'))

[fenway]

Well, the table isn't normalized, which is likely part of the problem.

 

But your example shows a single table, yet your query refers to multiple.