Jump to content

Problem with selecting specific data.


Recommended Posts

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.

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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`;

 

Link to comment
Share on other sites

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) :confused:

:wtf:

 

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

 

Any ideas?

 

 

Link to comment
Share on other sites

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;

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.

 

 

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.