Query Help

Hi All,
Hoping you might be able to help with this one.
Say I have this layout:
Tbl_People
Person_ID, Name, Address, City, State, Zip.
Tbl_Response
Response_ID, Person_ID, Q_ID (integer)
LU_Questions
Q_ID, Q_Label
So basically I have a lookup table with a bunch of question IDs. What I'm
really looking to get is data in this format:
Person_ID Q_Label1 QLabel2 QLabel3
---
5 Yes Yes
No
6 No Yes
Yes
Where I have one column for each record in LU_Questions. If a record exists
in Tbl_Response with that Person_ID and Q_ID, then it will return Yes, if
not, then No.
Any idea how I can do this?James
Can you post sample data to be tested?
"James" <minorkeys@.gmail.com> wrote in message
news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> Hi All,
> Hoping you might be able to help with this one.
> Say I have this layout:
> Tbl_People
> Person_ID, Name, Address, City, State, Zip.
> Tbl_Response
> Response_ID, Person_ID, Q_ID (integer)
> LU_Questions
> Q_ID, Q_Label
>
> So basically I have a lookup table with a bunch of question IDs. What I'm
> really looking to get is data in this format:
> Person_ID Q_Label1 QLabel2 QLabel3
> ---
> 5 Yes Yes No
> 6 No Yes Yes
> Where I have one column for each record in LU_Questions. If a record
> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> Yes, if not, then No.
> Any idea how I can do this?
>|||I'm not sure what format you'd be looking for it in, but something like:
Tbl_People:
Person_ID Name Address City
1 Bob 123 Street Madison
2 John 321 Ave Johnstown
Tbl_Response:
Person_ID Q_ID
1 1
1 2
2 1
2 2
2 4
Tbl_Questions:
Q_ID Q_Label
1 Golf
2 Camping
3 Hiking
4 Fishing
So ultimately I want:
Name OtherMetaData Golf Camping Hiking Fishing
Bob street/city/etc Yes Yes No No
John street/city/etc Yes Yes No Yes
Make sense? But it would need to be dynamic as Tbl_Questions grows.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
> James
> Can you post sample data to be tested?
>
>
> "James" <minorkeys@.gmail.com> wrote in message
> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> Hoping you might be able to help with this one.
>> Say I have this layout:
>> Tbl_People
>> Person_ID, Name, Address, City, State, Zip.
>> Tbl_Response
>> Response_ID, Person_ID, Q_ID (integer)
>> LU_Questions
>> Q_ID, Q_Label
>>
>> So basically I have a lookup table with a bunch of question IDs. What
>> I'm really looking to get is data in this format:
>> Person_ID Q_Label1 QLabel2 QLabel3
>> ---
>> 5 Yes Yes No
>> 6 No Yes Yes
>> Where I have one column for each record in LU_Questions. If a record
>> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
>> Yes, if not, then No.
>> Any idea how I can do this?
>|||On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <u...@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>
> > James
> > Can you post sample data to be tested?
> > "James" <minork...@.gmail.com> wrote in message
> >news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
> >> Hi All,
> >> Hoping you might be able to help with this one.
> >> Say I have this layout:
> >> Tbl_People
> >> Person_ID, Name, Address, City, State, Zip.
> >> Tbl_Response
> >> Response_ID, Person_ID, Q_ID (integer)
> >> LU_Questions
> >> Q_ID, Q_Label
> >> So basically I have a lookup table with a bunch of question IDs. What
> >> I'm really looking to get is data in this format:
> >> Person_ID Q_Label1 QLabel2 QLabel3
> >> ---
> >> 5 Yes Yes No
> >> 6 No Yes Yes
> >> Where I have one column for each record in LU_Questions. If a record
> >> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
> >> Yes, if not, then No.
> >> Any idea how I can do this... Hide quoted text -
> - Show quoted text -
select
tp.name
, tp.other
, tq.Q_Label
, tr.golf
, tr.camping,
, tr.hiking
, tr.fishing
from tbl_people tp
join (
select person_id
,Q_id
,case when Q_id =1 then 'yes' else 'no' end golf
,case when Q_id =2 then 'yes' else 'no' end Camping
,case when Q_id =3 then 'yes' else 'no' end Hiking
,case when Q_id =4 then 'yes' else 'no' end Fishing
from tbl_response
)as tr on tp.Person_id =tr.Person_id|||James
> I'm not sure what format you'd be looking for it in, but something like:
That what I meant
create table Tbl_People (Person_ID int, Name varchar(10)
,Address varchar(20), City varchar(20))
insert into Tbl_People values (1,'Bob','123 Street','Madison')
insert into Tbl_People values (2,'John','321 Ave',' Johnstown')
create table Tbl_Response (Person_ID int,Q_ID int)
insert into Tbl_Response values (1,1)
insert into Tbl_Response values (1,2)
insert into Tbl_Response values (2,1)
insert into Tbl_Response values (2,2)
insert into Tbl_Response values (2,4)
create table Tbl_Questions (Q_ID int,Q_Label varchar(10))
insert into Tbl_Questions values (1,'Golf')
insert into Tbl_Questions values (2,'Camping')
insert into Tbl_Questions values (3,'Hiking')
insert into Tbl_Questions values (4,'Fishing')
select Name ,Address, City,max(case when Q_Label ='Golf' then 'yes' else
'no' end) as Golf,
max(case when Q_Label ='Camping' then 'yes' else
'no' end) as Camping,
max(case when Q_Label ='Hiking' then 'yes' else
'no' end) as Hiking,
max(case when Q_Label ='Fishing' then 'yes' else
'no' end) as Fishing
from Tbl_People tp join Tbl_Response tr
on tp.Person_ID=tr.Person_ID
join Tbl_Questions tq on tq.Q_ID=tr.Q_ID
group by Name ,Address, City
"James" <minorkeys@.gmail.com> wrote in message
news:%23Tx7Y6nsHHA.4916@.TK2MSFTNGP04.phx.gbl...
> I'm not sure what format you'd be looking for it in, but something like:
> Tbl_People:
> Person_ID Name Address City
> 1 Bob 123 Street Madison
> 2 John 321 Ave Johnstown
> Tbl_Response:
> Person_ID Q_ID
> 1 1
> 1 2
> 2 1
> 2 2
> 2 4
> Tbl_Questions:
> Q_ID Q_Label
> 1 Golf
> 2 Camping
> 3 Hiking
> 4 Fishing
> So ultimately I want:
> Name OtherMetaData Golf Camping Hiking Fishing
> Bob street/city/etc Yes Yes No No
> John street/city/etc Yes Yes No Yes
> Make sense? But it would need to be dynamic as Tbl_Questions grows.
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>> James
>> Can you post sample data to be tested?
>>
>>
>> "James" <minorkeys@.gmail.com> wrote in message
>> news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> Hi All,
>> Hoping you might be able to help with this one.
>> Say I have this layout:
>> Tbl_People
>> Person_ID, Name, Address, City, State, Zip.
>> Tbl_Response
>> Response_ID, Person_ID, Q_ID (integer)
>> LU_Questions
>> Q_ID, Q_Label
>>
>> So basically I have a lookup table with a bunch of question IDs. What
>> I'm really looking to get is data in this format:
>> Person_ID Q_Label1 QLabel2 QLabel3
>> ---
>> 5 Yes Yes No
>> 6 No Yes Yes
>> Where I have one column for each record in LU_Questions. If a record
>> exists in Tbl_Response with that Person_ID and Q_ID, then it will return
>> Yes, if not, then No.
>> Any idea how I can do this?
>>
>|||Thank you both. This is pretty close and I may end up having to work with
this.
Is there any way to make the column names dynamic, instead of hardcoded?
Either way thank you immensely for your help.
"schal" <shivaramchalla@.gmail.com> wrote in message
news:1182264456.599338.38270@.w5g2000hsg.googlegroups.com...
> On Jun 19, 10:34 am, "James" <minork...@.gmail.com> wrote:
>> I'm not sure what format you'd be looking for it in, but something like:
>> Tbl_People:
>> Person_ID Name Address City
>> 1 Bob 123 Street Madison
>> 2 John 321 Ave Johnstown
>> Tbl_Response:
>> Person_ID Q_ID
>> 1 1
>> 1 2
>> 2 1
>> 2 2
>> 2 4
>> Tbl_Questions:
>> Q_ID Q_Label
>> 1 Golf
>> 2 Camping
>> 3 Hiking
>> 4 Fishing
>> So ultimately I want:
>> Name OtherMetaData Golf Camping Hiking Fishing
>> Bob street/city/etc Yes Yes No No
>> John street/city/etc Yes Yes No
>> Yes
>> Make sense? But it would need to be dynamic as Tbl_Questions grows.
>> "Uri Dimant" <u...@.iscar.co.il> wrote in message
>> news:O7%233G2nsHHA.840@.TK2MSFTNGP03.phx.gbl...
>>
>> > James
>> > Can you post sample data to be tested?
>> > "James" <minork...@.gmail.com> wrote in message
>> >news:e61Di0nsHHA.4800@.TK2MSFTNGP05.phx.gbl...
>> >> Hi All,
>> >> Hoping you might be able to help with this one.
>> >> Say I have this layout:
>> >> Tbl_People
>> >> Person_ID, Name, Address, City, State, Zip.
>> >> Tbl_Response
>> >> Response_ID, Person_ID, Q_ID (integer)
>> >> LU_Questions
>> >> Q_ID, Q_Label
>> >> So basically I have a lookup table with a bunch of question IDs. What
>> >> I'm really looking to get is data in this format:
>> >> Person_ID Q_Label1 QLabel2 QLabel3
>> >> ---
>> >> 5 Yes Yes No
>> >> 6 No Yes Yes
>> >> Where I have one column for each record in LU_Questions. If a record
>> >> exists in Tbl_Response with that Person_ID and Q_ID, then it will
>> >> return
>> >> Yes, if not, then No.
>> >> Any idea how I can do this... Hide quoted text -
>> - Show quoted text -
> select
> tp.name
> , tp.other
> , tq.Q_Label
> , tr.golf
> , tr.camping,
> , tr.hiking
> , tr.fishing
> from tbl_people tp
> join (
> select person_id
> ,Q_id
> ,case when Q_id =1 then 'yes' else 'no' end golf
> ,case when Q_id =2 then 'yes' else 'no' end Camping
> ,case when Q_id =3 then 'yes' else 'no' end Hiking
> ,case when Q_id =4 then 'yes' else 'no' end Fishing
> from tbl_response
> )as tr on tp.Person_id =tr.Person_id
>

query database user input from ListBox

I have a problem selecting fields from a table where fields are equal to user input from a listbox. example

listbox of zip codes:

33023
[red]22300[/red]
39844
[red]29339[/red]
23883
[red]38228[/red]

user wants to retreive highlight zip codes from database.
connection working perfect.
Thank you for your help.

you could use IN. check out books on line for more info.

SELECT

<columnlist>

FROM

yourtable

WHERE

zipcode IN ( ....)

|||

Hi There:

You need to retrieve the selected item from yous listbox first. Like this:

<code>

Dim sYourZipString As String = nothing
Dim sItem As ListItem
For Each sItem In zipListBox.Items
If sItem.Selected Then
sYourZipString = sYourZipString & "'" & sItem.Text & "', "
End If
Next

sYourZipString = sYourZipString.Remove(sYourZipString.Length-2, 2) ' remove the tail

</code>

You can use a select IN clause with a parameter which use the value(s) user.

Assume zipcode field is a text field (Char(5), or varchar(5) something not a number).

<code>
Dim conn As SqlConnection
Dim yourcmd As SqlCommand
Dim strConn as string = "yourconnection"

Dim yourSQLString As String
yourSQLString ="Select * FROM youTableIncludeZip WHERE zipcode IN (@.YourZipString)"

conn = New SqlConnection(strConn)

yourcmd = New SqlCommand(strUpdateStmt, conn)
yourcmd.Parameters.Add(New SQLParameter("@.Fname", txtFirstName.text))

Dim objDR as SQLDataReader
dim yourItem1fromDB as string =nothing
dim yourItem2fromDB as string =nothing
dim yourItem3fromDB as string =nothing

Try
conn.Open()
objDR=yourCmd.ExecuteReader(system.data.CommandBehavior.CloseConnection)

While objDR.Read()

yourItem1fromDB=objDR("yourItem1")
yourItem2fromDB=objDR("yourItem2")
yourItem3fromDB=objDR("yourItem3")
' ... get what you want from you Db table
End While

Finally
conn.close

End Try

</code>

Hope this help.

Limno

Query Column with letters

I have a database with a zip code column. I want to flag all rows that have letters in that column (a-z). How could I construct the where clause?
Is there a better alternative to:
WHERE ZipCode LIKE '%a%' OR ZipCode LIKE '%b%' ...if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?|||if you're doing this because you want to limit what goes into the column, why not just create a constraint that won't accept letters?

Well the data is already in there and we would rather have it disabled with the option of cleaning it up later rather than simply deleting such data.|||ok. Use the ISNUMERIC function... it'll return a 1 if the field is a valid number... 0 if it's not. That should be the easiest way to determine if there are letters in the postal code.|||Good idea, but I must allow for hyphens.|||Ya mean like

USE Northwind
GO

SET NOCOUNT OFF
CREATE TABLE myTable99(Col1 varchar(8000))
GO

INSERT INTO myTable99(Col1)
SELECT '12345' UNION ALL
SELECT 'Brett' UNION ALL
SELECT 'North Dallas 40' UNION ALL
SELECT '40-20' UNION ALL
SELECT '123.45' UNION ALL
SELECT '123456789012345678901234567890'
GO

SELECT * FROM myTable99
WHERE ISNUMERIC(REPLACE(Col1,'-','')) = 1
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO