How To Count Matches Between Two Tables?

Nov 24, 2007

Hello. I'm quite new to SQL so have included as many details as i can think of here. The scenario is a wordsearch style puzzle. The user can select their answers in any order and these answers are stored in a table. I need help with the UPDATE statement to compare the users answers against the correct answers for that puzzle.
(Note: In the actual scenario there will be 10-15 answers per grid, but i have reduced the number to make testing easier - hopefully the code for a working UPDATE statement will be scalable to account for grids with different numbers of answers etc.)

The Tables:
-- These are the correct answers for a given grid (gridid).
-- Due to the nature of the puzzle, answers can be in any order.
-- Each level may contain one,two,or no 'bonusanswers' which are harder to find, so these are scored separately so bonus points can be awarded.
CREATE TABLE correctanswers
(
gridid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
answer1 char(15),
answer2 char(15),
bonusanswer1 char(15),
bonusanswer2 char(15)
)

-- These are the user submitted set of answers 'answerid' for level 'gridid'.
-- Answers may be submitted in any order.
CREATE TABLE useranswers
(
answerid smallint IDENTITY(1,1)PRIMARY KEY CLUSTERED,
gridid smallint,
firstanswer char(15),
secondanswer char(15),
thirdanswer char(15),
fourthanswer char(15),
)

-- A user (userid) submits their answers which get stored as answerid.
-- This table shows the scores for each set of answerid's the user has submitted.
-- A high score table for both the individual user, and all users may be created using this table.
CREATE TABLE userscores
(
userid smallint,
answerid smallint,
mainmatches smallint,
bonusmatches smallint
)

The Test Data:
-- sample test data
-- 2 users userid's '1' and '2' each have two goes on level1 (gridid 1)

-- correct answers for gridid 1
INSERT INTO correctanswers (answer1, answer2, bonusanswer1, bonusanswer2) VALUES ('cat','dog','rabbit','elephant')

-- user submitted answers for gridid 1
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','rabbit','horse','cow')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'dog','cat','elephant','horse')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'rabbit','cat','elephant','donkey')
INSERT INTO useranswers (gridid, firstanswer, secondanswer, thirdanswer, fourthanswer) VALUES (1,'horse','cat','dog','sheep')

-- scores for users attempts - columns 3 and 4 needs calculating
INSERT INTO userscores VALUES (1,1,null,null) -- one main answer and one bonus answer, so null,null should be 1,1
INSERT INTO userscores VALUES (1,2,null,null) -- two main answers and one bonus answer, so null,null should be 2,1
INSERT INTO userscores VALUES (2,3,null,null) -- one main answer and two bonus answers, so null,null should be 1,2
INSERT INTO userscores VALUES (2,4,null,null) -- two main answers and no bonus answers, so null,null should be 2,0

I have included the correct new table values for the sample data - basically filling in the two null fields in the 'userscores' table.

I haven't used SQL much but from the little i know then i think the answer will include JOIN, COUNT and IN statements as part of the UPDATE statement...but i haven't a clue where to start with the order/logic etc.

I have looked for sample solutions myself but all the examples i have found so far are to do with exact matches between two tables, whereas in my scenario i need to know how many matches, irrelevant of the order.


Many thanks.
Roger

View 6 Replies


ADVERTISEMENT

Count Not Retunring A ZERO When Not Matches Are Found

Sep 13, 2005

I am pretty new to writing my own queries in SQL and am tied to Access unfortunately. Here is my query as it stands:

SELECT Count(*) AS COUNT_ACT_2_ATTEND
FROM [Main Table]
WHERE ((([Main Table].ACT_2_ATTEND)="Always" Or ([Main Table].ACT_2_ATTEND)="Frequently" Or ([Main Table].ACT_2_ATTEND)="Sometimes" Or ([Main Table].ACT_2_ATTEND)="Never" Or ([Main Table].ACT_2_ATTEND)="N/A"))
GROUP BY [Main Table].ACT_2_ATTEND;

However, if there is not match it won't retun a zero, it just returns no result and so I cannot tell which counts correspond to which matches

Does that makes sense?

View 2 Replies View Related

Count Need To Bring Back A Zero When No Matches

Sep 13, 2007

I am trying to do a calculation with the below query and it works long as d.closegoal has values and d1.opengoal has values but the problem is when there is no count for either, I need to bring back a value of zero if there are no matches. Since I am using it in an outer select statement for a calculation it not bringing anything back because of no matches.
This is my code:

select d.lwia,

cast((d.closegoal + d1.opengoal) as float)denominator

from

(

select yg.lwia,

cast(count(yg.appid)as float) closegoal

from dbo.wiayouthgoals yg

where yg.lwia = @RWB

-- Attained a goal in the timeframe timely or untimely

and ((convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthattaindate, 101)) <= @EndDte) -- Parm date for end of time frame needed

-- Goal due but not attained

or (convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) >= @BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),yg.youthgoalanniversary, 101)) <= @EndDte -- Parm date for end of time frame needed

and yg.youthattaingoal <> 1))

group by yg.lwia

)d,

(

-- Closure with open goal

select cast(count(yg.appid)as float) opengoal

from dbo.tbl_caseclosure cc,

dbo.wiayouthgoals yg

where yg.appid = cc.col_idnum

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) >= @BeginDte -- Parm date for beginning of time frame needed

and convert(smalldatetime, convert(varchar(10),cc.col_closuredate, 101)) <= @EndDte -- Parm date for end of time frame needed

and yg.youthattaindate is null

and yg.lwia = @RWB

group by yg.lwia

)d1

)d2

View 3 Replies View Related

Count From Two Tables

Jan 4, 2015

I have two tables. I have three columns DEPARTMENT_NAME, LAST_NAME, JOB_ID.

DEPARTMENT_NAME is from table

1. JOB_ID and LAST_NAME are from table 2.

I would like to count the number of employees for each DEPARTMENT_NAME and I want to be displayed DEPARTMENT_NAME, LAST_NAME, JOB_ID. But all I can do is to find the number of employees for each job_id. One DEPARTMENT_NAME has more than one JOB_ID.

Here is my code:
SELECT E.JOB_ID, D.DEPARTMENT_NAME, COUNT(E.LAST_NAME)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
GROUP BY E.JOB_ID, D.DEPARTMENT_NAME;

View 2 Replies View Related

Count From Two Tables.

Oct 10, 2007

What to know if it can be done.

Have a main table with the user id's in it it and i want to join it to two other tables. One being a downloads table and the second being a searches tables, will join them both via the user id's. What i would like to do is count the rows from both the and searches tables and return each value next with userid.

e.g

User ID, Downloads, Searches

View 5 Replies View Related

Two Tables And Count

Jul 20, 2005

I have two tables in my DB for an e-commerce app. This allows for 1 order tohave multiple products. I want to select the OrderID from the Orders tablebut only when all the corresponding records in the OrderDetails table allhave their Despatched value = 0. Can anyone offer any help please?Simplified tables below.Orders-------------OrderIDUserIDOrderNumberOrderDateOrderDetails-------------OrderDetailsIDOrderIDProductIDDespatched

View 4 Replies View Related

Several Matches In If

Apr 17, 2007

i dont know why this has slipped my mind but how would i say,

if @test = 'a' or @test = 'b' or @test = 'c'

in a shortted way?

View 1 Replies View Related

How Do I Join Two Tables To Get A Row Count?

Jan 10, 2007

I have two tables: Thread and Reply and they both have a field called UserID
I need to know the number of rows in both tables where UserID="Chris"
I can do this with two stored procedures and add the results together:
SELECT COUNT(*) FROM Thread WHERE Thread.UserID='Chris'
SELECT COUNT(*) FROM Reply WHERE Reply.UserID='Chris'
but there must be a better way.  Can this be written as one stored procedure with some sort of join?
Thanks, Chris

View 3 Replies View Related

Count From Multiple Tables

Jan 31, 2005

I have 4 tables
One is a user table and the other three contain records for the users. They all have a USERNAME column
I would like to get a count of records for each table grouped by USERNAME

My output would be:
username,totalFrom1,totalFrom2,totalFrom3

Thanks For the help!

View 2 Replies View Related

No.Of Tables Count....URGENT

Feb 14, 2000

Hi ,
Is there anyway I can get to know the total no of tables on a database

Bindu

View 2 Replies View Related

Count Rows In All Tables In A Db

Mar 1, 2001

Hello,
Do anyone knows how can i count rows in all the tables in a database.
With select count(*) I can count rows only from one table .
TIA.

View 2 Replies View Related

Count On Inner Join Tables

Jul 23, 2003

Hi all....

Have a bit of a dilema involving getting a count on an inner join table select.

The projects (WU_Title) are listed in one table (BPI_Upload) and the second table (BPI_ProjectFeedback) holds multiple rows that are added via form where PF_Project is the same as WU_Title.

Here is the current select I am using...
---------------------------------------

SELECT t1.FileID, t1.WU_Title, t1.WU_Start, t1.WU_End, t1.WU_ProjectStatus, t2.PF_Project
FROM dbo.BPI_Upload t1 INNER JOIN dbo.BPI_ProjectFeedback t2
ON t1.WU_Title = t2.PF_Project
WHERE t1.WU_ProjectStatus = 'Completed'

--------------------------------------

This presently returns:
----------------------------------
ID(1)Project(t1) ID(2) Project(t2)
1 project_a 1 project_a
1 project_a 2 project_a
1 project_a 3 project_a
---------------------------------

What I need is to return only the title and a count from the second table of how many times the title occurs there.

Ultimately ....
Title[project_a] Count[3]

I am having difficulty with where to place the COUNT() within the select.

Any suggestions would be appreciated.

Thanks...

View 7 Replies View Related

Get Row Count From System Tables

Apr 22, 2002

Does anyone know how to get the row count of a user table by using the system tables.
There is no guarantee that these user tables will have any indexes - so I can not use the sysindexes table to count the rows in a clustered index.

Is there another way?

View 1 Replies View Related

SQL - Count, Sum + Group By On Three Different Tables.

Jan 18, 2005

Hello To All @ Dev Shed,

I have three tables, say, A, B, and C. Both B and C contain/reference the primary key of A, ie B.a_id, and C.a_id. Multiple rows in B and multiple rows in C can have the same a_id. C and B have no relationships and essentially independant of each other.

I'm trying to find a single query (to prevent having to rewite function interfaces) that can return all the fields of A, the sum of a feilds in B, B.cost, where B.a_id = A.a_id and finially the count of C.a_id 's for where C.a_id = A.a_id.

The query I've been worlking on so far is as follows,

SELECT A.name, A.a_id, SUM (B.cost), COUNT ( DISTINCT C.a_id)
FROM A LEFT JOIN B USING (a_id) LEFT JOIN C USING (a_id)
GROUP BY A.a_id

This produces the correct result for the count of C.a_id (thanks to a DISTINCT) but the sum of B.cost is out by the factor of count C.a_id. I can see why this is happening but have completely run out of ideas. Group by on two columns may be? A strategically placed subquery? Is is even possible?

Any help would be much appreciated. Thanks in advance for your time.

Peter

PS I'm new to this forum thing so if you want the unsimplified query with all the full names just let me know and I'll post it.

View 2 Replies View Related

Help In Joining Three Tables For COUNT

Jan 31, 2005

Hi,

I have three tables - clients, messages, orders. clientid is the primary key for clients and clientid is present in both messages and orders as foreign key. I want to get clientid, messagecount, and ordercount, that is, for each client I want to get number of messages posted by the client and number of orders placed by the client.

To get clientid, messagecount I do this:

select a.clientid, count(b.clientid) as messagecount
from clients a left outer join messages b on a.clientid = b.clientid
group by a.clientid

To get clientid, ordercount I do this:

select a.clientid, count(b.clientid) as ordercount
from clients a left outer join orders b on a.clientid = b.clientid
group by a.clientid

But I am not able to get all three clientid, messagecount, ordercount in a single query. This is what I tried. But it is not giving me the correct answer.

select a.clientid, count(b.clientid) as messagecount, count(c.clientid) as ordercount
from clients a
left outer join messages b on a.clientid = b.clientid
left outer join orders c on a.clientid = c.clientid
group by a.clientid

Can any one tell me how to do it right?

Thanks.

View 2 Replies View Related

Count Date With 2 Tables?

Aug 18, 2012

I'm starting with the question but my end would be to store the results of this data so I can query it from my Access front end using between dates to determine totals.

1) How do I create a SQL view/temp table to query count totals from an MS Access 2010

2) CREATE TABLE [dbo].[tblProject](
[ProjectID] [int] IDENTITY(1,1) NOT NULL, [CAD] [bit] NULL, [RMS] [bit] NULL, [JMS] [bit] NULL,

and

CREATE TABLE [dbo].[tblProjectMilestones]([MilestoneID] [int] IDENTITY(1,1) NOT NULL, [ProjectID] [int] NULL, [ProjectSignedByCustomer] [datetime] NULL,
[ProjectCompleted] [datetime] NULL,

3) tblProject - CAD, RMS and JMS = True/False variations
tblProjectMilestones - ProjectCompleted = datetime

4) To calculate the count for each deliverable with no dates:
Select (Select COUNT (*) as CountCAD from tblProject where CAD='true') as CADNumber,
(Select COUNT (*) as CountRMS from tblProject where RMS='true')as RMSNumber

5) When I run the query between any dates, using tblProjectMilestonses.ProjectCompleted, what I would like is:

Code:
CADCount | RMSCount| JMSCount|
5 10 3

View 1 Replies View Related

SQL 2012 :: Count From Two Tables

Jan 4, 2015

I have two tables. I have three columns DEPARTMENT_NAME, LAST_NAME, JOB_ID.

DEPARTMENT_NAME is from table 1.
JOB_ID and LAST_NAME are from table 2.

I would like to count the number of employees for each DEPARTMENT_NAME and I want to be displayed DEPARTMENT_NAME, LAST_NAME, JOB_ID. But all I can do is to find the number of employees for each job_id. One DEPARTMENT_NAME has more than one JOB_ID.

Here is my code:

SELECT E.JOB_ID, D.DEPARTMENT_NAME, COUNT(E.LAST_NAME)
FROM EMPLOYEES E JOIN DEPARTMENTS D
ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
GROUP BY E.JOB_ID, D.DEPARTMENT_NAME;

View 9 Replies View Related

COUNT From Two Separate Tables

Apr 29, 2014

I have two tables

Books
-BookID
-CustomerID

Magazines
-MagazineID
-CustomerID

How would i write a single sql statement where i can get that counts how many bookIDs are listed for each custoemrID and how many magzaineIDs are listed for each customerID and have it return one table that looks like this:

CustomerID, BookCount, MagazineCount

View 3 Replies View Related

Record Count In All Tables

Jan 11, 2006

There a stored procedure or query that will count all the recordsin all my tables in a database.One of these two -exec sp_MSforeachtable N'SELECT "table" = ''?'', cnt = COUNT(*) FROM ?'select object_name(id), rows from sysindexes where indid in (1, 0)Is there a way to add columns to the second query? I would like to seehow many columns and their names as well.Thanks.

View 1 Replies View Related

Count All Records In All Tables

Apr 13, 2006

I need some help with this. I was able to count all the records in ourdatabase using the user_tables and user_tab_columns tables afterrefreshing the statistics on this database.We are doing an upgrade of a system and I will not be able to refreshthe statistics during the upgrade. I need more of a manual process ofrunning these queries.Now I do:select A.table_name, round(A.num_rows,0) as rowcount,count(b.table_name) as ColumnCountfrom dba_tables A, dba_tab_columns Bwhere A.table_name = B.table_name and A.owner in ('PS','SYSADM')group by A.table_name, A.num_rowsorder by rowcount desc, columncount descBut I can't use the num_rows anymore so I was thinking more to do this:Select A.table_name from(select count(*) from A.Table_name B where A.Table_name =B.Table_Name)from user_tableThis does not work for me since I don't know how to pass the table_namefrom the first select to the second select. The logic is there but thesyntax is not.Please help.

View 1 Replies View Related

How To Count Across Multiple Tables In A DB?

Sep 12, 2007



Thank you in advance for your assitance. I am trying to write a query that will query multiple tables for the same column. All the tables have thsi column "szF11". I am wanting something similar to this:




Code Snippet
SELECT count(ulID)
FROM (dbo.F_ACCOU_Data UNION dbo.F_AGNCY_Data UNION dbo.F_APPEA_Data UNION etc.....)
WHERE szF11 = ' '




Note: ulID is the name of a column that every table has and szF11 is also in every table.

Pseudo Code: I want to count how many ulID's (if there is a row then something is in the ulID column it is never blank) in all the tables that are listed that have a blank in the szF11 column.

I am getting a very cryptic error message and of course I can't find anything in the documentation to help me understand the error.

Thanks,
Erik

View 1 Replies View Related

How To Count Rows From Two Different Tables

Feb 29, 2008

If I have the following tables:

ID Car
1 Mazda3
2 Miata

Color Car ID
Black 1
Silver 1
Black 2

Style Car ID
i 1
s 1
touring 1




What is the simplest query that will return a result set like:

Car # of Colors # of Styles
Mazda3 2 3
Miata 1 0

View 3 Replies View Related

Get A Table's Row Count From System Tables

May 30, 2004

Anyone knows how to get a Table's Row Count from system tables?

Thanks.

View 1 Replies View Related

Select Multiple Tables Count

Oct 5, 2013

I'm trying to get the number of records from one table where a column matches another column in a 2nd table. I then need the total values of another column that it has selected.

SELECT HOLIDAY_REF].holiday_id, COUNT([BOOKING].booking_status_id) AS record_count COUNT([BOOKING].total_value) AS total_value FROM [HOLIDAY_REF] LEFT OUTER JOIN [BOOKING] ON [HOLIDAY_REF].holiday_id = [BOOKING].booking_status_id WHERE [BOOKING].holiday_id=[HOLIDAY_REF].holiday_id && booking_status_id = '330'

Table 1 HOLIDAY_REF
holiday_id | holiday_name
1 | Italy
2 | Russia
3 | Spain

Table 2 BOOKING
holiday_id | booking_status_id | total_value
1 | 330 | 2500
3 | 330 | 1500
1 | 330 | 1750
2 | 330 | 1240
2 | 330 | 5600

Results would be:
Holiday_id | holiday_name | total_value | record_count
1 | Italy | 4250 | 2
2 | Russia | 6840 | 2
3 | Spain | 1500 | 1

Not sure I'm going about it the right way.

View 3 Replies View Related

Need Help With Count Function And Temporary Tables

Nov 23, 2005

I have data like this in a two column temporary table -ID Age23586 323586 323586 223586 223586 123586 123586 123586 123586 1I need to create a temporary table that look like this:ID Age1 Age2 Age3 Age423586 5 2 2 0However, what I get is this:23586 5 NULL NULL NULL23586 NULL 2 NULL NULL23586 NULL NULL 2 NULLHere is the query that I am using...select managed_object_id, (select count(Age) where Age = 1) As Age1,(select count(Age) where Age = 2) as Age2,(select count(Age) where Age = 3) as Age3,(select count(Age) where Age = 4) as Age4into #enhancementCount from #enhancementsgroup by managed_object_id, AgeWhere's my mistake?Thanks-Danielle

View 4 Replies View Related

Quick Count Of All Tables In A Database

Sep 26, 2007

How can I get a quick count of all tables in a database? Is there a way to do this? Can the table count exclude system tables?

View 2 Replies View Related

Join Tables, Count Problems

Apr 19, 2006

Hi,

I have the following problem: I want to join 2 tables but the table that I want to join on has duplicates of the same record.

This is what my query looks like:
SELECT a.account, e.account AS Expr1, COUNT(e.ord_status) AS SentOrders, MONTH(e.datetime_added) AS Month, YEAR(e.datetime_added) AS YearFROM executionreports AS e INNER JOIN accounts AS a ON e.account = a.accountWHERE (e.ord_status = '0')GROUP BY a.account, e.account, MONTH(e.datetime_added), YEAR(e.datetime_added)ORDER BY Expr1
and the output looks like this:

1AA1AA328420061CC1CC45320061CD1CD8420061MA1MA1167320061MA1MA828420067TR7TR2420067TS7TS3696320067TS7TS2676420067TW7TW34420067TW7TW18320067UW7UW3320067VE7VE4320067YP7YP405320067YP7YP23142006TESTTEST142006
The problem is that the count is too high. This is because the account table has several entries with 1MA and 7TS for example. How can I correct it so i basically joins on a distinct set of the account table records?

Thanks,

Tom

View 7 Replies View Related

Reading Count(*) From 2 Tables In One Query....!

Aug 25, 2007

I want to read count(*) from various tables, I am trying code like that but won't work,

select count(*) as Table1.MyColumn1,
count(*) as Table2.MyColumn2
FROM Table1,
Table2

Any idea,

Thanks,

View 3 Replies View Related

Join Tables Count Problems

May 8, 2006

Help,
 
I have 2 tables one called ProjectHeader and the other called ProjectSuppliers.  There is a one to many relationships. I€™m trying to get a count of the total number of projects for a certain supplier where the Date Entered is less then 5/1/2206 with the Bid Date greater than 3/31/2206.
 
When I run the query below the count comes back wrong.  I know it€™s because the ProjectSupplier table can have multiply entries of the supplier under the same Projectid.
For example, the query below gives me a count of 206 projects for Miller in Tampa.
Where he really only has 169 projects entered.
I know you can run two queries to get the results. 
But because the data is being display on a Web page, I really would like the one SQL statement to handle this.
 






ProjectHeader Table

ProjectSuppliers Table


ProjectID

ProjectID


Bid Date

SupplierName


 

Date Entered


 

District


 

Modifed By
 
 SELECT Count(ProjectHeader.ProjectID) AS CountOfProjectID, ProjectHeader.District, ProjectSuppliers.[Modifed By]
FROM ProjectSuppliers INNER JOIN ProjectHeader ON ProjectSuppliers.ProjectID = ProjectHeader.ProjectID
WHERE (((ProjectSuppliers.[Bid Date])>#3/31/2006#) AND ((ProjectSuppliers.SupplierName)='Some Supplier') AND ((ProjectSuppliers.[Date Entered])<#5/1/2006#))
GROUP BY ProjectHeader.District, ProjectSuppliers.[Modifed By]
HAVING (((Count(ProjectHeader.ProjectID))>0))
ORDER BY Count(ProjectHeader.ProjectID) DESC , ProjectHeader.District, ProjectSuppliers.[Modifed By];

 

View 6 Replies View Related

How To Use WHERE To Find Various Matches

Apr 26, 2008

Hello.I have a select that returns some SubCategories.SELECT SubCategoryID from SubCategories SCATINNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryIdWHERE SCAT.ParentCategoryId = X Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.What code i need to write toI think .. Select * from Items AS IT WHERE IT.Subcategory = ???I don't know, anyone can help meThanks

View 4 Replies View Related

Repeating Matches

May 18, 2007

Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.

CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );

My statement

SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;

the result is

ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5

The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?

View 3 Replies View Related

IN Operation That Matches ALL Instead Of ANY?

Mar 25, 2008

A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?

I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.

View 7 Replies View Related

How To Use A WHERE To Find Various Matches.

Apr 26, 2008

Hello.

I have a select that returns some SubCategories.


SELECT SubCategoryID from SubCategories SCAT
INNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryId
WHERE SCAT.ParentCategoryId = X


Now i will to retrieve all rows from a Table called Items where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.

What code i need to write to ?



I think .. Select * from Items WHERE Items.Subcategory = ???

I don't know what code i need to type, anyone can help me



Thanks

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved