Help with SQL query

Please bare with me in trying to get my point across, I'm new to SQL and
would appreciate the help so much.
I have two table Table1 and Table2. There is a one to many relationship
between Table1 and Table2. Table2 has a list of widgets, I need to create a
query that would show the records from Table1 along with it's related
records and all non related records from Table2. So if I have 5 widgets and
only two have related records in Table1 results should be
Table1ID(2) fkTable2 Table2(widgetname1)
Table1ID(2) fkTable2 Table2(widgetname2)
null null Table2(widgetname3)
null null Table2(widgetname4)
null null Table2(widgetname5)
When I filter the query on another Table1ID, I would need the same sort of
result for each Table1ID.try this
SELECT TABLE1.COL1,TABLE1.COL2,TABLE2.COL1,TABLE2.COL2 FROM TABLE1 RIGHT
OUTER JOIN TABLE2 ON TABLE1.COL1 = TABLE2.COL1
--
Regards
R.D
--Knowledge gets doubled when shared
"Tim Harvey" wrote:

> Please bare with me in trying to get my point across, I'm new to SQL and
> would appreciate the help so much.
> I have two table Table1 and Table2. There is a one to many relationship
> between Table1 and Table2. Table2 has a list of widgets, I need to create
a
> query that would show the records from Table1 along with it's related
> records and all non related records from Table2. So if I have 5 widgets an
d
> only two have related records in Table1 results should be
> Table1ID(2) fkTable2 Table2(widgetname1)
> Table1ID(2) fkTable2 Table2(widgetname2)
> null null Table2(widgetname3)
> null null Table2(widgetname4)
> null null Table2(widgetname5)
> When I filter the query on another Table1ID, I would need the same sort of
> result for each Table1ID.
>
>
>

Help with SELECT

Can anyone point me in the right direction, the code below returns results but the date range imposed by the BETWEEN command doesn't seem to work, asall the results from the table are shown.

<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
AND date BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>

Regards

Tino

What format is your date column in the DB table? You can try using:

WHERE...AND date <='25/11/2007' AND date >= '26/11/2007'

Hope this helps,

Vivek

|||

Vivek

Do you mean the date type, if so its a datetime.

Thanks

Tino

|||

Hitinomclaren ,

try like this

ANDCONVERT(DATETIME,'date',101) BETWEEN CONVERT(DATETIME,'2007-11-25',101) AND CONVERT(DATETIME,'2007-11-26',101)

or

AND (date > '2007-11-25' anddate < '2007-11-26' )

Regards,
Shri

|||

Its a good practice to apply the conversion to all the participants in any comparison. So, your query should be like

"SELECT DISTINCT venue, town, artist, date FROM gigs
WHERE (town +' (Town)' = @.town) OR (venue +' (Venue)' = @.venue) OR (artist +' (Artist)' = @.artist)
ANDCONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103)"

Hope this helps.

|||

Thanks for suggestions, they didn't work for me

I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.

After 2 days at this one problem im trying not to get down but its really getting hard now

My database is SQLExpress and i've used the datetime datatype in the date colum on the DB. Im using VS2008 beta 2.

I have 20 test records in my DB and all have the date column filled in, im not using thetimepart but they are there in the DB and all default to midnight 00:00:00.

I dont know how to use stored procedures yet but im looking at the video tutorial form thelearn part of this site to hopfully get to grips with it, im assuming that the examples you have given me will work in either on the page (in the SqlDataSource as above) or in a Stored Procedure. Is there any Difference in the Syntax when switching between the two?

When I tried the suggestions you gave me using the Convert way I get ALL the records in the DB and when I use the < > way I get No records.

What am i doing wrong?

thx

tino

|||

Can you post the table structure along with some sample records, so that we can understand your problem better ?

|||

Here's the Table structure......

Coumn Name Data Type

gigId int ---> Primary Key, Is Identity-->Yes

venue varchar(50)

town varchar(50)

artist varchar(50

date datetime

I've looked at the datatypes available and there isn't just a date (without the time) must be an SqlExpress thing.

I cant even hard code 1 date in the SELECT command i.e.

SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE (town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)AND (date = '25/11/2007') ">

thx tino

|||

tinomclaren:

Thanks for suggestions, they didn't work for me

I really think im missing something fundamental now because all the posts i've trawled through have suggested similar solutions.

Yes, you are missing something fundamental. :)

You cannot mix AND with OR as you have done and expect to get correct results.

In your example, all the ORs need to be within a set of () .

() must be used to delimit the logic boundaries of the ANDs and ORs.

Example:

If I tell you I will only date women who have red hair and blue eyes or green eyes, what do I mean?

Do I mean the women must have red hair - then they must also have blue eyes or green eyes?

Or would a blonde with green eyes qualify?

My original statement was ambiguous.

If, instead, I said, "I will only date women with red hair and (blue eyes or green eyes)", then the blonde with green eyes won't qualify.

If I said "I will only date women with (red hair and blue eyes) or green eyes" then the blonde with green eyes will qualify. So would a redhead with green eyes, for that matter.

HTH

|||

David,

Once again you have saved my life. I can only hope that one day I will be as experienced as you.

Two days for 2 brackets........I wont forget this.

Thankyou

Tino

|||

Thanks everyone for your help....All answers turned out to be correct......this forum is simply superb :)

For anyone with a similar problem here is the final code I used, which works perfectly....

<asp:SqlDataSourceID="ResultsSqlDataSource1"runat="server"
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT DISTINCT venue, town, artist, date FROM gigs WHERE((town + ' (Town)' = @.town) OR (venue + ' (Venue)' = @.venue) OR (artist + ' (Artist)' = @.artist)) AND CONVERT(DATETIME,date,103) BETWEEN CONVERT(DATETIME,'25/11/2007',103) AND CONVERT(DATETIME, '26/11/2007',103) ORDER BY date asc">
<SelectParameters>
<asp:controlparameterControlID="TextBox1"Name="town"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="venue"PropertyName="Text"/>
<asp:controlparameterControlID="TextBox1"Name="artist"PropertyName="Text"/>
</SelectParameters>
</asp:SqlDataSource>

Brackets inbold made the whole thing work correctly

thnx

Tino

HELP with Running value totals

HI,
I have a table created. I need to have static fields.
The table has one group, where I use an IIF statement to point the values
into one of the three static fields. The reason I am using a table is that I
have to show months/loan programs with zero as well. The fixed static fields
are The loan types. I need to add a sum to the group footer so for each
month I can show the grand total of the three loan programs. Please help me
with this...I have been working on this for two days now and just can not
figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
an error
that states "The value expression for the textbox â'textbox31â' refers to the
report item â'Textbox21â'. Report item expressions can only refer to other
report items within the same grouping scope or a containing grouping scope."
Okay so here it is:
FY2003 FY2004 FY2005
Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
Sum, Nothing)
Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)
Loan3 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
CDbl(0)), Sum, Nothing)All of the texboxes have to be in the same scope... (the same level in the
table etc...), and you probably need to supply the scope name ie the group
name etc.. You may even have to split up the sums ie
=Sum(Reportitems!textbox1.Value,"mygroup") +
sum(ReportItems!Textbox2.Value,"mygroup") ...etc
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Susan" wrote:
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is that I
> have to show months/loan programs with zero as well. The fixed static fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but receive
> an error
> that states "The value expression for the textbox â'textbox31â' refers to the
> report item â'Textbox21â'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1" and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value), CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)|||This is exactly the problem that I'm having. I notice that there has been
no reply to this request in a month. Is that because there's no way to make
it work?
"Susan" <Susan@.discussions.microsoft.com> wrote in message
news:817F9F2D-3BE0-489A-8575-325DFE42CC20@.microsoft.com...
> HI,
> I have a table created. I need to have static fields.
> The table has one group, where I use an IIF statement to point the values
> into one of the three static fields. The reason I am using a table is
> that I
> have to show months/loan programs with zero as well. The fixed static
> fields
> are The loan types. I need to add a sum to the group footer so for each
> month I can show the grand total of the three loan programs. Please help
> me
> with this...I have been working on this for two days now and just can not
> figure it out. I tried the following: =Sum(ReportItems!Textbox21.Value +
> ReportItems!Guar_Dollar_Amt.Value + ReportItems!Textbox47.Value) but
> receive
> an error
> that states "The value expression for the textbox 'textbox31' refers to
> the
> report item 'Textbox21'. Report item expressions can only refer to other
> report items within the same grouping scope or a containing grouping
> scope."
> Okay so here it is:
> FY2003 FY2004 FY2005
> Oct Loan1 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan1"
> and
> Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)),
> Sum, Nothing)
> Loan2 =RunningValue( iif(Fields!Loan_type_Code.value = "Loan2"
> and Fields!FCLYR.Value = 2002, CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)
> Loan3 =RunningValue( iif(Fields!Loan_type_Code.value => "Loan3" and Fields!FCLYR.Value = 2002,
> CDbl(Fields!Guar_Dollar_Amount.Value),
> CDbl(0)), Sum, Nothing)

Help with linked servers

Hello,
I have two servers db1 and db2. I have created a linked server in db2 to
point to db1. It works great from the server db2 via query analyzer. I can
do a select * from db1.dbname.dbo.tablename. However I get the message
"Server: Msg 18452, Level 14, State 1, Line 1
Login failed for user '(null)'. Reason: Not associated with a trusted SQL
Server connection."
when I try the run the same query from a XP Pro box which is not hosting one
of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks in
advance.
JakeYou do not have Kerberos delegation configured properly. Check out the
following KB article for the details.
http://support.microsoft.com/default.aspx?scid=kb;en-us;319723
Sincerely,
Anthony Thomas
"Jake" <rondican@.hotmail.com> wrote in message
news:OUSaF$2yEHA.3908@.TK2MSFTNGP12.phx.gbl...
> Hello,
> I have two servers db1 and db2. I have created a linked server in db2
to
> point to db1. It works great from the server db2 via query analyzer. I can
> do a select * from db1.dbname.dbo.tablename. However I get the message
> "Server: Msg 18452, Level 14, State 1, Line 1
> Login failed for user '(null)'. Reason: Not associated with a trusted SQL
> Server connection."
> when I try the run the same query from a XP Pro box which is not hosting
one
> of the db servers. The dbservers are running w2k3 and it's sql2k. Thanks
in
> advance.
> Jake
>