Help to build query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help to build query

Author

Topic

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 07:15:14

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 07:22:56

select * from tablex where items in
(
select distinct items from tablex where manufacturers like '%olf%'
)

PS: If anyone is interested in a SQL Server Job in Connecticut with excellent pay send me a resume to ValterBorges@msn.com

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:26:14

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 07:27:23

Dear Peso,
ABC1 and etc. is only example, my database has ~10 thousand records.
May you advise some common solution.
I put in in Access.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:28:42

There is an MS Access for Access queries.

You use it much the same way. Replace % with *.

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 07:30:30

Dear Peso,
ABC1 and etc. is only example, my database has ~10 thousand records.
May you advise some common solution.

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:33:29

The first part is only to mimic YOUR environment!

This is the part you need

SELECT		s.Items,
		s.Manufacturers
FROM		{Your table name here} AS s
INNER JOIN	(
			SELECT		Items
			FROM		{Your table name here}
			WHERE		Manufacturers LIKE '%olf%'
			GROUP BY	Items
		) AS r ON r.Items = s.Items

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 07:42:59

peso, I'm new in SQL, so be gentle :)
After I wrote it and run I 'm getting dialog box with a request for "items" and "s.items".
Instead it I need to enter only part of manufacturer and to get requested output (for examples: Manufacturers LIKE '%olf%)

SELECT s.Items,
s.Manufacturers
FROM Table1 AS s
INNER JOIN (
SELECT Items
FROM Table1
WHERE Manufacturers LIKE '%olf%'
GROUP BY Items
) AS r ON r.Items = s.Items

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 07:45:25

Does your table have a column called items?

You must use the names defined in your table or access will think it's a parameter.

PS: If anyone is interested in a SQL Server job in Connecticut with excellent pay please send me your resume to ValterBorges@msn.com

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 07:47:11

I have three columns:
1.ID
2.Items
3.Manufacturers
..and only "Manufacturers" must get criteia.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:49:34

Did you REPLACE the "{Your table name here}" part with your actual table name in your database?

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 07:52:39

Yes, I wrote
SELECT s.Items,
s.Manufacturers
FROM Table1 AS s
INNER JOIN (
SELECT Items
FROM Table1
WHERE Manufacturers LIKE '%olf%'
GROUP BY Items
) AS r ON r.Items = s.Items

My table is table1

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 07:55:09

And your TABLE1 table has these three columns/fields?

1.ID
2.Items
3.Manufacturers

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 10:25:01

I got an error:

"The Micrsoft Jet database engine cannot find the input table or query.."

Please, try to simulate my "Table1" and "Query1" in your Access.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 10:36:07

We can't see images stored on your local disk.

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 10:41:42

You're BIG!!!
Thanks.., just replaced '%' by '*'. That's all..

Thanks again!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 10:44:50

Didn't I write that three hours ago?
See post at 09/13/2007 : 07:28:42

I am happy things worked out for you.

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 11:05:37

Dear Peso,
How to display all records, but without record that fit category.
From previous example, show all , except "golf".

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:09:30

[code]DECLARE @Sample TABLE (Items SYSNAME, Manufacturers SYSNAME)

INSERT @Sample
SELECT 'ABC1', 'golf' union all
SELECT 'ABC1', 'toyota' union all
SELECT 'ABC1', 'citroen' union all
SELECT 'ABC2', 'skoda' union all
SELECT 'ABC2', 'subaru' union all
SELECT 'ABC2', 'lada' union all
SELECT 'ABC3', 'peugeot' union all
SELECT 'ABC3', 'renault' union all
SELECT 'ABC3', 'dodge'

SELECT s.Items,
s.Manufacturers
FROM @Sample AS s
LEFT JOIN (
SELECT Items
FROM @Sample
WHERE Manufacturers LIKE '%olf%'
GROUP BY Items
) AS r ON r.Items = s.Items
WHERE r.Items IS NULL[/code]

And this is the code you need[code]SELECT s.Items,
s.Manufacturers
FROM {Your table name here} AS s
LEFT JOIN (
SELECT Items
FROM {Your table name here}
WHERE Manufacturers LIKE '*olf*'
GROUP BY Items
) AS r ON r.Items = s.Items
WHERE r.Items IS NULL[/code]

E 12°55'05.25"
N 56°04'39.16"

jonny
Starting Member

12 Posts

Posted - 2007-09-13 : 11:21:06

It gives me nothing.

For *olf* I want to get following:

ABC1 | toyota
ABC1 | citroen

(ABC1 | golf is not showing)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:22:23

Nothing? It gives

Items	Manufacturers
ABC2	skoda
ABC2	subaru
ABC2	lada
ABC3	peugeot
ABC3	renault
ABC3	dodge

for me...

E 12°55'05.25"
N 56°04'39.16"

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources