• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with a query?

 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Can someone help me with a query? I have 2 tables.

table1
-------
id
name
item

table2
-------
id
item

table1 is a link table where the id's can be the same but the names will be different for each id. table1.item = table2.item. What I need to do is count the number of times table1.item exists of a specific item for a given id. Does that makes sense? So pretend I have the following data in table1.



What I need is a query that returns basically:

steak 1
fish 2
salad 2

I can think of a hundred ways to do this in code, but I need it to be SQL as much as possible. Thanks.
 
Eric Pascarello
author
Rancher
Posts: 15385
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Isn't it just

SELECT item, COUNT(item) AS totCount
FROM table1
GROUP BY item


[edit]Not sure how Table2 plays into your question - will reread![/edit]
Eric
[edit]It doesn't. I don't know why I even bothered with it - Gregg[/edit]

[ September 08, 2005: Message edited by: Eric Pascarello ]
[ September 08, 2005: Message edited by: Gregg Bolinger ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually Eric, I think that works.
 
Stephen Boston
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
His Item in table one will be a number in table two where he will have a join to pull the Item (name) from table 2 based on the table 1 item number.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Stephen Boston:
His Item in table one will be a number in table two where he will have a join to pull the Item (name) from table 2 based on the table 1 item number.


Actually, no it won't. I didn't create the database and the item in table1 is the item in table2, not the id.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, part 2 of this question. Let's say I have another table

OfficerTable
-----------
id
name
item

Now what I need to do is do the same thing for this table that I did for the other, but the total for each item needs to be count(table1.item) + count(OfficeTable.item).

Strange I know, but any suggestions?
 
Stephen Boston
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hmmm... How does this work for you?

 
Stephen Boston
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Cleaned it up a bit.


[Edited from Left Outer to Full Outer [/Edited]
[ September 08, 2005: Message edited by: Stephen Boston ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think that is almost there except for one thing

ON T.Item = O.Item

The OfficerItem won't always have the same values as Table1.item. So it loses some of the count because of that criteria.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Man, that is close though. Thanks for the shove. Getting there...
 
Stephen Boston
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Gregg, change the left outer join to a full outer join and that should get it all. Sorry about that.
[ September 08, 2005: Message edited by: Stephen Boston ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Stephen Boston:
Gregg, change the left outer join to a full outer join and that should get it all. Sorry about that.

[ September 08, 2005: Message edited by: Stephen Boston ]


You are my alltime favorite hero!!! You rock! Thanks a bunch.
 
Stephen Boston
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Java I'm still working on, but SQL I got a good handle on.
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ok, one more thing Stephen. The 2 values from OfficerTable that don't exist in Table1 are coming up as NULL, so my returned data looks like:



Any ideas?
 
Eric Pascarello
author
Rancher
Posts: 15385
6
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
SELECT ISNULL(T.Item,O.Item) As Item,.........
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Eric Pascarello:
SELECT ISNULL(T.Item,O.Item) As Item,.........


That put the count total in for the NULL values and removed my Total column. I need the output as shown in my last post with the correct values for NULL.
 
Stephen Boston
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Eric beat me to it.


[ September 08, 2005: Message edited by: Stephen Boston ]
 
Gregg Bolinger
Ranch Hand
Posts: 15304
6
Chrome IntelliJ IDE Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Stephen Boston:
Eric beat me to it.



Got it, thanks. Works great.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic