Query Help (Distinct and Left Join)

Question

0.00/5 (No votes)

See more:

Hello guys. Good day!

Ive created a query but it seems that there are many excess data.

here's my query:

select distinct ticketstatus.tickettype, stat1.ticketcount as onhand, stat2.ticketcount as pending, stat3.ticketcount as sold from ticketstatus 

left join (Select TicketType, TixStatus, Count(TicketType) as TicketCount from TicketStatus WHERE ConcertTitle = 'Blink182 (Get in the Phil)' and tixstatus = 'OnHand' group by TicketType, tixstatus) stat1 on stat1.tickettype = ticketstatus.tickettype

left join (Select TicketType, TixStatus, Count(TicketType) as TicketCount from TicketStatus WHERE ConcertTitle = 'Blink182 (Get in the Phil)' and tixstatus = 'Pending' group by TicketType, tixstatus) stat2 on stat2.tickettype = ticketstatus.tickettype

left join (Select TicketType, TixStatus, Count(TicketType) as TicketCount from TicketStatus WHERE ConcertTitle = 'Blink182 (Get in the Phil)' and tixstatus = 'Sold' group by TicketType, tixstatus) stat3 on stat3.tickettype = ticketstatus.tickettype

when i use it on my program it shows all the tickettype, I want to show the ticket type for the particular concert like in my query i assigned one which is on my db. please help me. i cant spot what i need to change.

thank you guys in advance. have a great day forward. :)

Posted 20-Apr-12 1:54am

Ariel Riyo

Add a Solution

Comments

Bernhard Hiller 20-Apr-12 8:36am

Why do you think that there are "many excess data"?
"tixStatus" seems to be a character type in the database, but it should be an enumeration/integer type.
Also "ConcertTitle" should be a foreign key (ID in a "Concerts" table), so the title can be changed without too big problems...

Nelek 20-Apr-12 17:35pm

OP answered to you but posting a new comment, so you might have missed the notification. Have a look.

Ariel Riyo 21-Apr-12 0:26am

thanks! i forgot. sorry.

Nelek 21-Apr-12 19:27pm

No problem :) It happens to me quite oft as well :P

Ariel Riyo 21-Apr-12 0:26am

Hey, thank for your response. It all display the ticketType but it does display the ticket count on the desired ticketType where there is the ConcertTitle Supplied. If its not that concertTitle then itll display null on the count but all of the TicketType supplied is displayed even in the other concertTitle.

Ariel Riyo 20-Apr-12 8:59am

Hey, thank for your response. It all display the ticketType but it does display the ticket count on the desired ticketType where there is the ConcertTitle Supplied. If its not that concertTitle then itll display null on the count but all of the TicketType supplied is displayed even in the other concertTitle.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Jörgen Andersson · Answer 1 · 2012-04-23T09:48:00

Nothing strange there, using left joins means that you get ALL selected rows from the first table.
I guess what you actually want is a pivot. But as you don't state what version of sql-server you have, I can't be sure it's supported.

But don't worry, it's easy enough to do with a CASE WHEN

Try this or a variation of it:

SQL

SELECT  TicketType
       ,Sum(CASE TixStatus WHEN 'OnHand' THEN 1 ELSE 0 END) AS OnHand
       ,Sum(CASE TixStatus WHEN 'Pending' THEN 1 ELSE 0 END) AS Pending
       ,Sum(CASE TixStatus WHEN 'Sold' THEN 1 ELSE 0 END) AS Sold
FROM    TicketStatus
WHERE   ConcertTitle = 'Blink182 (Get in the Phil)'
GROUP BY TicketType