Simple SQL Query Or Design Problem?

Nov 21, 2005

Dear All

I have posted this a few days ago, but probably didn’t describe it properly so I’ll attempt it again.

I’m wondering if it’s the design that needs to be changed or I simply can’t put this together.

I have 3 tables.

1. people (peopId, peopFName, peopSName etc.)
2. codes (codeId, codeName)
3. codedPeople(codePeopleId, peopId, codeId)

Codes represent different skills that people might have, like:

Technology-Founder
Technology-Chief Executive Officer
Technology-Systems Designer
etc.

people, clearly holds data about people.

CodedPeople holds data on which people in the db are coded what skills.

What I need is a query that returns all distinct people records and takes a number of codeNames as input.

So if I have person1 and person2 in the db and they are coded as:

Person1 is a t-Founder
Person1 is a t-CEO

Person2 is a t-Systems Designer

Query 1, looking for all t-CEO
Result 1: person1

Query 2, looking for all t-CEO AND t-Founder
Result 2: person1

Query 3, looking for all t-CEO OR t-Systems Designer
Result 3: person1, person2

Query 4, looking for all t-Founder AND t-Systems Designer
Result 4: -


I have:

[ code]
SELECT people.peopId, peopFName, peopSName, peopPhoneHome, peopPhoneMobile, peopEmail, codes.codeId, codename
FROM people INNER JOIN codedPeople ON people.peopId = codedPeople.peopId
INNER JOIN codes ON codes.codeId = codedPeople.codeId
WHERE ( parameter )
ORDER BY peopSName, peopFName
[ /code]

And I add in parameters from my front end to the WHERE clause, like in the example:

Parameter = ( codeName LIKE 't-CEO' ) OR ( codeName LIKE 't-CFO' )


This works fine when using OR but doesn’t work when trying to use AND in a sense of a person is coded with skill1 AND skill2 AND skill3 etc.

Any idea how to implement what I need to achieve?

View 14 Replies


ADVERTISEMENT

Simple Database Design Question

Jan 18, 2008

I'm designing a database with 3 tables called Function, Test and Scene.

A Function has multiple Tests, but a Test has only one Function. A many to many relationship exists between Test and Scene therefore I need a junction table between these two tables - giving 4 tables in total. The Test table would store a foreign key, the primary key of the Function table.

There is a problem with design though and that is that Functions and Scenes are actually defined before the Test is defined. Therefore it should be possible to create a Function and add to id its Scenes, before Tests have been defined. In other words, Scenes are as much a part of a Function as they are of Tests. Tests are in fact only of relavence to testers. Anyway, to satisfy this scenario, a Junction box is also needed beween Function and Scene. This creates a loop between all tables.

Is this a good approach? Any other suggestions or advice on the matter? Any advice regarding data integrity?

Thanks,

Barry

View 1 Replies View Related

Database Design/query Design

Feb 13, 2002

Ok, I'm doing a football database for fixtures and stuff. The problem I am having is that in a fixture, there is both a home, and an away team. The tables as a result are something like this:

-------
Fixture
-------
fix_id
fix_date
fix_played

----
Team
----
tem_id
tem_name

-----------
TeamFixture
-----------
fix_id
tem_id
homeorawayteam
goals

It's not exactly like that, but you get the point. The question is, can I do a fixture query which results in one record per fixture, showing both teams details. The first in a hometeam field and the second in an away team field.

Fixture contains the details about the fixture like date and fixture id and has it been played

Team contains team info like team id, name, associated graphic

TeamFixture is the table which links the fixture to it's home and away team.

TeamFixture exists to prevent a many to many type relationship.

Make sense? Sorry if this turns out to be really easy, just can't get my head around it at the mo!

View 2 Replies View Related

Knowledgeable Yet Simple Book For Database Modelling Or Database Design

Aug 16, 2007

Hi All,Can u please suggest me some books for relational database design ordatabase modelling(Knowledgeable yet simple) i.e. from which we couldlearn database relationships(one to many,many to oneetc.....),building ER diagrams,proper usage of ER diagrams in ourdatabase(Primary key foreign key relations),designing smallmodules,relating tables and everything that relates about databasedesign....Coz I think database design is the crucial part of databaseand we must know the design part very first before starting up withdatabases.....Thanks and very grateful to all of you....Vikas

