Problem with selecting specific data.

[Johnnyboy69]

Hi all,

 

I am currently stuck with the following issue:

 

I have the following tables: Sale ( Contains Sales made) Saledetail (contains details of sale)

These sales have stock on them, tables: Stock (contains stock) Stockdetails (contains details of stock)

 

I am trying to select all Sales made with a specific stock type on them. This is easy enough, however, I have to select sales with ONLY the specific stock on them and no other stock.  I also have to display specific data from the Sale, Stock, and stockdetail tables.

 

Here is what I have so far:

 

SELECT `Sale`.`saleNo`,`Sale`.`dispatchDate`,`SaleDetail`.`serialStart`,`SaleDetail`.`serialEnd`,
`StockDetail`.`label`,`StockDetail`.`type`, Warehouse.name
FROM `Sale`
INNER JOIN `SaleDetail` ON `Sale`.`id` = `SaleDetail`.`saleID`
INNER JOIN `StockDetail` ON `StockDetail`.`id` = `SaleDetail`.`StockDetailID`
INNER JOIN Warehouse ON Warehouse.id = SaleDetail.warehouseID
WHERE `Sale`.`invoiceDate` BETWEEN '2012-04-19' AND '2012-04-19'
and `StockDetail`.`type` = 'SIM
'

 

In this case I am trying to retrieve Sales that contain only the Stock type of "SIM". It displays all the needed information I need, however, does not filter to ONLY retrieving sales with SIM on them and no other stock types.

 

Any advice on how to resolve this?

 

Or perhaps any general pointers on how to go about doing what I am trying to achieve?

 

Thanks in advance.

 

[smoseley]

Thiss will work:

 

SELECT `Sale`.`saleNo`,`Sale`.`dispatchDate`,`SaleDetail`.`serialStart`,`SaleDetail`.`serialEnd`,

`StockDetail`.`label`,`StockDetail`.`type`, Warehouse.name

FROM `Sale`

INNER JOIN `SaleDetail` ON `Sale`.`id` = `SaleDetail`.`saleID`

INNER JOIN `StockDetail` ON `StockDetail`.`id` = `SaleDetail`.`StockDetailID`

INNER JOIN Warehouse ON Warehouse.id = SaleDetail.warehouseID

WHERE `Sale`.`invoiceDate` BETWEEN '2012-04-19' AND '2012-04-19'

GROUP BY `Sale`.`id`

HAVING MAX(`StockDetail`.`type`) = 'SIM'

    AND MIN(`StockDetail`.`type`) = 'SIM'

 

as an alternate to that "having" clause, you can use this:

 

HAVING MAX(`StockDetail`.`type`) = 'SIM'

    AND COUNT(DISTINCT `StockDetail`.`type`) = 1

[Johnnyboy69]

Dear smoseley,

 

Thank you. Still have to test the results to see whether they are accurate but it seems to be working fine. Really appreciate your help.

 

Just one last thing, how would I go about adjusting the having clause in order to select 2 stock types.

 

I.e This time for instance I want sales that contain only BOTH "sim" and "handset" and nothing else.

 

Thanks for all your help, still quite new to SQL.

[smoseley]

That's a tough one... this might work:

 

 

SELECT `Sale`.`saleNo`,`Sale`.`dispatchDate`,`SaleDetail`.`serialStart`,`SaleDetail`.`serialEnd`,

`StockDetail`.`label`,`StockDetail`.`type`, `Warehouse`.`name`

FROM `Sale`

INNER JOIN `SaleDetail` ON `Sale`.`id` = `SaleDetail`.`saleID`

INNER JOIN `StockDetail` ON `StockDetail`.`id` = `SaleDetail`.`StockDetailID`

INNER JOIN Warehouse ON Warehouse.id = SaleDetail.warehouseID

WHERE `Sale`.`invoiceDate` BETWEEN '2012-04-19' AND '2012-04-19'

    AND MAX(CASE WHEN `StockDetail`.`type` IN ('SIM', 'OtherType') THEN 1 ELSE 0 END) = 1

    AND MAX(CASE WHEN `StockDetail`.`type` NOT IN ('SIM', 'OtherType') THEN 1 ELSE 0 END) = 0

GROUP BY `Sale`.`id`;

 

[Johnnyboy69]

Thanks smoseley, learning a lot in regards to the having function etc.

 

When running the code for selecting 2 types only, I get the following error:

Error Code : 1111

Invalid use of group function

(0 ms taken)

 

I'm playing around and checking google for a possible solution to this error.

 

Any ideas?

 

 

[smoseley]

Yeah, oops, MySQL is choking on the aggregate function in the where clause - can't execute a where on multiple records, need having for that. 

 

This will work;

 

 

SELECT `Sale`.`saleNo`,`Sale`.`dispatchDate`,`SaleDetail`.`serialStart`,`SaleDetail`.`serialEnd`,

    `StockDetail`.`label`,`StockDetail`.`type`, `Warehouse`.`name`,

    MAX(CASE WHEN `StockDetail`.`type` IN ('SIM', 'OtherType') THEN 1 ELSE 0 END) AS `has_required`

    MAX(CASE WHEN `StockDetail`.`type` NOT IN ('SIM', 'OtherType') THEN 1 ELSE 0 END) AS `has_other`

FROM `Sale`

INNER JOIN `SaleDetail` ON `Sale`.`id` = `SaleDetail`.`saleID`

INNER JOIN `StockDetail` ON `StockDetail`.`id` = `SaleDetail`.`StockDetailID`

INNER JOIN Warehouse ON Warehouse.id = SaleDetail.warehouseID

WHERE `Sale`.`invoiceDate` BETWEEN '2012-04-19' AND '2012-04-19'

GROUP BY `Sale`.`id`

HAVING `has_required` = 1

    AND `has_other` = 0;

 

[smoseley]

By the way, if you're grouping on Sale ID, be sure that your other tables  (sale detail, etc) either have a 1:1 relationship with the Sale table, or that you use the proper aggregate column on the data coming out.  The way I did this, it'll take one Sale record and random data from the other tables if they're not 1:1.

 

If you want multiple Sale Detail records per sale, it'll be a much more complex query (involving subselects, etc.).  Please explain the relationship of the tables so I can be sure you're getting the right query.

[Johnnyboy69]

Thanks smoseley.

 

The Sale table is linked to the SaleDetail table. Multiple SaleDetail ranges can be on one Sale. For instance 1 sale can have 5 ranges from the SaleDetail table each being a different product type.

 

Information I require is also being pulled from a Warehouse and stockdetail table. These 2 tables are linked with the SaleDetail. I.e in the SaleDetail information is stored from what warehouse the stock is coming from (warehou table) and what kind of stock this is: Product name (label column from stockdetail table) and product type(type column in stockdetailtable)

 

So in other words, 1 sale can have 5 Saledetail ranges which are linked with the warehouse and stockdetail tables regarding data it contains.

 

Hope this makes sense? Brain is a bit fried at the moment.