Stuck on a sql 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

Stuck on a sql query

Author

Topic

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 10:54:37

I have been trying to build a query that will find all customers how have purchased at least one Truck and one SUV. Show I did a full join between car and customer tables:

Select *
From Car
Full join Customer
On Car.CustomerID = Customer.CustomerID

ModelType = car models

I though maybe use a count(*) would do this but it is never works.

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-18 : 11:09:15

SELECT * FROM Customers o
WHERE EXISTS (
SELECT * FROM Car i1 WHERE i1.CustID = o.CustID AND Type = 'SUV')
AND EXISTS
SELECT * FROM Car i1 WHERE i1.CustID = o.CustID AND Type = 'Truck')

Post the DDL of the tables with constraints

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 12:33:07

Could not get that to work. I keep on getting this error:

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "il.ModelType" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "il.ModelType" could not be bound.

Car is a bridge table for all of the other tables

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 12:49:15

I saw where the error was coming from

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 12:55:00

I was wondering how could I get the same results but using a join and saved query?

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 13:07:05

Like Create View view_name as Select ....

I was just trying learn if I could use a saved query and join to the same thing

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-18 : 14:00:16

Why would you need a join if you have a solution that works? EXISTS = very fast, Join can be slow.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

intern2424
Yak Posting Veteran

53 Posts

Posted - 2009-11-18 : 14:03:06

Oh, I am just trying to understand the differences between the two. I was going to compare query times. I am trying to learn more about sql queries.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-11-19 : 08:10:38

In that case, look up "JOIN" in books online (Sql Server help). It explains joins quite clearly and thoroughly.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 09:40:11

[Frank Barone]
"Holy Crap"
[/Frank Barone]

My Bad




SELECT * FROM Customers o
WHERE EXISTS (
SELECT * FROM Car i1 WHERE i1.CustID = o.CustID AND Type = 'SUV')
AND EXISTS (
SELECT * FROM Car i2 WHERE i2.CustID = o.CustID AND Type = 'Truck')

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

X002548
Not Just a Number

15586 Posts

Posted - 2009-11-19 : 10:14:28

As a join


	   SELECT * 
		 FROM Customers o
   INNER JOIN Car i1 
		   ON i1.CustID = o.CustID 
		  AND Type = 'SUV'
   INNER JOIN Car i2 
		   ON i2.CustID = o.CustID 
		  AND Type = 'Truck'

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources