SQL Query

airwalk_90

Sorry, should have been more specific.

I'm currently using:

select registrationno, business, monthend
From tbldetails
where registrationno > 50
AND Monthend = 'Jan'
AND ....... (Im stuck here)

The table in question is called tbldetails.

The table headers I am looking at are: registrationno, Business, monthend

Registrationno contains different reg numbers.

Business contains both 'yes' and 'No'.

Monthend contains the months.

My problem is that I'm struggling to pick out all registrationno's for a specific month who choose BOTH 'yes' and 'no' (for that month)

I am using SQL query analyser through SQL server enterprise manager.

Hope you can help.

Kimbot

WHats is the exact query you are using??

Select etc etc etc.

Post it up!!

Kimbot

airwalk_90 wrote:

tbldetails.

The table headers I am looking at are: registrationno, Business, monthend

Business contains both 'yes' and 'No'.

Monthend contains the months.

My problem is that I'm struggling to pick out all registrationno's for a specific month who choose BOTH 'yes' and 'no' (for that month)

Hope you can help.

I would go with

select registrationno
from tbldetails
where Monthend = 'XXX'
and Business in ( 'yes', 'no');


something along those lines in anyway!!

airwalk_90

and Business in ( 'yes', 'no');

does this line basically mean that: where business contains both yes and no?

Kimbot

It does indeed!!

Basically what that means is what ever is in the brackets are the only values that you search for and it has to contain one of those!

airwalk_90

one more question:

THis is returning multiple lines of data (as the one reg number can be equal to many lines) how can I change it so that it returns the reg number once?

Can you help?

airwalk_90

"it has to contain one of those!"

But it needs to contain both of those!!

Kimbot

airwalk_90 wrote:

"it has to contain one of those!"

But it needs to contain both of those!!

THen if it needs to contain both.. change it to

select registrationno
from tbldetails
where Monthend = 'XXX'
and Business = 'yes'
and Business = 'no';

airwalk_90

I am currently using:

select registrationno
from tbldetails
where Monthend = 'Feb'
and Business = 'yes'
and Business = 'no'
and registrationno > 50
order by registrationno;

I am recieving nothing back even though i can see that someone entered both 'yes' and 'no' for Feb...

RosieJoe

select registrationno
from tbldetails
where Monthend = 'Feb'
and Business = 'yes'
and Business = 'no'
and registrationno > 50
order by registrationno;

The reason you are not getting any information from the above SQL is because you are looking for Business = Yes and No. Needs to be an or statement, or as you where told earlier in ("Yes","No")

TBH, if the only possible values for Business are Yes or No then why bother specifying both in the select clause?

if you were to select the registration number for Jan why are you getting duplicate information back. Seems like RegNo should be a primary key and therefore duplicate rows should not be allowed.

Also, if Business was No and then they selected Yes then the row should be upated as opposed to inserting another row. If you were updating the rows then you would not get duplicate rows

Need to rethink how you are using the table

airwalk_90

The thing is: The producer can chose 'yes' and 'No' or just either 'yes' or 'No'

I'm trying to return those who entered both 'yes' AND 'no' for specific months.

DublinWriter

airwalk_90 wrote:

The thing is: The producer can chose 'yes' and 'No' or just either 'yes' or 'No'

I'm trying to return those who entered both 'yes' AND 'no' for specific months.

select registrationno
from tbldetails
where Monthend = 'Feb'
and ( Business = 'yes' or and Business = 'no')
and registrationno > 50
order by registrationno;

....also, watch out for case-sensitivity on your 'yes' and 'no' strings - you have 'no' as 'No' somewhere in one of your postings.

The business field should really be of type boolean/logical.

seamus

OK,

let's just break down the whole Business column.

In terms of general syntax and the And/OR situation:

When performing a SELECT query in SQL, all items after the WHERE statement, are matched against each row. That is, in order for a particular row to be returned, that row must match *ALL* items after the WHERE statement.

So for example,
1.

SELECT * FROM people WHERE age > 18 AND name = 'John'

This will only return rows where the age is greater than 18, and the name is exactly "John".

2.

SELECT * FROM people WHERE age > 18 OR name = 'John'

This will return rows where the age is greater than 18 OR the person's name is "John". So you will get rows where people aren't called John (but are over 18), and you will get rows where people are under 18, but are called "John".

3.

SELECT * FROM people WHERE name = 'John' OR name = 'Mary'

This will return all rows where people are called "John" or "Mary". Obviously.

4.

SELECT * FROM people WHERE name = 'John' AND name = 'Mary'

This will never return any rows. A row cannot logically have two different values for the same field. That is, an individual person cannot be called "John" and "Mary" at the same time.

Edit:

The thing is: The producer can chose 'yes' and 'No' or just either 'yes' or 'No'

I'm trying to return those who entered both 'yes' AND 'no' for specific months.

OK, then you're going to need to explain how this data appears in the database. If a producer chooses both yes and no for the one month, is this entered as two separate rows in the database, or does it appear in a single row, with the value "yesno" in the column?

RosieJoe

Correct me if I am wrong here! A Producer can in any given month can have either 1 row of data with Business = Yes, 1 row of data with Business = No or 2 rows if data with Business = Yes in 1 row and = No in another.

If you want to find data for a Producer who has selected Yes AND No for a month and the above is correct then try using:

select DISTINCT REGNO
from TABLE T1
WHERE MONTH = 'JAN'
AND (SELECT COUNT(*)
FROM TABLE T2
WHERE T1.REGNO = T2.REGNO
AND MONTH ='JAN')>1

airwalk_90

Hi there,

People are entering data of products they sell.

So every single different product they sell they are asked a 'yes' or 'no' business question. So when a person enters 2 different products there could be a yes and a no under the business column.

I'm trying to find those producer have entered a 'yes' and a 'no'.

The column headings are written below:

Category Sub Category Product Quantity Weight Business


Does this help?

DublinWriter

airwalk_90 wrote:

So every single different product they sell they are asked a 'yes' or 'no' business question. So when a person enters 2 different products there could be a yes and a no under the business column.

Airwalk dude, you really need to rethink that table design.

On the simplest level, you should only have rows present if the supplier sells something in a particular month. No rows means no sales.

But I think your whole problem might be down to an underlying bad schema design. Maybe it would be better to have three tables - supplier, products, sales.

RosieJoe

airwalk_90 wrote:

Hi there,

People are entering data of products they sell.

So every single different product they sell they are asked a 'yes' or 'no' business question. So when a person enters 2 different products there could be a yes and a no under the business column.

I'm trying to find those producer have entered a 'yes' and a 'no'.

The column headings are written below:

Category Sub Category Product Quantity Weight Business


Does this help?

Looking at this it is possible for a Produces to have 2 rows of data both set to No. Following query will work for you:

select DISTINCT REGNO
FROM TABLE
WHERE MONTH = 'JAN'
AND BUSINESS = "Yes"
AND REGNO IN (select DISTINCT REGNO
FROM TABLE
WHERE MONTH = 'JAN'
AND BUSINESS = "No")

This will report all Producers that have Yes AND No entered for the month.

I'd agree with all the suggestions of bad design as this table structure and data content is terrible!

Code above should work but it is not performant and not that nice, dont blame me blame the design

DublinWriter

RosieJoe wrote:

Following query will work for you:

select DISTINCT REGNO
FROM TABLE
WHERE MONTH = 'JAN'
AND BUSINESS = "Yes"
AND REGNO IN (select DISTINCT REGNO
FROM TABLE
WHERE MONTH = 'JAN'
AND BUSINESS = "No")

Code above should work but it is not performant and not that nice, dont blame me blame the design

Rosie...you're the sort of person who makes DBA's and Baby Jesus cry!

Don't use subqueries if you can avoid them. Here's a *much* better version of your query:

select DISTINCT REGNO, BUSINESS
FROM TABLE
WHERE MONTH = 'JAN' AND
(BUSINESS = "Yes" OR BUSINESS = "No")

RosieJoe

I do agree with you on the horrible piece of code DublinWriter, but as I did say it is crap.

Only problem with you query is that is will return all data whether it is Yes OR No . This is fine if you want to eyeball the result yourself but it is not doing what was originally asked!

DublinWriter

RosieJoe wrote:

I do agree with you on the horrible piece of code DublinWriter, but as I did say it is crap.

I do apologise humbly...I didn't realise that OP wanted businesses with both yes AND no returned for a particular month...your code is fine.

Typical me - code first, ask questions later.

RosieJoe

No problem DublinWriter!

seamus

It can be done without subqueries, by joining the table with itself

SELECT DISTINCT b1.REGNO, b1.BUSINESS, b2.BUSINESS
from TABLE b1
RIGHT JOIN TABLE b2 ON b1.REGNO = b2.REGNO AND b2.BUSINESS = 'No' AND b2.MONTH = 'JAN'
WHERE b1.MONTH= 'JAN' AND b1.BUSINESS = 'Yes'

It's essentially the same as the subquery. However if a particular REGNO has more than two entries in the table, or the table is particularly large, the subquery method may prove much quicker.

kayos

SELECT 
	D.RegistrationNo
FROM
	(
	SELECT
		RegistrationNo,
		COUNT(RegistrationNo) AS Rows
	FROM
		tblDetails
	WHERE
		MonthEnd = @MonthEnd
	GROUP BY
		RegistrationNo
	HAVING
		COUNT(RegistrationNo) > 1
	) AS D

QED

seamus

kayos wrote:

SELECT 
	D.RegistrationNo
FROM
	(
	SELECT
		RegistrationNo,
		COUNT(RegistrationNo) AS Rows
	FROM
		tblDetails
	WHERE
		MonthEnd = @MonthEnd
	GROUP BY
		RegistrationNo
	HAVING
		COUNT(RegistrationNo) > 1
	) AS D

QED

I tested that.

It doesn't necessarily capture companies that have both Yes and No entries. If there are two rows, both "Yes"es, then it sneaks in

layke

Seamus, ever think of modding an SQL forum?

kayos

seamus wrote:

I tested that.

It doesn't necessarily capture companies that have both Yes and No entries. If there are two rows, both "Yes"es, then it sneaks in

Depends on the constrants put on the table. If there is a primary key extended across registrationno, monthend and business then the scenario you have brought up can not happen. Given the imformation provided here then that would be the logical primary key on the table.

All depends on the actual make up of the table. Which I guess we have only been given a small portion of. My bad for assuming that a table would have a primary key... :rolleyes:.

Another possible way of doing this query would be something along the lines of

SELECT
	T1.Regno, 
	T1.MonthEnd
FROM
	tblDetails AS T1
	JOIN tblDetails AS T2 ON (T1.RegNo = T2.RegNo AND 
				T1.MonthEnd = T2.MonthEnd AND 
				T1.Business != T2.Business)

This would ensure that only the regnos that have different business values for the same monthend would appear. Of course we could have some crap data in the business column like 'Maybe' or a NULL value in which case this is again wrong.

Any way lots of possible solutions to the problem but with out the full facts we can only guess as to what the best answer would be. Personnally I cry when I see distinct used in any query, its a sign of poor schema design and/or a poorly written query.

If you have a situation where you can have the same regno with the same monthend with the same value in the business column then the designer of that database should be taken outside and beaten silly. Which I think they should be any way for actually storing a boolean value as text. Not having what seems based on the information given the logical primary key would just mean changing the word "silly" for "senseless".

Best thing to do is actually provide the DDL including PK and any unique constraints for the table at this stage if we want to get that picky..

airwalk_90

RosieJoe, that works like a charm - thank you.

I know the tables etc are a bit all over the place- but thats more to do with different people adding to the project as they go along more than anything.

Thanks again.