View 3 Replies View Related

Help With A Simple Query

Oct 1, 2007

I am trying to make a single display page for an author's books.the books page only displays books of a type "type" (novels, non-fiction, etc)I would like to make it so that it can also show all books if "type" isn't selected.  I THOUGHT the string would look like this: <asp:SqlDataSource ID="SqlDSBooks" runat="server" ConnectionString="<%$ ConnectionStrings:csK2Reader %>"
SelectCommand="SELECT * FROM [Books] ( If @Type <> "" then WHERE ([Type] = @Type)) ORDER BY [SortDate] DESC"> But it doesn't seem to want to work.  I get a "server tag is not well formed" error. 

View 2 Replies View Related

I Need Help With A Simple Query

Jan 7, 2008

I have a table called "member" that has two fields (amongst others) called "firstname" and "city"I want to return a list that sorts people who live in the same city, but does not return people who do not have a city in common.For example if this is my data:Karen - ParisMike - RomeTim - Dallas

Jim - ParisChris - DallasJohn - ManhattanSarah - OrlandoDavid - DallasThe query would return this:Dallas - ChrisDallas - DavidDallas - TimParis - JimParis - KarenI have this SQL so far:  Select city, firstname From member Order By city, firstnamebut it includes all the cities including those that only have 1 resident.Can someone tell me how to do this?Thanks a lot,Chris

View 2 Replies View Related

Simple Sql Query

Mar 18, 2008

hi,i have three fields in the database tablep1                    p2       rank10%               20%          3                        21%                40%          2now i am passing the input as 15%...this 15% is related to 3rd rank.. so i need the o/p rank as 3..if the p1 and p2 datatypes are varchar..then how we can we write the query for that one tgo get the rank as 3.Ramesh

View 2 Replies View Related

Help With Simple SQL Query

Apr 21, 2005

Hi All
Can anyone tell me what this simple SQL query would be: 
Find all the words "black", "dvd" and "player" from any of the fields "Product", "Brand" or "Description".
 
Many thanks
 
accelerator

View 2 Replies View Related

Simple Query Help...

May 15, 2005

I am trying to select players from a database based on the userid not having any user's name in it with an order by ASC. I basically want all the players that haven't been taken. Here is what I came up with and it gives me empty.
 
strSQLA="SELECT UserID, playerName from Allplayers WHERE UserID = '' ORDER BY playername ASC"

View 4 Replies View Related

Simple SQL Query -- Perhaps You Can Help

May 28, 2006

I hope it's considered appropriate to post a SQL query question here; it's not I'll gladly post this question elsewhere.  That having been said, I've run into a simple but quite tricky SQL query and was wondering if the community might be able to help.  Here's what's going on:
I have a "Documents" table.  Let's call it DI have a "Benefits" table.  Let's call it B.There is a many-to-many relationship between D and B so I have a third table to store the relationships.  Let's call that table BD.
When I create a document it creates a row in D.  Let's call that D1, D2...DN.  When I create a benefit it creates a row in B.  Let's call that B1, B2...BN
When I associate a document to a paritcular benefit, let's say that if D1 and B2 are associated, that I created row B2-D1 in my BD table.  Now here's what my challenge is.  Suppose I have these entries in my tables:
D: D1, D2, D3B: B1, B2, B3
Table BD:B1-D1B2-D1B2-D2
When the user goes to add documents to benefit B1, I want to show the user only documents that are not yet added to B1.  In this case, I want to show the user D2 and D3 but not D1 since that one's already added.  How do I write a SQL query to do this?
Here's what I've been using, but it shows too many results (e.g. included D1, D2, D3)
SELECT D.*, BD.*FROM Documents AS d LEFT JOIN rel_BenefitsDocuments AS bd ON d.doc_id = bd.doc_idWHERE (benefit_id <> @benefit_id OR benefit_id IS NULL)
Any thoughts?

View 2 Replies View Related

Simple Query Help

Mar 7, 2003

Hi,

I want to write a sql query for an asp page which will display only unique rows from the specified column along with the number of count for each unique row.

Example:

Table that I want to query

Last Name || First Name
Gates || Bill
Boyce || Mike
Gates || Bill
Gates || Phil


Results I want:

Last Name || First Name || Count
Gates || Bill || 2
Boyce || Mike || 1
Gates || Phil || 1

Thanks a lot,
Heta

View 2 Replies View Related

Simple Query

Aug 21, 2004

Hi,

I have a very simple question:

declare @treshold int
set @treshold = 10

SELECT
dbo.fn_Calc(t.column1) as calc,
t.column2
FROM
mytable t
WHERE
dbo.fn_Calc(t.column1) > @treshold


I can't think of a way to get rid of the function call in the WHERE clause.
Is this actualy a problem ?

I mean does realy the function fn_Calc execute 2 times in this statement and isn't this a performance issue then?

thanks

View 2 Replies View Related

Simple Query

Jan 17, 2002

Hi,

I am new to SQL and this question may be most easiest to many of you. Here is what I need.

I have two identical tables (exactly the same in structure) having a compound primary key with a combination of 3 columns. Can someone give me the most efficient query that fetches all the rows from table1 that are not in table2.

Thanks in advance for your help
Kevin

View 1 Replies View Related

Query Help Please Max() Simple?

Oct 21, 2006

This is my query...I am attempting to only return the records for each
sales_contact with the greatest issue_id

select
sc.sales_contact_id
, idd.issue_id
, sr.code

from
sales_contact sc
, invoice i
, invoice_line_item il
, sales_region_special_section srss
, sales_region sr
, issue_date idd

where
sc.sales_contact_id = i.sales_contact
and i.invoice_id = il.invoice
and srss.sales_region = sr.sales_region_id
and il.issue_date = idd.issue_date_id
and srss.invoice_line_item = il.invoice_line_item_id

order by
sc.sales_contact_id


================

heres some of the result set I want only the records with the greatest id
for each unique sales contact...

AF85F32E-8E34-4C40-9468-00148A34E903, 41, N
AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 70, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 34, BC
B44C914E-6001-40CE-8AB6-0126BD572D45, 25, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 26, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 24, NW
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45 , 28, NW



this would be an ideal result set using the data above...note there are
situtations where there are n* for a greatest issue id.

AF85F32E-8E34-4C40-9468-00148A34E903, 42, N
5D26328A-192B-4E4E-9B34-010C4E077CF8, 77, N
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, HC
B44C914E-6001-40CE-8AB6-0126BD572D45, 28, NW


thanks in advance for your help.

View 1 Replies View Related

Simple Query

Jan 7, 2005

This should be easy, I'm just having a brainfart at the moment, can't remember how to do this:

Say I have a bunch of records in a table, with an ID field as Key. I want to return the count of times each ID shows up, so for the following example data:

Code:


-----
| ID |
-----
| 1 |
| 2 |
| 1 |
| 4 |
| 1 |
| 4 |
------


Would return something like:

Code:


----------------
| ID | Count |
----------------
| 1 | 3 |
| 2 | 1 |
| 4 | 2 |
-----------------


This should be obvious but for some reason I can't see it.

When this is done I'm going to join in another table to get a name based on the ID with the highest count. I believe it's faster to do a TOP 1 here rather than after the join, but I want to verify that while I'm at it also.

View 3 Replies View Related

Simple Query

Sep 26, 2006

OLD_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____0

NEW_TABLE
CustomerPost_ClosestStore1_ClosestStore2_ClosestStore3
00501_____9339_____9193_____9445
02151_____9161_____9838_____9185
02917_____9788_____9105_____9626
06443_____9644_____9102_____9286
43434_____9258_____9496_____1111


(end result)
FINAL_TABLE
43434_____9258_____9496_____1111
I have two tables ... I am trying to get only the rows that are different.

View 2 Replies View Related

Simple SQL Query

Sep 19, 2005

Hi folks,

I am a complete newbie to SQL. I have a simple query to make in SQL, but I'm not being able to construct the statement properly. I've already tried googling this info, but with little luck. If someone could help me here, I'd be very thankful.

The problem:

I have a table with a list of questions. All questions have a serial number. The query has to return whether a particular question is the last one in the table or not (according to serial no.)

Logically, this is the query -


Code:


select islast = 0
if count(*) from question where serialno > $serial != 0
else
select islast = 1;



The '$serial' part will get replaced dynamically before query execution through another program.

Any idea how to code this in valid SQL? I would like to make it a simple statement and not a stored procedure. Thanks.

View 5 Replies View Related

Simple SQL Query Help

Sep 1, 2006

Hi,

This is probably a simple SQL statement question, so please bear with me.

Basically I have a CustomerMaster with the following fileds

1) CustomerID
2) CustName
3) Address
4) ResTelNo

I want retreive the CustName,Address and ResTelNo if the ResTelNo has occured in the table more than twice.

Could someone please help with this. I would like to use the same query in MS Access as well.


Thanks in advance for your help.

View 2 Replies View Related

Simple Sql Query..help Please

Sep 17, 2004

Hey.. hope someone can help, i desperatly need some assistance. I have a few query issues but first things first.. one is that i have a table of messages and users having an online conversation, so fields are msg id, subject, topic, message, user, reply to and time it was sent. Im trying to do a query that will tell me which user sent the most messages, ive tried using count and stuff like that but not really getting anywhere. It will basically have to count each time a user has spoken and then give me the name of the user that has sent the most messages, ive been playing around with stuff like
SELECT Count(*) AS Expr1
FROM Table1
WHERE User='andro8472' OR User='bumies';

That will count the times those users have spoken but cant get further

any help would be greatly appreciated really stuck at the mo..

thanks

View 4 Replies View Related

What Should Be A Simple Query

Feb 29, 2008

I'm having a bad day as I just cannot get a query to work and its doing my head in and it should be a simple query.

I'll try and explain what I need.

Have a simple query with one join, this is it so far:

SELECT DISTINCT Replace(StaffName,'''''',' ') AS StaffName,OutReason,OutStartDate
,OutEndDate,OutStartTime,OutEndTime,OutID
FROM vwOutOfOffice
LEFT OUTER JOIN vwStaffList ON OutUser=StaffID
WHERE OutComplete=0

What I'm having trouble with adding to the WHERE is as follows, I need a list of Staff who are out of the office based on the various criteria:

Example
Current date=29/02/2008
Current time=14:00

Start date---End Date----Start Time--End Time--Should Appear
27/02/2008--28/02/2008--09:00------17:00-----No (because of date)
28/02/2008--03/03/2008--08:00------17:00-----Yes
28/02/2008--29/02/2008--17:00------17:00-----Yes
29/02/2008--29/02/2008--12:00------14:00-----Yes
29/02/2008--29/02/2008--14:30------17:00-----No (because of time)
02/03/2008--02/03/2008--08:00------10:00-----No (because of date)

There may be others I've missed but as long as the dates combined with the times match correctly I need the records to appear.

Please help before I end up putting my head through the monitor.

View 2 Replies View Related

Simple Query?

Nov 29, 2004

I have a table that looks like this:
c1c2
1a
1b
1c
2a
2b
2c
2d
3a
3b
3c
3d
3e


I want a result set that looks like this:
c1c2c3
1a1
1b2
1c3
2a1
2b2
2c3
2d4
3a1
3b2
3c3
3d4
3e5

Basically, grouping/numbering the rows by column c1. The number of distinct c1 columns and distinct c1,c2 columns is varying.

View 3 Replies View Related

Simple Query

Oct 2, 2005

I have a table like

create table sample (Column1 , Column2 int)

I want to select say 3 or 4 Column1's for each Column2 with out using a cursor.

Can u help

View 1 Replies View Related

A Simple Query...plz Help

Sep 4, 2007

Hi All,

The following table is entitled "class".

ID Number NameJoin DateSex
101 Jason01/02/1996M
117 Robert06/25/1999M
113 Doug08/12/1999M
161 Marian07/30/1998F

Please let me know the SQL queries for the following questions for the above table:

1)Write a Select statement that selects the persons whose Type is "M" and who have joined in the last four years and place the result in an array. Today's date is 12/31/1999.

2) Write a Select statement that selects the persons who do not have unique ID Number and place the results in an array.

3) Write code to sort the array by Name + Join Date. Assume that the array has already been created and is sorted in the same manner as the table above. The Join Date is of type smalldatetime. The Name field should be sorted case insensitively.



Thanks for your help.

View 2 Replies View Related

Simple SQL Query

Nov 8, 2007

I have a teams table with an id col (primark key) and name col.

I also have a fixtures table. its has an Id, hometeam, awayteam and date cols.

I would like to use the Pk of the Teams table as a foreign key in the fixtures table for both the hometeam and away team cols.

How could I write the SQL to get a list of fixtures for a specific team given the ID of the team.

Many thanks for your help

View 6 Replies View Related

Need Help On Simple SQL Query

Jan 27, 2008

Hi

I am just starting out on sql programming.

I have one table with three columns

Item Name | Price |Date
Item1 | 44 |25-Jan-08
Item1 | 45 |26-Jan-08
Item1 | 43 |27-Jan-08
Item2 | 21 |26-Jan-08
Item2 | 34 |28-Jan-08
Item3 | 91 |27-Jan-08
Item3 | 98 |29-Jan-08


I want to pick latest price of all items and produce a result as below

Item1 | 43 |27-Jan-08
Item2 | 34 |28-Jan-08
Item3 | 98 |29-Jan-08


Please help me on this

Thank You
-Jag

View 3 Replies View Related

Simple Query Help

Feb 26, 2008

How do I retrieve all rows from a middle_name column that have a character length of <= 2. This table is populated with middle names and middle initials. I want to isolate the middle initials only.

View 5 Replies View Related

I Need Some Help With This Simple Query !!

Mar 10, 2008

Hi,

I have this query:

SELECT ('Section : ' + F.Section + ' ' + (SELECT S.SectionName FROM SFM_Section AS S
WHERE S.Company = F.Company
AND S.Department = F.Department
AND S.Section = F.Section ) ),
F.Variety, F.Category,
isnull(SUM(F.AreaCutCurrent), 0) AS AreaCutCurrent,
isnull(SUM(F.TonnageCurrent), 0) AS TonnageCurrent,
isnull(SUM(F.YieldCurrent), 0) AS YieldCurrent
FROM SFM_Field AS F
WHERE F.CropYear = 2007 and ActiveFlag = 1 and Section = 04
GROUP BY F.Company, F.Department, F.Section, F.Variety, F.Category
ORDER BY F.Section, F.Variety, F.Category


that returns me the following results:

Section:04 Region Haute|M0052/78|R07|1.84 |137.64 |74.804
Section:04 Region Haute|M0695/69|R01|37.71|2817.65|434.009
Section:04 Region Haute|M0695/69|R02|35.08|2282.33|523.234
Section:04 Region Haute|M0695/69|R05|21.2 |1563.01|147.579
Section:04 Region Haute|M0695/69|R09|6.52 |484.39 |74.293
Section:04 Region Haute|M0695/69|R11|0 |0 |0
Section:04 Region Haute|M1397/86|R04|8.35 |793.61 |190.141
Section:04 Region Haute|M1400/86|GS |18.03|2093.91|116.135

What should I do if I don't want the records that the sum is equal to 0 to appear in my results? (Like the 3rd row from the end)

Thanks for your help

View 4 Replies View Related

Simple Query?

Mar 18, 2008

Hello,

You probably seen this Q before but I have no idea what to search and and therefor any direction would be appreciated.

I have three columns table, two are int and one is datetime (for sorting). I would like to query all values witch are unique within the two integers, however, in some cases data looks like this:
int1 = 1
int2 = 2
and another record like:
int1 = 2
int2 = 1

this pair should be considered as a duplicate and second record should not come with the results set.

Any suggestions?

View 1 Replies View Related

Help! Should Be A Simple Query

Mar 19, 2008

Hi I've got what should be a simple query but I'm not sure how to write it.

I have a table which holds employee assignments to positions, which holds historical data.

Eg:

ID EmployeeID StartDate PositionID
--- ---------- --------- ----------
1 7663 23/1/2007 234
2 7663 28/6/2007 272
3 7663 1/02/2008 983
4 81212 2/03/2008 2888


Now, what I'm after for each employee is their current position based on the highest "StartDate" for that employee.

So from the above table I want the results:


3 7663 1/02/2008 983
4 81212 2/03/2008 2888


Can anyone help? Do I need a Group By? That clause always confuses me!!!

Thanks!!!!

View 6 Replies View Related

Simple MAX Query?

Jul 23, 2005

Hi AllI know that I am supposed to submit a schema of my table, but it is a verybasic one so I'll crack straight on with my query.In essence I have a 3 column table called STOCKTRANS, which consists ofSTOCKCODE, TRANSACTIONDATE and TRANSACTIONTYPE.Each time a user orders a stock item I insert the stock code and the datethey ordered it into this table, egSTOCKCODE TRANSACTIONDATE TRANSACTIONTYPEIVP 1-4-04 1STP 31-6-04 8KGC 8-7-04 6IVP 11-8-04 8etc etcetcWhat I want to create is a simple query that informs what the last orderdate was for a stock code. As you can see from the above example, I mayhave hundreds of the same stock code with different dates so I thought thatthe following query would work:select STOCKCODE, MAX(TRANSACTIONDATE) as 'LastOrderDate'from STOCKTRANSwhere STOCKCODE = 'IVP' and TRANSACTIONTYPE in (1,8)But it doesn't.NOTE: the reason for the transaction types of 1 and 8 is because these arethe types I want. The other types are used for credits, returns, etc.First of all the above, brings me back loads of rows when all I want is:IVP 11-8-04 << this being the last date of order for this stock item.Just as a test I removed the STOCKCODE out of the select and found that Idid get my one liner, but for some reason I was only getting the last datefor transaction types 1 only - not the last date for a combination of 1 and8 transactions.Can you help??Many thanksRgds Robbie

View 4 Replies View Related

Help With A Simple Query Please

Jul 23, 2005

I have Two Tables, TableA and TableB, both containing a common field,Feild1.How do I find all records in TableA, where Field1 is not in TableB?Regards,Ciarán

View 3 Replies View Related

Simple Query

Jul 20, 2005

I know this is super-basic, but I'm a newbie and I can't get it to work...I'm trying to call a stored procedure that has a datetime as one of itsparameters. How the heck do I get a datetime?? I'd even settle for knowing Iwas declaring variables correctly...DECLARE @Date datetime (right?)???EXEC usp_AddRequest 313,'E',@Date,'QUAL'^^^^^ <- this is the parameter that wants to be adatetimeHow do I make Date correspond to an actual date/time? How do I assign it tobe equal to SELECT GETDATE()? Why doesn't SET @DATE = SELECT GETDATE() work?And why the heck is it so hard to find the answers online?? I've Googledendlessly and found nothing...--Christopher Benson-Manica | Jumonji giri, for honour.ataru(at)cyberspace.org |

View 2 Replies View Related

A Simple Query

Apr 1, 2008



Hello there, this is my first time here,please tell me if I'm posting in the wrong place.

well I have a table with three columns: nb,date and place; a query like
(select * from table where date<'3/19/2008 11:00:00' and date<>3/19/2008 8:00:00' )
returns:
nbr date place
11 '3/19/2008 08:05:00 aa
11 '3/19/2008 08:06:00 aa
11 '3/19/2008 08:07:00 aa
22 '3/19/2008 08:08:00 ab
22 '3/19/2008 08:09:00 ab
11 '3/19/2008 10:00:00 aa
11 '3/19/2008 10:05:00 aa

My prob is that I don't need all those records: the first three records for me refer to the same object so I want just one of them, same for the 4th and 5th records I just want one, and for the last 2 records, so I need a query that returns

11 '3/19/2008 08:05:00 aa
22 '3/19/2008 08:08:00 ab
11 '3/19/2008 10:00:00 aa

View 5 Replies View Related







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