Re: problem with date calculation - Database Discussion Boards

Re: How do we select only those columns that aren`t null

GuyThiebaut5-Oct-07 9:32

5-Oct-07 9:32

I'm not sure why you would want to not return a whole column if one of the rows contained a null value in the column?

So I'm taking a risk and guessing you mean don't return the row if the column contains a null.

In which case:

How about this?

<br />
select name,isnull(occupation,'yadayadayada'),age<br />
from persons<br />
where occupation <> 'yadayadayada'

<br />
select name,occupation,age<br />
from persons<br />
where occupation is not null

Both of these should work.

You mention select * so to achieve this you would need to turn the first example into a view, the second example you could just replace the selected columns with an *.

I hope this helps...

You always pass failure on the way to success.

how to create a local search engine?

LyndonJohn4-Oct-07 7:40

LyndonJohn

4-Oct-07 7:40

guys please help me on how to create a local search engine for my website..thank you.. for example, the user can search for the different products that my website is selling..

Re: how to create a local search engine?

Joe4-Oct-07 8:43

Joe

4-Oct-07 8:43

LyndonJohn wrote:
the user can search for the different products

You can probably have 2 tables
Table1
ProductID
ProductName

Table2
ProductID
Description
Price

When the user wants to search details pertaining to a product, you may map it onto table2 based on ProductID and fetch the corresponding info.

Re: how to create a local search engine?

PIEBALDconsult4-Oct-07 9:46

PIEBALDconsult

4-Oct-07 9:46

For what? Local restaurants? Local singles? ... Big Grin | :-D

Re: how to create a local search engine?

LyndonJohn5-Oct-07 7:24

LyndonJohn

5-Oct-07 7:24

i mean in local website..the user can just search the product within the system...G? Big Grin | :-D

MS Access 2003 and Triggers

Ryno Burger4-Oct-07 7:17

Ryno Burger

4-Oct-07 7:17

Hey guys

Not sure if this is the appropriate forum but let me give it a go anyways.

I’m trying to simulate triggers in MS Access 2003 from a .NET platform using C#. Is there anybody out there that perhaps faced the same challenge and if so can you please give me some guideline where to start.

Any background, information of guidelines will be highly appreciated.

Thanks.

R

problem with date calculation

ahmed_fci20064-Oct-07 1:39

ahmed_fci2006

4-Oct-07 1:39

hi every body I hope you are all fine.
I have project in asp 2.0 and database oracle10g and i want to preview the fields that in the last 6 month only.
i just want the query in sql thx in advance. Smile | :)

let's work together

Re: problem with date calculation

pmarfleet4-Oct-07 4:16

pmarfleet

4-Oct-07 4:16

How can anyone help you when you haven't given details of your tables/fields?

Paul Marfleet

Re: problem with date calculation

Blue_Boy4-Oct-07 4:22

Blue_Boy

4-Oct-07 4:22

select cast( month(getdate()) as varchar), cast(month(getdate())-6 as varchar)

I Love SQL

group by problem(plz help me)

Arihant rathi4-Oct-07 1:31

Arihant rathi

4-Oct-07 1:31

hi i m ajay rathi..i m facing a problem in group by clause in sql query..plz any one of u help me

The problem is... following are 3 queries and i need to combine all three queries into one query..because this time these queries are giving me data in three table and i am showing this data in repeater in ASP.NET with SQL SERVER 2003..

select count(*)as active from tbl_Post_Job where Company_Id_Fk = 60
and Status = 'Active' and Post_Date between '01/01/2007' and '01/01/2008' group by year(Post_Date), month(Post_Date)

select count(*)as billed from tbl_Post_Job where Company_Id_Fk = 60
and Status = 'Inactive' and Post_Date between '01/01/2007' and '01/01/2008'
group by year(Post_Date), month(Post_Date)

select count(*)as billed , month(Post_Date) as month_date ,year(Post_Date)as year_date from tbl_Post_Job where Company_Id_Fk = 60 and Status='Inactive' group by year(Post_Date), month(Post_Date)

actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...

Date | Active | Billed
------------------------------------
9,2007 | 4 | 8
10,2007 | 7 | 2
12,2007 | 6 | 6
------------------------------------

please if any one of you can help me it's very good for me plzzzzzzzzzzzzzz
thanks

Ajay Rathi
software engineer
NOIDA(UP),INDIA

Re: group by problem(plz help me)

andyharman4-Oct-07 1:40

andyharman

4-Oct-07 1:40

Hi Ajay

Try something like:

select month(Post_Date) as Post_Month,
    year(Post_Date) as Post_Year,
    sum(case when Status = 'Active' then 1 else 0 end) as Active,
    sum(case when Status = 'Inactive' then 1 else 0 end) as Billed
from tbl_Post_Job
where Company_Id_Fk = 60
group by year(Post_Date), month(Post_Date)
order by year(Post_Date), month(Post_Date)

The case expressions allow you to count the number of rows that match their criteria.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

Re: group by problem(plz help me)

Arihant rathi4-Oct-07 3:13

Arihant rathi

4-Oct-07 3:13

thx a lot of thx it's working
u gave me a big support....
but i have one more problem related to this problem...actually when i m using that query which u gave me that is not working when i m using a sub query in that query ...

following is that query..

sum(case when Type_of_view like 'Applicants' and Posting_Id_Fk in
(select Id_PK from tbl_Post_Job where Company_Id_Fk = @Company_Id_Fk and Job_Title like @Job_Title and Job_Type like @Job_Type and Job_Location like @Job_Location ) then 1 else 0 end) as Applicants,

in this query i know the value of @Company_Id_Fk, @Job_Type, @Job_Location ,@Job_Title

Ajay Rathi
software engineer
NOIDA(UP),INDIA

inserting values with a nested join query

anuradha.rrao4-Oct-07 1:18

anuradha.rrao

4-Oct-07 1:18

Hi,

I have 3 tables: City_master(cityid(PK),cityname), zone_master(zoneid(PK),zname,cityid(FK)) and city_x_zone(czid(PK),cityid(FK),zoneid(FK).

I am running a join query to get the cityid and zone id into city_x_zone.the query is something like this:
select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='tb1.text' and zname='tb2.text'

I am running an insert query for city_x_zone like this:
insert into cityzone values(newid(),select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='Bangalo' and zname='south')

this is throwing an error.

What can be the solution to this?

Regards,
Anuradha

Re: inserting values with a nested join query

Paddy Boyd4-Oct-07 1:34

Paddy Boyd

4-Oct-07 1:34

Try something like:

insert into cityzone
select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='Bangalo' and zname='south'

Query required

veereshIndia3-Oct-07 23:06

veereshIndia

3-Oct-07 23:06

Hi All,

Iam having two tables

table1

1.Rno Primary key
2.name
3.empid foreign key of table2

table2

1. empid primary key
2.address
3.Rno foreign key of table1

Here Rno and empid are auto generated. Now i want to insert data into first table i need sql query for this.Please help me on this

i want to join this group

Re: Query required

John-ph3-Oct-07 23:33

John-ph

3-Oct-07 23:33

veereshIndia wrote:
i want to insert data into first table i need sql query for this

INSERT INTO tblEmployee(
     EmpolyeeID,
     EmployeeName)
VALUES(
     @EmpolyeeID,
     @EmployeeName)

Regards
J O H N

"Even eagles need a push." David McNally

Re: Query required

veereshIndia3-Oct-07 23:35

veereshIndia

3-Oct-07 23:35

Hi,

Please see my question once again.

Regard's
veeresh

i want to join this group

Re: Query required

John-ph4-Oct-07 0:05

John-ph

4-Oct-07 0:05

To Insert Into First Table

INSERT INTO tblEmp1(
                         EmpID,
                         EmpName
                      )
          VALUES(
                         @EmpID,
                         @EmpName
                       )

To Insert Into Second Table

<code>INSERT INTO tblEmp2(
                        RowID,
                        EmpAddress,
                     )
         VALUES(
                        @RowID,
                        @EmpAddress
                       )</code>

Regards
J O H N

"Even eagles need a push." David McNally

Re: Query required

Krish - KP4-Oct-07 0:30

Krish - KP

4-Oct-07 0:30

both insert statements results in violation of foreign key constraint

Regards
KP

Re: Query required

John-ph4-Oct-07 0:41

John-ph

4-Oct-07 0:41

Table Relationship's are Violating the Normalization Rule.

Regards
J O H N

"Even eagles need a push." David McNally

Re: Query required

Krish - KP4-Oct-07 0:28

Krish - KP

4-Oct-07 0:28

disable constraint then a dummy master data.

use the same while inserting into transaction table.

Regards
KP

Re: Query required

John-ph4-Oct-07 0:38

John-ph

4-Oct-07 0:38

Inserting Into these table's are possible only If one of the table's Foreign Key column Allows Null Value.

Regards
J O H N

"Even eagles need a push." David McNally

Re: Query required

Paddy Boyd4-Oct-07 0:35

Paddy Boyd

4-Oct-07 0:35

You have two foreign keys, each referencing the primary key of the other table. Why do you have this circular reference?

Makes inserting records a little awkward...

Re: Query required

John-ph4-Oct-07 0:44

John-ph

4-Oct-07 0:44

You are right.

Regards
J O H N

"Even eagles need a push." David McNally

Re: Query required

Paddy Boyd4-Oct-07 0:46

Paddy Boyd

4-Oct-07 0:46

I know

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.