Jump to content


Photo

Confusing Query Help - Multiple Entries

sqy mysql

  • Please log in to reply
2 replies to this topic

#1 herghost

herghost

    Advanced Member

  • Members
  • PipPipPip
  • 699 posts

Posted 30 January 2013 - 06:13 AM

HI guys

I am having some issues getting the results I want from a query.

Basically I have two tables. To keep things short and sweet ill use made up values

table a contains the field ID,Week
table b contains ID and type

Type can be either a 1 or a 0 and and the ID will match table A.

Table b can contain more than one entry for the same ID on table A with different types.

What I am trying to do is return all the ID's in table B that only contain a type of 0, for a specific week in table a. So if table B contains:

ID Type
1 1
1 0
2 0
2 0
3 1
3 1

I would only need to return ID 2

I have tried something like this

select a.id, b.id
from b inner join
a on b.id = a.id
where a.week = '22' and b.type = 0 

This issue is that it will return ID 1 and 2 as ID 1 contains a 0?

I hope this is clear enough!

Thanks for any pointers or help

#2 PFMaBiSmAd

PFMaBiSmAd

    Advanced Member

  • Staff Alumni
  • 16,767 posts
  • LocationColorado, U.S.A.

Posted 30 January 2013 - 06:45 AM

You would need to post data from both tables that reproduces the problem and the actual query to get help. You are either joining on the wrong column or the data in the table isn't what you imply it is.
Signature: (not a comment about anything you posted unless specifically indicated)
Debugging step #1: To get past the garbage-out equals garbage-in stage in your code, you must check that the inputs to your code are what you expect.

Programming is just problem solving, but it is done in another language. You must learn enough of the programming language you are using to be able to read and write code.

#3 kicken

kicken

    Wiser? Not exactly.

  • Gurus
  • 2,709 posts
  • LocationBonita, FL

Posted 30 January 2013 - 12:05 PM

Two possible options I can think of, the first being a sub-query for the totals:
http://sqlfiddle.com/#!3/96e2b/3/0
SELECT
  *
FROM tblA
INNER JOIN (SELECT ID, COUNT(*) as rows FROM tblB GROUP BY ID) as total ON tblA.ID=total.ID
INNER JOIN (SELECT ID, Type, COUNT(*) as rows FROM tblB GROUP BY ID, Type) as groupTotal ON tblA.ID=groupTotal.ID
WHERE
  groupTotal.Type=0 
  AND total.rows=groupTotal.rows

The second being a query using GROUP BY and a HAVING filter:
http://sqlfiddle.com/#!3/96e2b/6/0
SELECT
  tblA.ID as aid,
  tblB.ID as bid
FROM tblA
INNER JOIN tblB ON tblA.ID=tblB.ID
GROUP BY
  tblA.ID,
  tblB.ID
HAVING
  SUM(CASE WHEN tblB.Type=0 THEN 1 ELSE 0 END)=COUNT(tblB.ID)


Recycle your old CD's, don't trash them!
Did I help you out?  Feeling generous? I accept tips via Paypal or Bitcoin @ 14mDxaob8Jgdg52scDbvf3uaeR61tB2yC7




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

Cheap Linux VPS from $5
SSD Storage, 30 day Guarantee
1 TB of BW, 100% Network Uptime

AlphaBit.com