help sql search in detail table from a master table field

[kyriakos70]

Hello,
I have a question, I have 2 tables master/detail, master has a primary key and a field called name detail has 5 fields with details of the company name and a primary key and the foreign key, I want to create a query which will search for a master table name and in the detail table if a field value exists eg. select * from master union select * from detail where master.name like 'something' and (if master name exists) detail.field like 'something'. Is there a way to do this? I only know union for the rest I am not sure.


Thank you
Kyriakos

 

[manmaria]

Based on my understanding. Let us know if that is correct.

Select m.*, d.* from master m inner join detail d
on m.pk=d.fk

where m.name ='something'

 

[kyriakos70]

Hello,
I don't think it is working.

 

[gmmastros]

Can you show some sample data from each of the tables (master and detail), and then show what the expected output should be? Please do not show any sensitive data, but post made up data instead.

This will make it a lot easier for us to understand your goals.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kyriakos70]

master table
USE [Zeibekis]
GO
/****** Object: Table [dbo].[EPONIMIA] Script Date: 03/06/2009 17:44:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EPONIMIA](
[id] [int] IDENTITY(1,1) NOT NULL,
[????????] [nvarchar](50) COLLATE Greek_CI_AS NOT NULL,
CONSTRAINT [PK_EPONIMIA] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

detail table
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[KATASTASI](
[id] [int] IDENTITY(1,1) NOT NULL,
[id_2] [int] NOT NULL,
[aa] [int] NOT NULL,
[??_???????????] [nvarchar](50) COLLATE Greek_CI_AS NOT NULL,
[??_???????_?????] [nvarchar](50) COLLATE Greek_CI_AS NOT NULL,
[?????] [nvarchar](50) COLLATE Greek_CI_AS NOT NULL,
[???????] [nvarchar](50) COLLATE Greek_CI_AS NOT NULL,
[??_??????????] [nvarchar](50) COLLATE Greek_CI_AS NOT NULL,
[??????????] [datetime] NOT NULL,
[???????_?????????] [float] NOT NULL,
CONSTRAINT [PK_KATASTASI] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
USE [Zeibekis]
GO
ALTER TABLE [dbo].[KATASTASI] WITH CHECK ADD FOREIGN KEY([id_2])
REFERENCES [dbo].[EPONIMIA] ([id])

What I need is master has a record with id=1
and eponimia = 'epon'
detail has all the fields filled with some data and datetime = '12/2/2009'
what I want is to search in the master tbale in record id=1 or eponimia='epon' but not only in id=1 but for all records (I will give this value as a pearameter either) for a date which I will give as a parameter from a query (delphi adoquery form like date))

 

[kyriakos70]

And something else when I move to next record and then to prior (delphi 2006) the dbgrid which holds the detail table adds the last record until I refresh the clientdataset and all revert to normal, any help?

kyriakos

 

[gmmastros]

I was hoping that you would post some data contained. The data should demonstrate the problem you are having and what the expected output should be. Usually, 5 or 6 rows from each table is enough.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kyriakos70]

detail
id(pk) id_2(fk)
1 1 234 sfsdf sdfsd sdfsdf 453223 sdfsd 12/2/2009 12:00:00 ?? 432535

master
1 epon
2 jhfgg

 

[gmmastros]

Try this...

Select *
From   KATASTASI
       Inner Join EPONIMIA
         On KATASTASI.Id_2 = EPONIMIA.Id
Where  EPONIMIA.???????? = 'epon'
       And KATASTASI.?????????? = '12/2/2009'

You mentioned earlier that you wanted to filter on date, which is why I included it in the query. This query may return multiple rows depending on your data.

Notice that I start with:

Select [!]*[/!]

* will return all the columns from all the tables, which is usually more information than you need. Instead, you should replace the * with a comma separated list of column names.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[kyriakos70]

Hello,
Thank you both worked well.

Kyriakos